why timechart is not working in this query?

[amaurya1]

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?

[richgalloway · Accepted solution]

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

[amaurya1]

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

[amaurya1]

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?

[richgalloway]

Have you tried my query?

[amaurya1]

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