Can you help me identify when end time overlaps start time for the following events?

[kmaron]

We have a process that runs for various pieces of our system, and I'm trying to prevent any overlaps. I have been able to create a table of start times and end times, but I'm a bit stumped on how to have SPLUNK identify when the end time of the previous event overlaps the start time of the next event.

I have broken it down to a run date, an object, start time and end time using this search:

index=foo ("started event" OR "ended event")
| eval start_time = case(Status="Started",_time)
| eval end_time = case(Status="Completed",_time)
| transaction ObjectStore maxspan=10h startswith="Started" endswith="Completed"
| eval run_date = strftime(start_time,"%F")
| eval start_time = strftime(start_time,"%H:%M:%S")
| eval end_time = strftime(end_time,"%H:%M:%S")
| table run_date ObjectStore start_time end_time][1]

Which gives me the table in the screenshot attached. Now I just need a way to identify when the previous end time overlaps the next start time. The specific events are highlighted in the screenshot as well. I assume I can do this with some form of streamstats, but I haven't managed to figure it out yet.

[whrg · Accepted solution]

Hi!
Try it like this:

...
| streamstats current=false last(start_time) as next_start_time
| eval overlap=if(next_start_time<end_time,"yes","no")

Then it looks like this:

run_date    ObjectStore       start_time  end_time   next_start_time   overlap
2018-12-03  Underwriting      22:12:48    05:03:44                     no
2018-12-03  Claims            20:40:03    20:51:23   22:12:48          no
2018-12-03  Life              18:25:00    22:06:29   20:40:03          yes
2018-12-03  FSCD              17:45:01    18:13:03   18:25:00          no
2018-12-03  Marketing         17:40:00    17:42:02   17:45:01          no
2018-12-03  HumanResources    17:30:00    17:38:25   17:40:00          no
2018-12-02  Underwriting      22:30:03    05:48:57   17:30:00          no
2018-12-02  Claims            20:45:03    20:55:04   22:30:03          no
2018-12-02  Life              18:33:04    20:52:03   20:45:03          yes
2018-12-02  FSCD              17:45:02    18:07:51   18:33:04          no
2018-12-02  Marketing         17:40:01    17:41:16   17:45:02          no
2018-12-02  HumanResources    17:35:00    17:39:23   17:40:01          no

You will need to add the run date to start_time and end_time, i.e. it should look something like "2018-12-03 22:12:48". Otherwise there will be a false positive if a process runs past midnight because "00:10:00"<"23:30:00" will evaluate to true. However, "2018-12-03 00:10:00"<"2018-12-02 23:30:00" will not.
Alternately, instead of run_date have two columns such as start_date and end_date and then modify the streamstats command to calculate last(start_time) as well as last(start_date).

[kmaron]

perfect! Thank you!