Splunk Search

why timechart is not working in this query?

amaurya1
Explorer

index=abc sourcetype=xyz earliest=-65h latest=-61h
|stats count as Fail by school
|where like (school, "%public%")
|timechart span=1h sum(Fail) as Fail

I want to get the sum of fails per hour but I don't know why it is showing "no result" as the output. If I use stats instead of timechart (stats sum(Fail) as Fail), then it gives me the total sum of fails but since I want per hour count therefore I wanted to have timechart in the query.
Can someone please help?

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The stats command filters out all fields except those used in its arguments so there is no _time field for timechart to use.

Try this query

index=abc sourcetype=abc school="*public*" | bin span=1h _time
|stats count as Fail by _time host
| timechart span=1h sum(Fail) as Fail
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The stats command filters out all fields except those used in its arguments so there is no _time field for timechart to use.

Try this query

index=abc sourcetype=abc school="*public*" | bin span=1h _time
|stats count as Fail by _time host
| timechart span=1h sum(Fail) as Fail
---
If this reply helps you, Karma would be appreciated.
0 Karma

amaurya1
Explorer

Hi @richgalloway thanks for your answer.. yes you are correct, the first stats comment removed the time field. I've update the query and now it's working fine.

index=abc sourcetype=xyz earliest=-65h latest=-61h
|stats min(_time) as _time, count as Fail by school
|where like (school, "%public%")
|timechart span=1h sum(Fail) as Fail

0 Karma

amaurya1
Explorer

Hi @richgalloway But one problem with this new query... Now it is giving the sum of fails only in the earliest hour (ie. has the sum of all the 5 hours), all the ther rows have tie stamp for each hour but the sum of Fails is null against them.. Can you please suggest what should I use instead of min(_time) in stats?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Have you tried my query?

---
If this reply helps you, Karma would be appreciated.
0 Karma

amaurya1
Explorer

Yeah.. I tried that one and it's working fine.. I'll use yours.. thanks

0 Karma
Get Updates on the Splunk Community!

Demo Day: Strengthen Your SOC with Splunk Enterprise Security 8.1

Today’s threat landscape is more complex than ever. Security operation centers (SOCs) are overwhelmed with ...

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...