Splunk Search

How to start span at midnight

Path Finder

I have a search:

| tstats count AS ThreeHourCount WHERE earliest=-2d@d latest=now index=* by index, _time span=3h

which needs to run every 3 hours and give me the total count for that range. however, span of anything besides 1h starts the time besides midnight. For example in this case, it starts at 2:00 -> 23:00

Also, just to clarify, if you do a span of 3 hours, the count is representative of the previous 3 hours, correct?

0 Karma
1 Solution

Champion

Hopefully somebody out here knows why that is happening. I was able to verify that with tstats and timechart running over the same interval where "now" was in the 8pm hour. Interestingly 1h, 2h, 4h, 5h all seemed to work right (6h also didn't work).

Also, i'm sure there is a prettier way to do this in Splunk, but maybe this (or something better) could be used as a workaround in the meantime? Assuming you'll always start at midnight...

| tstats count WHERE earliest=-2d@d latest=now index=* by index, _time span=1h 
| eval group_by = floor(tonumber(strftime(_time,"%H"))/3) 
| stats min(_time) as _time sum(count) AS ThreeHourCount by index group_by 
| fields - group_by

UPDATE: realized this will not work across days, but something similar could be done using streamstats count.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

That should not be happening.

Potentially, I suspect it is a display issue, perhaps, having to do with time zone.

The idea is that events might be being stored in UTC, but being presented to you in local time.

To investigate, try this and check the time zone in mytime ...

| tstats count AS ThreeHourCount WHERE earliest=-2d@d latest=now index=* by index, _time span=3h
| eval mytime=strftime(_time,"%Y-%m-%d %H:%M:%S %Z")
0 Karma

Path Finder

no this mytime confirms that logs end up being displayed as my own time.

0 Karma

Champion

Hopefully somebody out here knows why that is happening. I was able to verify that with tstats and timechart running over the same interval where "now" was in the 8pm hour. Interestingly 1h, 2h, 4h, 5h all seemed to work right (6h also didn't work).

Also, i'm sure there is a prettier way to do this in Splunk, but maybe this (or something better) could be used as a workaround in the meantime? Assuming you'll always start at midnight...

| tstats count WHERE earliest=-2d@d latest=now index=* by index, _time span=1h 
| eval group_by = floor(tonumber(strftime(_time,"%H"))/3) 
| stats min(_time) as _time sum(count) AS ThreeHourCount by index group_by 
| fields - group_by

UPDATE: realized this will not work across days, but something similar could be done using streamstats count.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

Replace line 2 with this...

 | bin _time as group_by span=3h

or do this

 | eval group_by = floor(tonumber(strftime(_time,"%H"))/3) 
 | bin _time as myday span=1d
 | stats min(_time) as _time sum(count) AS ThreeHourCount by index group_by myday
 | fields - group_by myday

Path Finder

Thank you both. So in this case, the count is for the previous 3 hours correct?

Champion

It's for the following 3 hours. The 12am interval includes, 12am, 1am and 2am. The 3am interval includes 3am, 4am, and 5am...and so on. If span worked, i think that's the way it would work.

if you want it to be the other way, a few more commands can probably handle that.

0 Karma

Path Finder

no need. I just wanted to make make sure. thanks!

0 Karma