Check if a date is within interval of another record

yvassilyeva — Fri, 08 Oct 2021 10:47:19 GMT

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?

ID	CREATED_DATE	RESOLVED_DATE
123	2021-10-04 19:30:35	2021-10-07 15:13:16
123	2021-10-05 16:11:25	2021-10-15 12:05:32
456	2021-03-05 10:10:13	2021-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!

Re: Check if a date is within interval of another record

ITWhisperer — Fri, 08 Oct 2021 12:15:45 GMT

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

topic Check if a date is within interval of another record in Splunk Search

Check if a date is within interval of another record

Re: Check if a date is within interval of another record