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
Revered Legend

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!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...