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
Esteemed Legend

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!

Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...

Splunk Observability Cloud | Enhancing Your Onboarding Experience with the ...

We understand that your initial experience with getting data into Splunk Observability Cloud is crucial as it ...