Splunk Search

Check if a date is within interval of another record

yvassilyeva
Path Finder

Hi!

I have the following data and would like to check, for those records with the same ID, if one record has CREATED_DATE within CREATED_DATE and RESOLVED_DATE of another one. So in the example, the first record in blue was created on 10-4 and resolved on 10-07, where the second record with the same ID was created on 10-05 while the other one was open. Can we do this kind of check in Splunk?

IDCREATED_DATERESOLVED_DATE
1232021-10-04 19:30:352021-10-07 15:13:16
1232021-10-05 16:11:252021-10-15 12:05:32
4562021-03-05 10:10:132021-05-05 11:05:21

 

We'd need another column, say CHECK, that says "overlap" when the second record was created between the range of the first one, with the same ID.

Thank you very much in advance!

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| sort 0 ID CREATED_DATE
| streamstats values(RESOLVED_DATE) as PREVIOUS_RESOLVED_DATE current=f window=1 by ID
| eval overlap=if(isnotnull(PREVIOUS_RESOLVED_DATE) AND CREATED_DATE < PREVIOUS_RESOLVED_DATE, "Overlap", null())

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| sort 0 ID CREATED_DATE
| streamstats values(RESOLVED_DATE) as PREVIOUS_RESOLVED_DATE current=f window=1 by ID
| eval overlap=if(isnotnull(PREVIOUS_RESOLVED_DATE) AND CREATED_DATE < PREVIOUS_RESOLVED_DATE, "Overlap", null())
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

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

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...