Splunk Search

How to calculate total duration for overlapping transactions

cwilmoth
Path Finder

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
SplunkTrust
SplunkTrust

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

So same user multiple sessions should be counted as one?

0 Karma

Murali2888
Communicator

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.

0 Karma

Murali2888
Communicator

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...