How to calculate total duration for overlapping transactions

[cwilmoth]

I have been trolling the community and have found a lot of information regarding usage of transactions, however I am not finding a solution for what I need to do. I need to calculate the total duration for a users Citrix sessions for a particular day. This is pretty easy if:

user ICA_START=08:00 id=1111
user ICA_END=12:00 id=1111
user ICA_START=13:00 id=2222
user ICA_END=17:00 id=2222

I can just use "transaction user id startswith="ICA_START" endswith="ICA_END" | stats sum(duration) by user" (simplified from my actual search, but this is the core of it) to get a total duration of 08:00:00.

This works fine if the transactions do not overlap, but how do I go about this when a user decides to open up multiple Citrix sessions? i.e.

user ICA_START=08:00 id=1111
user ICA_START=08:10 id=2222
user ICA_END=17:00 id=(1111 OR 2222 - depending on which one they shut down first)
user ICA_END=17:10 id=(1111 OR 2222 - depending on which one they shut down last)

If I use the above search, I get a duration of 18:00:00 when really what I want to show is 09:10:00. I have no way of knowing how many sessions will be opened during a day or how many will be concurrent.

So basically what I (think I) need is a way to fill time buckets per user per day for each session they use

...
07:55 - 0
08:00 - 1
08:05 - 1
08:10 - 2
...
11:55 - 2
12:00 - 0
...
13:00 - 0
13:05 - 1
13:10 - 2
...
17:00 - 2
17:05 - 0

And then count the number of buckets that are > 0 and multiply by 5? I would do this on a more granular basis, but set it to 5 minute buckets for brevity.

If anyone has done this or has suggestions, I would really appreciate it.

Thanks
Craig

[Richfez]

Just to clarify:

In your example data (the 4 lines), you don't care about multiple sessions, you only care about the extent of "them being logged in". So if they log in at 8:00 and out at 17:00, you don't care what ELSE they did in that time or how many sessions they may have opened during that period, you just want that to count for 9 hours?

In that case, drop your "id" from your transaction. It will then link all those events by a single user together and the duration will then be the "outside" duration only.

transaction user startswith="ICA_START" endswith="ICA_END" | stats sum(duration) by user

Let us know how that works, if I misread your request, there are alternates that can get you different answers.

[somesoni2]

So same user multiple sessions should be counted as one?

[Murali2888]

Hi cwilmoth,

Can you try the below?

<Base Search> | stats min(ICA_START) as SessionStart min(ICA_END) as SessionEnd by user, id | eval Duration = SessionEnd - SessionStart | table user, id, Duration

You can format the time fields as per your convenience if this is giving you the desired results.

[Murali2888]

If any of the answers worked for you, can you accept it so that the thread can be closed?