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!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...