stats by date_hour failing to return results

[anssntaco]

Been scratching my head about this one...

This search returns a value:

index=os source=cpu host=myhost | stats avg(pctUser)

This one returns "No results found.":

index=os source=cpu host=myhost | stats avg(pctUser) by date_hour

Am I forgetting to do something? Shouldn't that 2nd search just work?

P.S. I'm running 4.3.2

[lguinn2 · Accepted solution]

Instead of using date_hour, which is not always present, use this search:

index=os source=cpu host=myhost 
| eval hour = strftime(_time,"%H")
| stats avg(pctUser) by hour

This should work for any search.

[anssntaco]

Agreed... though my only confusion, from a user perspective, is why these fields rely on information which may not necessarily exist in the event (see comments in other answer). Why not use _time as the basis for populating those default fields?

[lguinn2]

I don't consider this a workaround. I consider it the "right answer" as it works for all searches AND it uses the normalized timestamp, which will therefore work properly across multiple timezones.

[anssntaco]

As a workaround, I've been doing exactly what you described. Thanks! ... and sorry for not responding sooner.

[yannK]

The fields date_hour is automatically generated by splunk at search-time, based on the timestamp.
(like date_month, date_day, etc...)

to check that all the fields are present, look at your events field by field.
index=os source=cpu host=myhos | table _time date_hour pctUser

[anssntaco]

Ok, so perhaps that's where I'm confused. Using _time instead of a timestamp in _raw would guarantee that a) these default fields would always exist, and b) they'd be normalized.

Do you know the rationale for using the timestamp in _raw instead of _time?

[lguinn2]

Yes, date_hour and similar fields are extracted from _raw. So if an event does not have a timestamp in _raw, these fields will not be present.

Also, I don't believe that these fields are "normalized" based on their timestamp - their values are extracted from _raw unchanged. This could be a problem if you have events from multiple timezones.

[anssntaco]

AFAICT, the main difference between the scripted vs non-scripted inputs is that the scripted do not include the event timestamp in _raw. Could that be what's going on? Does the raw event text have to include a timestamp (which splunk uses for _time)?

[anssntaco]

sorry for the late answer...

I did a little more digging, and found that it's only the scripted *NIX inputs that fail to have this field auto-extracted. Here's the search that I ran to get a sense of the sources missed the date_wday field:

 host=xyz index=os | eval does_date_wday_exist=if(isnotnull(date_wday),"true","false") | chart count over source by does_date_wday_exist 

For example, the cpu, iostat, lastlog, lsof, and df did not have these date_* fields auto-extracted. However, "/var/log/cron", "/var/log/secure", and "/var/log/messages" did.

[yannK]

double check that you don't have the automatic field discovery disabled (left panel)

[anssntaco]

date_hour is not present (nor are date_month, date_day, date_wday).

Is there something that needs to be done to make those fields get auto-extracted (i.e. is there a config for it)?

[Ayn]

Do you actually see the date_hour field for the logs you're running stats on? It's not present for all events.