topic Re: How to record/calculate the duration of overlapping transactions in Splunk Search

How to record/calculate the duration of overlapping transactions

raynold_peterso — Tue, 29 Sep 2020 15:54:33 GMT

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 stime_epoch 1503933435, the end of that transaction overlaps the next at stime_epoch 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

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

DalJeanis — Fri, 29 Sep 2017 05:18:46 GMT

| 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

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

raynold_peterso — Wed, 12 Sep 2018 16:54:44 GMT

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.