topic Re: How to search the sum of transactions where the times do not overlap? in Splunk Search

How to search the sum of transactions where the times do not overlap?

cmamer — Tue, 28 Jul 2015 16:57:59 GMT

I want to be able to show the sum of time that users have had licenses checked out (historically). But if a user has two of the same license checked out at any given time, I only want one to be added to the tally. If the times overlap (concurrent transaction from the same user) I only want that time counted once in the sum. For example, if there are 3 transactions: one from 9:00 to 9:15, one from 9:00 to 9:30 and one from 8:30 to 10:00 I would want the sum to show as 1.5 hours.

These transactions are created to be unique to 3 fields, so I would need these 3 fields to be included in the results (user, license, machine name) .

Any ideas?

Re: How to search the sum of transactions where the times do not overlap?

Richfez — Tue, 28 Jul 2015 19:44:08 GMT

If I read this correctly, you want to create "transactions" out of license usage, disregarding concurrent use. So, given the following data (with a couple extra lines added for controls):

user license machine start end
Me Adobe MyMachine 7/28/2015 7:00 7/28/2015 8:10
Me Adobe MyMachine 7/28/2015 9:00 7/28/2015 9:15
Me Adobe MyMachine 7/28/2015 9:00 7/28/2015 9:30
Me Adobe MyMachine 7/28/2015 8:30 7/28/2015 10:00
Me Adobe MyMachine 7/28/2015 11:00 7/28/2015 11:04

You would want one output line (duration 1:10) for the first line, ONE created out of the middle three lines (duration 1.5 hours, starting at 8:30 and ending at 10:00), then one more output line for 4 minutes from the last line?

Turns out for the data as I have it above, I don't even need transaction because I discard all the lines that we don't need and each remaining event is self contained:

<search that returns data like the above> 
| eval dur=strptime(end,"%m/%d/%Y %H:%M")-strptime(start,"%m/%d/%Y %H:%M") 
| concurrency duration=dur start=_time 
| search concurrency=1 
| table user, machine, license, start, end , dur

Fill in your search, then each line can be added one by one to check the results.

Explanation:
The first "eval" takes two time fields which happen to be formatted as the ones I posted above and subtracts them to get a duration called "dur" (to not conflict with the transaction's duration later). You may already have a duration, in which case you don't need this. Likewise, if your start and end times are epoch (e.g. integers) you can just do the math directly with them, like "eval dur=end-start"

The "concurrency" then counts overlapping events.

The "search" line then only matches lines with a concurrency of 1. In other words, only events that don't overlap with other events are included, the others are not.

The "table" at the end just makes it pretty, listing the start/end time of the license usage and the duration of it in seconds.

Re: How to search the sum of transactions where the times do not overlap?

cmamer — Tue, 28 Jul 2015 20:17:03 GMT

To clarify, I also want the times of the non-concurrent transactions to be part of the sum, so in rich7177's answer below I would want the durations of the first and last lines added as well.

Re: How to search the sum of transactions where the times do not overlap?

cmamer — Tue, 28 Jul 2015 20:25:59 GMT

I already have the duration of each transaction, but I'm looking for the sum of the transaction durations without double-counting concurrent transactions, so I would want my results to look like this (using your example events):

Me Adobe MyMachine 164 minutes--------> (70 mins + 90 mins + 4 mins )

Re: How to search the sum of transactions where the times do not overlap?

Richfez — Tue, 28 Jul 2015 21:04:00 GMT

Something as simple as ...| stats sum(dur) AS time by user, machine, license won't do it, I presume?

If that works, great. 🙂 ( ...| eval license_time_in_minutes=time/60 could come in handy if you need it in minutes.... )

If not... next reasonably easy possibility: ... | streamstats sum(dur) AS time by user, machine, license? That'll be a running sum for that, so it's not quite right, either.

If I'm not quite on the right track, could you post a bit of the data you have and perhaps the output you have so far? It might go far to helping us figure out precisely what it is still missing.