Check if a date is within interval of another record

[yvassilyeva]

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!

[ITWhisperer · Accepted solution]

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