Splunk Search

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

kmaron
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

whrg
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

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

kmaron
Motivator

perfect! Thank you!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...