How to get a single value visualization to display "0" instead of "N/A" when there is no matching event?

[dbcase]

Hi,

I have a very simple search:

host="ccivirpxa0*" index=cox sourcetype=wls_managedserver STUCK|timechart count

with a visualization of single value. It works fine as long as the time value selected has an event that matches. Where there is no match, it returns N/A which I kinda understand. I'd like it to return "0". I've tried several of the other solutions that I've seen on the Answers, but no luck.

[helius]

I've been using the following which is way easy:

| stats dc($specific_field_here)

[alexeyglukhov]

Totally agree - much easier !

[bandit]

If you were able to accept just a stats count instead of a timechart count, you possibly could do like this. I'm appending count of zero to the search results and then just using the first record with the head command.

host="ccivirpxa0*" index=cox sourcetype=wls_managedserver STUCK | stats count
| append [ | makeresults | eval count=0 | table count ]
| head 1

Rob

[pisit_t]

it's work for me !

[bandit]

Don't know that this will be perfect but may suit your needs. Essentially I'm just pre-pending a dummy event with a count of zero.

| makeresults | eval count=0 | table _time count
| append [ search host="ccivirpxa0*" index=cox sourcetype=wls_managedserver STUCK | timechart count ]

Rob

[MuS · Accepted solution]

Hi dbcase,

look at this answer https://answers.splunk.com/answers/196554/timechart-fill-values-in-empty-slots.html where you can find an example an another link to another answer.

Hope this helps ...

cheers, MuS

UPDATE: Try this run everywhere search:

index=_internal 
| eval hasSource=if(searchmatch("source"),1,0)
| stats count as myCount sum(hasSource) AS hasSource by _time
| bin _time span=1min
| chart sum(hasSource) AS count over _time

it will check the events for the field called source and counts them, the bin sets the _time to the span value and the chart will display it like timechart (timechart is nothing else but a chart ... over _time by ...)

UPDATE OF UPDATE: The final working search was this:

host="ccivirpxa0*" index=cox sourcetype=wls_managedserver
| eval isEvent=if(searchmatch("STUCK"),1,0)
| stats count as myCount sum(isEvent) AS isEvent
| eval result=if(isEvent>0, isEvent, myCount)
| table result

[dbcase]

Hi Mus!

Thanks for the hint but still no joy

This is my updated query host="ccivirpxa0*" index=cox sourcetype=wls_managedserver STUCK|stats count AS myCount by _time|timechart sum(myCount) as count

[MuS]

see the updated answer 😉

[dbcase]

Hmmmm, ok I feel like an idiot :)..... Here is the updated updated query but still nada. It just so happens that there is a field named "source" in my data. Where am I going off the rails?

host="ccivirpxa0*" index=cox sourcetype=wls_managedserver STUCK| eval hasSource=if(searchmatch("source"),1,0)
 | stats count as myCount sum(hasSource) AS hasSource by _time
 | bin _time span=1min
 | chart sum(hasSource) AS count over _time

[MuS]

Give this a try:

host="ccivirpxa0*" index=cox sourcetype=wls_managedserver 
| eval hasSource=if(searchmatch("STUCK"),1,0)
| stats count as myCount sum(hasSource) AS hasSource by _time
| bin _time span=1min
| chart sum(hasSource) AS count over _time

[dbcase]

Ah! Now I see what you were shooting for Duh! But....... Still "No results found" is the result. Which is technically correct but not visually pleasing.

[dbcase]

If I strip it down to the first stanza still "no results found"

host="ccivirpxa0*" index=cox sourcetype=wls_managedserver | eval hasSource=if(searchmatch("STUCK"),1,0)|table hasSource

[MuS]

hmmm, how about this one:

index=_internal sourcetype=splunkd splunk_server=local earliest=-1h@h latest=-0h@h 
 | eval isEvent=if(searchmatch("source"),1,0)
 | stats count as myCount sum(isEvent) AS isEvent
 | eval result=if(isEvent>0, isEvent, myCount)
 | table result

This should return a single number count and this one

index=_internal sourcetype=splunkd splunk_server=local earliest=-1h@h latest=-0h@h foobbooobafdlkjhsdflkjhsrjkfhrejkghlkarsg
 | eval isEvent=if(searchmatch("source"),1,0)
 | stats count as myCount sum(isEvent) AS isEvent
 | eval result=if(isEvent>0, isEvent, myCount)
 | table result

should return a 0

[dbcase]

Well, both return a 0 soooooo maybe you are on to something 🙂 I'll work on the query syntax to get it to apply to the query. Cross your fingers!!!

[dbcase]

Hey Hey! This works!!!!! Thanks MuS!!!!

host="ccivirpxa0*" index=cox sourcetype=wls_managedserver
  | eval isEvent=if(searchmatch("STUCK"),1,0)
  | stats count as myCount sum(isEvent) AS isEvent
  | eval result=if(isEvent>0, isEvent, myCount)
  | table result

[dbcase]

well I typed too soon...... When table result is changed to timechart values(result) or chart values(result) by _time we are back to no results found.

However stats values(result) works but not when you add by _time

[MuS]

I'm sure you will get the final search now 😉
Just one thing, why would you use timechart to display a single value result / visualisation ? As soon as you display some value by _time the single value makes no sense to me - except you want to compare it to a previous time range and use the trend indicators.... some fancy search stuff can be found here https://answers.splunk.com/answers/390574/how-to-create-a-search-that-shows-a-trending-value.html

[MuS]

Nice, I'll update the answer in this case 😉