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