Splunk Search

How to start span at midnight

mkarimi17
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

maciep
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

DalJeanis
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

mkarimi17
Path Finder

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

0 Karma

maciep
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.

0 Karma

DalJeanis
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

mkarimi17
Path Finder

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

maciep
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

mkarimi17
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...