Splunk Search

How do I compare times to find the closest to time in a column?

Julia1231
Communicator

Hi everyone,

From dbxquery, I retrieve this table:

id start_time1 end_time1 start_time2 end_time2
1234 13/09/2022 21:46:43.0 16/09/2022 12:10:35.414809 15/09/2022 21:46:32.0 16/09/2022 09:27:41.0
1234 13/09/2022 21:46:43.0 16/09/2022 12:10:35.414809 14/09/2022 24:52:03.0 15/09/2022 10:15:56.0
1234 13/09/2022 21:46:43.0 16/09/2022 12:10:35.414809 15/09/2022 10:30:14.0 15/09/2022 10:47:26.0

I want to find the start_time2 that closest to the start_time1, means the 2nd line.

How can I do please?

 

Thanks,

Julia

Labels (5)
Tags (3)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Julia1231,

if in each event, you have all the fields, you have to run something like this:

<your-search>
| eval diff=strptime(start_time2,"%Y-%m-%dT%H:%M:%S")-strptime(end_time1,"%Y-%m-%dT%H:%M:%S")
| stats values(start_time1) AS start_time1 values(end_time1) AS end_time1 values(start_time2) AS start_time2 values(end_time2) AS end_time2 min(diff) AS min_diff BY id	
| sort -min_diff
| head 1

Ciao.

<your-search>
| eval diff=strptime(start_time2,"%Y-%m-%dT%H:%M:%S")-strptime(end_time1,"%Y-%m-%dT%H:%M:%S")
| stats values(start_time1) AS start_time1 values(end_time1) AS end_time1 values(start_time2) AS start_time2 values(end_time2) AS end_time2 min(diff) AS min_diff BY id	
| sort min_diff
| head 1

or

<your-search>
| eval diff=strptime(start_time2,"%Y-%m-%dT%H:%M:%S")-strptime(end_time1,"%Y-%m-%dT%H:%M:%S")
| sort diff
| head 1

Ciao.

Giuseppe

0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...