Timespan single value by day excluding yesterday, but maintaining the entire 24 hour count of yesterday and the previous days

[clozach]

All I want to do is display a single value of yesterdays entire 24 hour count compared to that of the previous day/year. Below is the command I've been trying and I've been messing with it, but this is the general understanding.

index=myindex action=Blocked earliest=-7d latest=-1d | timechart count

This search runs but displays an incorrect number for the yesterday count which I know to be 19497, but instead the search displays 4936. I'm assuming it's doing this because of how the time modifiers are set up and it is only giving me the count for yesterday up until the current time it is now, but yesterday.

i.e. if I ran the search at 12 noon, it would return the values of the start of yesterday until 12. This is the only explanation I can think of.

[landen99]

Snap the time frame to the day with "@d"

index=myindex action=Blocked earliest=-7d@d latest=-1d@d | timechart span=1d count

1 - Make sure that the "action" field is being extract correctly for all desired events.
2 - Ensure that all events are being indexed in a timely manner
3 - Check that the events are being indexed with the correct times (timestamp parsing).
4 - Check the timezones for the timestamps and the timezone configuration in props.
5 - Check if udp is being used; possible packet dropping.
6 - Check the timezone of the Splunk user's profile.

[woodcock]

Be aware that the definition of Yesterday depends on your Time zone and for splunk searches, this is a user-level setting found at <Your login> -> Preferences -> Time zone. So the I know to be 19497 depends on a certain Time zone value which is almost certainly different than your user's value. Fix this before anything else and what you have already should work fine.

[rbechtold · Accepted solution]

Hey Clozach,

I hope I'm understanding the question correctly. I've recreated what I believe your data-set may look like. Copy and paste this into any Splunk window and let me know if this doesn't look correct.

| gentimes start=7/10/19 end=7/13/19 increment=2s
| eval action = random()%2
| eval action = if(action = 0, "Blocked", "Allowed")
| rename starttime AS _time

I believe the problem you're running into is that you're allowing Splunk to designate the time-frame based on the timechart command. To fix this, we can use the span argument with timechart.

You also mentioned that you're suspicious that 24 hour period is starting from whenever you run the search, and you are correct.
(i.e. if you run ...|timechart count at 3:25 pm, it will look from 3:25 pm today back until 3:25 pm yesterday).

The way we can fix this is adding a "snap-to-time" option to our span argument. This will make sure that yesterday will always be a 24 hour period, as well as all days before that. If you'd like more information on how to use the snap-to-time option, refer to the documentation here: https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Timechart

I've also added an eval statement that checks if the day is today and eliminated it from the search results, so you will only see a count for yesterday and the days before.

If your data-set looks like the data-set I've created above, this search should work for you:

...BASE SEARCH...
| timechart count(eval(action="Blocked")) AS count span=1d@d
| eval daycheck = strftime(_time, "%Y-%m-%d")
| eval today = strftime(now(), "%Y-%m-%d")
| where daycheck != today
| table _time count

Please let me know if I'm interpenetrating the question incorrectly, or if this doesn't work for you!