topic How can I manipulate time stamps to "snap" to 5 minute intervals in Splunk Search

How can I manipulate time stamps to "snap" to 5 minute intervals

jcouture — Mon, 05 Jun 2017 15:15:46 GMT

Hello, I'm joining two tables in splunk and their only common attribute is time. This works well 99% of the time. Both data sets have time stamps every 5 minutes. Occasionally one side of the data has a timestamp that is 1 minute off. Is there a way in splunk to detect the "off" timestamp and round it to the nearest 5 minute mark?

The timestamps are pieces of the data I am dealing with. They are not Splunk timestamps.

Thank you for your time.

Re: How can I manipulate time stamps to "snap" to 5 minute intervals

micahkemp — Mon, 05 Jun 2017 15:24:36 GMT

<your search> | bin span=5min _time | <your stats/join/whatever>

bin command docs

Re: How can I manipulate time stamps to "snap" to 5 minute intervals

jcouture — Mon, 05 Jun 2017 15:30:15 GMT

How does this know to look at the timestamp I'm examining? I should also mention this is not a Splunk timestamp it is an attribute of the table that I have pulled into Splunk from a Db

Re: How can I manipulate time stamps to "snap" to 5 minute intervals

micahkemp — Mon, 05 Jun 2017 15:41:13 GMT

You can specify a different timestamp:

| bin span=5min <timestamp field>

You may need to look into how that field needs to be formatted to work with the bin command.

Re: How can I manipulate time stamps to "snap" to 5 minute intervals

jcouture — Mon, 05 Jun 2017 16:27:07 GMT

Ok, thank you for your help. I'll test it the next time I have an anomaly.

Re: How can I manipulate time stamps to "snap" to 5 minute intervals

DalJeanis — Mon, 05 Jun 2017 17:56:13 GMT

1) The span=5m assumes your data is formatted as epoch-time ... as a number (integer or float) where +1 = +1 second.

2) bin truncates the timestamp to the preceding 5m interval.

3) If your timestamp is occasionally off by 1 minute, I'd recommend doing a histogram on each source with a span to see where the breaks are. For example, If source 1 item timestamps are at 4:20-7:45 and 9:15-2:27 minutes, then I'd recommend adding 50 seconds before the bin command, so that the former bin to 5:00 and the latter to 0:00.

(your search for one source ) | bin mytime as mytime600 span=10m | bin mytime as mytime10 span=10s | eval mytime0=mytime10-mytime600 | chart count by mytime0

Either way, you're going to have to play around a bit to make sure you are cutting the events on each side at the right spot so that they match up.

Re: How can I manipulate time stamps to "snap" to 5 minute intervals

jcouture — Mon, 05 Jun 2017 19:38:45 GMT

Very helpful explanation, thank you. I think this should work. I'll update the post when I have found my solution.