Splunk Search

How do I round the time to up/down to the nearest hour?

darls15
Explorer

Hi

I'm fairly new to Splunk and I need to round my time field up/down to the nearest hour.

For example...

If now returns 09:26:52 I want it to be rounded to 09:00:00, if the time is 14:36:18 then 15:00:00.

I have searched and can't find or understand how to do this. Is there someone help me with how?

Thanks

0 Karma
1 Solution

niketn
Legend

@darls15 , if now is your field you can try the following to round off hours time. However some case()may need to be added/changed as per your use case.

<yourCurrentSearch>
| rex field=now "(?<Hour>[^:]+):(?<Minute>[^:]+):(?<Seconds>\d+)"
| eval rounded_hour=case(Seconds>30 AND Minute>29,Hour+1,
                         Minute>30,Hour+1,
                         true(),Hour).":00".":00"

Following is run anywhere example you can use to test:

| makeresults 
| eval now="14:36:18"
| rex field=now "(?<Hour>[^:]+):(?<Minute>[^:]+):(?<Seconds>\d+)"
| eval rounded_hour=case(Seconds>30 AND Minute>29,Hour+1,
                         Minute>30,Hour+1,
                         true(),Hour).":00".":00"

Above computes 09:29:31 as 09:00:00,
09:30:00 as 09:00:00,
09:30:30 as 09:00:00
but 09:30:31 as 10:00:00.
09:31:00 as 10:00:00,
and 09:31:31 as 10:00:00
So in case your expectations are different. Please change as per your needs.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@darls15 , if now is your field you can try the following to round off hours time. However some case()may need to be added/changed as per your use case.

<yourCurrentSearch>
| rex field=now "(?<Hour>[^:]+):(?<Minute>[^:]+):(?<Seconds>\d+)"
| eval rounded_hour=case(Seconds>30 AND Minute>29,Hour+1,
                         Minute>30,Hour+1,
                         true(),Hour).":00".":00"

Following is run anywhere example you can use to test:

| makeresults 
| eval now="14:36:18"
| rex field=now "(?<Hour>[^:]+):(?<Minute>[^:]+):(?<Seconds>\d+)"
| eval rounded_hour=case(Seconds>30 AND Minute>29,Hour+1,
                         Minute>30,Hour+1,
                         true(),Hour).":00".":00"

Above computes 09:29:31 as 09:00:00,
09:30:00 as 09:00:00,
09:30:30 as 09:00:00
but 09:30:31 as 10:00:00.
09:31:00 as 10:00:00,
and 09:31:31 as 10:00:00
So in case your expectations are different. Please change as per your needs.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

junweii
Observer

Hi @niketn,

I'm fairly new to Splunk but what if I need to round my time field up/down to the nearest 5th minute.

For example...

My time field returns 02/24/2022 09:26:52 I want it to be rounded to 02/24/2022 09:25:00, and if the time is 02/24/2022 14:58:10 then 02/24/2022 15:00:00.

0 Karma

darls15
Explorer

Thank you so much niketnilay ... works perfectly!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...