Why does my single value and trend visualization does not show up when there is no data within the time range?

[Masterbaker]

Hi there!

I'd like to display a single value (with trend and sparkline) for displaying the count of specific events (number of errors in a log file) for the last 24 hours. Makes for a nice live dashboard panel. Seemed simple enough...

If i use "single value" visualization with trend and sparkline (with a timechart command), the following works just fine... as long a there is data within the selected timerange.

index=myIndex CRITICAL earliest=-1d| timechart count 

As soon as there is no data, (in this example, if there are no events with the word CRITICAL in the last 24h) the dashboard panel switches to an ugly "no results found" instead of displaying a nice green "0", and people get nervous! Not good.

The following WILL give me a numerical result even with 0 results in the last 24h, but since it's not done using timechart the trend and sparkline won't display.

index=myIndex CRITICAL earliest=-1d| stats count

Any way to get this done? Get a "0" single value, with trend and sparkline, that will still display when there are 0 events in the logs, ideally without resorting to sophisticated splunk voodoo?

(I've dabbled in many of the solutions suggested from these forums, with no success; Some simply don't work (eg : fillnull), or they seemed so complicated they scared me off.

Another way to put it : Why can't "timechart count" generate stats (which can be used in a vizualisation) when there are no results from the search? A good old "0" would do the trick here. After all, it does exactly that as soon as it finds a single event (for example, If I have a single event in the last 24h at, say at 10am, it will fill all other hours (ex : 8am, 9am, 11am) with zeroes... and will give me a nice trend and sparkline. Why can't it do the same when there are no results?

Thanks!

[Masterbaker]

All due respect to somesoni2's solution - it works fine - but it feels complicated and I'm pretty sure that many junior splunkers (such as myself) will have a hard time just understanding what it does...

Anyone has a more straighforward or simpler solution? Thanks!

[paul_thomas_bb]

Try this - used for similar issue when performance logs not arriving within time range.

| timechart count
| where isnotnull(count) <---remove null results
| appendpipe

[ stats count <---append additional row to statistics with the count result
| where count=0 <----removes dummy row if there are results
]

[somesoni2 · Accepted solution]

Give this a try

index=myIndex CRITICAL earliest=-1d| timechart count 
| appendpipe [| stats count | where count=0 | addinfo | eval time=info_min_time." ".info_max_time | makemv time | mvexpand time | table time count | rename time as _time ] 

This will give a 0 count and flat trendline where there are no results. You may not get your green color though by default.

[mariamathewtel]

Hi, Is there any way to change the color to Zero or picking from the given color range?

[Masterbaker]

I'll admit I'll have to spend time to understand what you did there - seems like a complex formula to fix this simple issue. But it works! Thanks mate.