Splunk Search
Highlighted

retrieving timechart values when there are no events

Explorer

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?

Highlighted

Re: retrieving timechart values when there are no events

SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: retrieving timechart values when there are no events

Legend

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




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: retrieving timechart values when there are no events

Explorer

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

0 Karma
Highlighted

Re: retrieving timechart values when there are no events

Legend

Please find updated answer!




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: retrieving timechart values when there are no events

Esteemed Legend

Like this:

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

View solution in original post

Highlighted

Re: retrieving timechart values when there are no events

Legend

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 😞




| eval message="Happy Splunking!!!"


Highlighted

Re: retrieving timechart values when there are no events

Esteemed Legend

I am full of IT. Ask anybody.

0 Karma
Highlighted

Re: retrieving timechart values when there are no events

Ultra Champion

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

simple and great!

Highlighted

Re: retrieving timechart values when there are no events

Explorer

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

0 Karma