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!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...