Splunk Search

retrieving timechart values when there are no events

ashish198511
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?

1 Solution

woodcock
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

woodcock
Esteemed Legend

Like this:

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

ashish198511
Explorer

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?

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

ashish198511
Explorer

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

ashish198511
Explorer

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

0 Karma

ashish198511
Explorer

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

0 Karma

to4kawa
Ultra Champion

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

simple and great!

niketn
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 😞

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

woodcock
Esteemed Legend

I am full of IT. Ask anybody.

0 Karma

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

ashish198511
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

niketn
Legend

Please find updated answer!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

richgalloway
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, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...