Splunk Search

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

Motivator

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.

0 Karma
1 Solution

Motivator

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

View solution in original post

Motivator

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

View solution in original post

Motivator

perfect! Thank you!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!