Kibana - date histogram vizualization with unique count per user with login and logout

[Nuno_Barros]

Hi,

I am importing data from a license database into elastic through logstash. The data comes with this structure:

user_name, timelogin, elapsedtime, timeofrelease

I want to build a visualization with the unique count per user and the difference between the time of login and the time of release of those logins. Indeed, at the end, I need to have a spike with cumulative login sessions that were not released.

I don´t have any ideia how to build such a thing. Anyone of you guys could help me?

The max that i reached it was build a TSVB dashboard with the unique count per user for timelogin and timeofrelease, but without the cumulative for the sessions that was not released.

[Luca_Belluccini]

Hello @Nuno_Barros

Welcome to the Elastic discuss forum!

I have few questions:

@Nuno_Barros

user_name, timelogin, elapsedtime, timeofrelease

Can you have multiple sessions by the same user?

@Nuno_Barros

I want to build a visualization with the unique count per user

Unique count per user of what? Do you want to know the number of sessions per user?

@Nuno_Barros

the difference between the time of login and the time of release of those logins

What does elapsedtime represent?

@Nuno_Barros

Indeed, at the end, I need to have a spike with cumulative login sessions that were not released.

How we can identify if a session was not released?

---

I think we can help but I think the best option is to share 10 lines with some dummy data and tell us what are the values/calculations you would like to obtain.

Also, as we're talking about a user centric approach, I think the Transform Jobs might help.
But let's first better frame the problem so we can route you to the best solution.

[Nuno_Barros]

Hi @Luca_Belluccini,

Yes, I can have multiple session by the same user on a period of time.

I want to have unique count per user during one hour period because of the billing that is made. (we pay per single user on an hour period and not the multiple sessions made by that user on that period)

Elapsedtime is one field that I wasn't consider on this Approach but can be helpful. Is, in fact, the elapsed time of a session in seconds. I have the timelogin and if you sum the elapsed time you will get the timeofrelease.

If a session was not release, the field timeofrelease will be null. In this case, I have defined a timeout of one hour to all inactive sessions, so the null records were expected to be very small.

I can share some dummy records:

Timelogin;User_Name;TimeOfRelease;Elapsed_Time
2020-05-14 05:36:41.000;user2;2020-05-14 06:50:01.000;4400
2020-05-14 05:44:03.000;user1;2020-05-14 05:44:04.000;1
2020-05-14 05:53:06.000;user3;2020-05-14 19:09:48.000;47802
2020-05-14 06:00:11.000;user1;2020-05-14 06:00:12.000;1
2020-05-14 06:13:21.000;user4;2020-05-14 07:57:10.000;6229
2020-05-14 06:13:38.000;user1;2020-05-14 06:13:40.000;2
2020-05-14 06:14:00.000;user4;2020-05-14 07:57:10.000;6190
2020-05-14 06:19:42.000;user5;2020-05-14 07:48:27.000;5325
2020-05-14 06:38:22.000;user6;2020-05-14 07:39:59.000;3697
2020-05-14 06:39:07.000;user1;2020-05-14 06:39:07.000;0
2020-05-14 06:46:01.000;user1;2020-05-14 06:46:01.000;0
2020-05-14 06:46:35.000;user7;2020-05-14 13:25:41.000;23946
2020-05-14 06:54:58.000;user8;2020-05-14 09:57:43.000;10965
2020-05-14 06:56:37.000;user9;2020-05-14 07:57:10.000;3633
2020-05-14 06:56:51.000;user10;2020-05-14 08:06:02.000;4151
2020-05-14 06:56:58.000;user1;2020-05-14 06:56:59.000;1
2020-05-14 06:56:59.000;user1;2020-05-14 06:57:00.000;1
2020-05-14 06:59:43.000;user11;2020-05-14 17:53:24.000;39221

Sorry, because i am not being clear and is a bit difficult to explain.

Basically, we need to build a visualization to show many licences are being used aggregated for
one hour period and per user and consider the licenses that was not released like a cumulative sum.

Best regards and thank you for helping me.