topic Re: retrieving timechart values when there are no events in Splunk Search

retrieving timechart values when there are no events

ashish198511 — Sun, 19 Jan 2020 11:14:00 GMT

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.

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

Re: retrieving timechart values when there are no events

richgalloway — Sun, 19 Jan 2020 15:12:33 GMT

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

Re: retrieving timechart values when there are no events

niketn — Wed, 30 Sep 2020 03:45:30 GMT

[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.

Re: retrieving timechart values when there are no events

ashish198511 — Sun, 19 Jan 2020 19:21:05 GMT

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

Re: retrieving timechart values when there are no events

woodcock — Sun, 19 Jan 2020 20:13:25 GMT

Like this:

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

Re: retrieving timechart values when there are no events

niketn — Sun, 19 Jan 2020 20:20:35 GMT

Please find updated answer!

Re: retrieving timechart values when there are no events

niketn — Sun, 19 Jan 2020 20:25:18 GMT

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 😞

Re: retrieving timechart values when there are no events

to4kawa — Sun, 19 Jan 2020 21:16:18 GMT

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

simple and great!

Re: retrieving timechart values when there are no events

woodcock — Sun, 19 Jan 2020 23:09:37 GMT

I am full of IT. Ask anybody.

Re: retrieving timechart values when there are no events

ashish198511 — Mon, 20 Jan 2020 03:41:56 GMT

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

Re: retrieving timechart values when there are no events

ashish198511 — Mon, 20 Jan 2020 04:35:48 GMT

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

Re: retrieving timechart values when there are no events

ashish198511 — Mon, 20 Jan 2020 09:37:19 GMT

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?

Re: retrieving timechart values when there are no events

woodcock — Mon, 20 Jan 2020 10:33:07 GMT

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.

Re: retrieving timechart values when there are no events

ashish198511 — Mon, 20 Jan 2020 11:16:54 GMT

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

Re: retrieving timechart values when there are no events

woodcock — Mon, 20 Jan 2020 12:32:00 GMT

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