Splunk Search
Highlighted

How to record/calculate the duration of overlapping transactions

Path Finder

I have a transaction overlap issue. The output below is my data from search query with a transaction command. Here is my search query:

Search

index=* (sourcetype=InCharge-Traps AND (State="Notify" OR State="Clear")) OR (sourcetype=SAM_Audit AND (eventtype="Notification Notify" OR eventtype="Notification Clear")) source!="D:\\InCharge\\SAM\\smarts\\local\\logs\\TRAP-INCHARGE-OI_en_US_UTF-8.log" 
[| inputlookup New_SLA_Targets where Alert="y" 
| fields InstanceName EventName] 
| lookup New_SLA_Targets InstanceName EventName OUTPUT Service Target Type Dev_Needed Status Weight SecsDown StartTime EndTime 
| sort _time

| transaction Service InstanceName EventName Type startswith=(State="Notify" OR eventtype="Notification Notify") endswith=(State="Clear" OR eventtype="Notification Clear") 

| concurrency duration=duration 

| eval stime=strftime(_time, "%H:%M:%S") 
| eval stime_epoch=_time
| eval etime_epoch=stime_epoch+duration
| eval etime=strftime(etime_epoch, "%H:%M:%S") 
| where stime>StartTime AND etime<EndTime

| eval Active=if(SecsDown=0,"Y",if(duration>SecsDown,"Y","N")) 
| where Active="Y" 
| table _time stime_epoch stime etime_epoch etime duration concurrency InstanceName EventName

Output

_time                 stime_epoch   stime      etime_epoch   etime     duration   concurrency   InstanceName    EventName
2017-08-28 10:13:19 1503933199  10:13:19    1503933383  10:16:23    184     1             ualbuacwas5     Down
2017-08-28 10:17:15 1503933435  10:17:15    1503941278  12:27:58    7843       1              ualbuacwas4     Down
2017-08-28 12:22:35 1503940955  12:22:35    1503941180  12:26:20    225     2             ualbuacwas5     Down
2017-08-28 12:29:39 1503941379  12:29:39    1503945457  13:37:37    4078       1              ualbuacwas4     Down
2017-08-28 13:13:43 1503944023  13:13:43    1503947722  14:15:22    3699       2              ualbuacwas5     Down

I need to identify and report the overlapping transactions and the overlapping duration. All other duration's are not important.

So, if you look at the output stimeepoch 1503933435, the end of that transaction overlaps the next at stimeepoch 1503940955. This is the record with the concurrency of 2. I have two overlaps in my data and need to report on the duration of just the overlap. I believe in my example above, it would be 323 seconds. My second would be 1434.

At this point I am stuck. I'm sure that someone out there can help me out.

Thanks in advance,
Rcp

0 Karma
Highlighted

Re: How to record/calculate the duration of overlapping transactions

SplunkTrust
SplunkTrust
| rename COMMENT as "These are the fields you need to determine seconds of concurrency"
| table stime_epoch etime_epoch

| rename COMMENT as "create a +1 record at stime and a -1 record at etime"
| eval fan=mvrange(0,2)
| mvexpand mvrange
| eval _time=if(fan=0,stime_epoch,etime_epoch)
| eval change=if(fan=0,+1,-1)

| rename COMMENT as "roll together any records that occur at the same time."
| stats sum(change) as change by _time

| rename COMMENT as "make sure they are in order, then do a running total of how many are running at each time."
| sort 0 _time
| streamstats sum(change) as concurrency

| rename COMMENT as "reverse the order and copy the next time back onto each prior record, then calculate the duration."
| reverse
| streamstats current=f last(_time) as nexttime
| eval duration=nexttime-_time

| rename COMMENT as "drop records with no concurrency, tehn reverse them back into time order."
| where concurrency>1
| reverse

After testing the above, remove the last line and replace it with a stats.

| stats sum(duration) as totalduration

View solution in original post

0 Karma
Highlighted

Re: How to record/calculate the duration of overlapping transactions

Path Finder

I had to deconstruct your logic to get my head around it. Once I slowed down and handled the code line by line a light went off in my head. You had a minor typo what I corrected and this is what I came up with.

| sort Service _time
| table _time stime_epoch etime_epoch Service EventName duration Weight

`comment("create a +1 record at stime and a -1 record at etime")`
| eval fan=mvrange(0,2)

`comment("create a record for both the start and end times")`
| mvexpand fan
| eval _time=if(fan=0,stime_epoch,etime_epoch)
| eval change=if(fan=0,+1,-1)
| stats sum(change) as change by Service _time EventName duration Weight

`comment("make sure they are in order, then do a running total of how many are running at each time.")`
| sort 0 _time
| streamstats sum(change) as concurrency by Service 

`comment("reverse the order and copy the next time back onto each prior record, then calculate the duration.")`
| reverse
| streamstats current=f last(_time) as nexttime by Service
| eval duration=(nexttime-_time)*Weight

`comment("Keep all the transactions which have a updated duration")`
| sort Service _time
| where concurrency>0

I needed both the overlapping data and the ones that did not. It all worked good. Thanks for the assistance.

0 Karma