Dashboards & Visualizations

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

dbcase
Motivator

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.

0 Karma
1 Solution

MuS
Legend

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

View solution in original post

helius
Path Finder

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

| stats dc($specific_field_here)

alexeyglukhov
Path Finder

Totally agree - much easier !

0 Karma

bandit
Motivator

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
Engager

it's work for me !

0 Karma

bandit
Motivator

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

0 Karma

MuS
Legend

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
Motivator

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

0 Karma

MuS
Legend

see the updated answer 😉

0 Karma

dbcase
Motivator

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
0 Karma

MuS
Legend

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
0 Karma

dbcase
Motivator

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.

0 Karma

dbcase
Motivator

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
0 Karma

MuS
Legend

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

0 Karma

dbcase
Motivator

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!!!

0 Karma

dbcase
Motivator

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
0 Karma

dbcase
Motivator

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

0 Karma

MuS
Legend

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

0 Karma

MuS
Legend

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

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...