I have a transaction overlap issue. The output below is my data from search query with a transaction command. Here is my search query:
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
_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,
| 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.