retrieving timechart values when there are no events

[ashish198511]

I am running following query in Splunk
index=appName build=xyz logLevel=ERROR | timechart span=1d count As value.

if there are any events, then it will return the data in the following columns [result 1]; which is exactly what I am looking for

result 1
_time | value
19-Jan-2020 | 10
18-Jan-2020 | 14

The problem is when there are no results. it returns 0 events. What I want is the output should be something like this [result 2] when there are no results for my query

result 2
_time | value
19-Jan-2020 | 0
18-Jan-2020 | 0

I tried improving my query and I was able to achieve result 2, with query 2, but when there are events as result of my query the output gets messed up.

query 2
index=appName | timechart span=60 count as valueInner | appendcols [| search index=appName build=xyz lvl=ERROR | bucket _time span=60s | stats count as value ] | fillnull value=0

How should I write a query which should give _time and value column irrespective of the result of events?

[woodcock · Accepted solution]

Like this:

index="appName" AND sourcetype="YourSourceTypeHere" AND build="xyz" AND logLevel="ERROR"
| append [| makeresults]
| timechart span=1d count(logLevel) AS value

[ashish198511]

Well the answer provided by @woodcock is correct and I have accepted it as answer but the makeresults adds a row in the output. Is it possible to remove this row added by makeresults?

[woodcock]

I changed your count to count(logLevel) which is VERY important. You must not have added/noticed that because without that it does what you are seeing.

[ashish198511]

@woodcock I agree with your point.
Is there a way to trim _time row added by makeresults in the final output?

[woodcock]

There isn't one; all events are consumed by timechart.

[ashish198511]

@woodcock this adds an additional row with value "1" when there are no events. Is there any way to skip it?

[ashish198511]

@woodcock realized the issue in my query. Now I am getting correct results.

[to4kawa]

till now, avoiding "No Results Found" is |appendpipe [eval count=0]
from now, | append [| makeresults]

simple and great!

[niketn]

Simple solution of adding a dummy event using makeresults to ensure "No Results Found" message is not displayed! 🙂

I went for generating dummy event for each time span 😞

[woodcock]

I am full of IT. Ask anybody.

[niketn]

[UPDATED ANSWER]

Documenting query with makeresults command for generating 60 rows for last 60 seconds.

index=_internal log_level="INFO" earliest=-60s latest=now 
| timechart span=1s count 
| fillnull value=0 
| append 
    [| makeresults count=60 
    | eval count=1 
    | accum count 
    | eval _time=_time-count 
    | eval count=0]
| dedup _time

---

@ashish198511 if you intend is to show a timechart of all 0 count in case search returns No Results, instead of showing the No Results Found message, you can use the following append logic with gentimes (similar logic can be built with makeresults command as well). The append logic creates a timechart of 0 values and performs a final dedup to keep count from original timechart command if it exists.
Following run anywhere example is based on Splunk's _internal index. Change the log_level from ERROR to FATAL (which rarely happens) and you will see that you get timechart of all 0 count instead of No Results Found.

index=_internal log_level="ERROR" earliest=-2d@d latest=now
| timechart span=1d count 
| fillnull value=0
| append
    [| gentimes start=-2 end=+1 increment=1d
    | fields starttime
    | eval _time=starttime,count=0
    | fields _time count] 
| fields - starttime
| dedup _time

Other alternative would be to build two panels with depends and rejects based on $job.resultCount$ and show only the panel with gentimes kind of logic above to show 0 count. The depends/rejects logic for No Results has been called out in Splunk Document as well as several questions here on Splunk Answers if you are interested in this approach.

[ashish198511]

Blockquote
@niketnilay Is it possible to give start and end in minutes? I am running this in a 1-minute window with span=1s

[niketn]

Please find updated answer!

[richgalloway]

timechart will fill in missing times if you specify the cont option.

index=appName build=xyz logLevel=ERROR | timechart span=1d cont=true count As value