Splunk Search

stats by date_hour failing to return results

anssntaco
Path Finder

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

Tags (2)
1 Solution

lguinn2
Legend

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.

View solution in original post

lguinn2
Legend

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
Path Finder

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?

0 Karma

lguinn2
Legend

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.

0 Karma

anssntaco
Path Finder

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

0 Karma

yannK
Splunk Employee
Splunk Employee

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
Path Finder

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?

0 Karma

lguinn2
Legend

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.

0 Karma

anssntaco
Path Finder

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)?

0 Karma

anssntaco
Path Finder

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.

0 Karma

yannK
Splunk Employee
Splunk Employee

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

0 Karma

anssntaco
Path Finder

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)?

0 Karma

Ayn
Legend

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...