I have the below query:
My Search query returns a value when it finds some result whereas when it doesn't find any matching events it returns as "No Results Found".
Now, I would like to display as "0" instead of "No Results Found" and return the values if it gets any events as before.
Sample search query:
| chart count AS event_count by text
Add this to the bottom of your search SPL string:
| appendpipe [stats count | where count=0]
Hey, @santosh_hb, come back here and click Accept
to close your question!
Add this to the bottom of your search SPL string:
| appendpipe [stats count | where count=0]
what if I want to print 100.00% instead of zero
@woodcock, works like a charm, thanks!
I got this from @martin_mueller. Be sure to click Accept
if this is the best solution and UpVote
anybody who helped or has other working solutions.
@santosh_hb, you have several options to handle no data found scenario gracefully. As stated by @kamlesh_vaghela, handling the same using append
and dedup
is one of the options. Other one is to use $job.resultCount$
to fin out whether the search returned results and then set/unset token to show/hide required data/panel (get Splunk Dashboard Example
app which explains this scenario).
Following is the run anywhere search for attached screenshot:
<form>
<label>Replace No Results with zero</label>
<fieldset submitButton="false">
<input type="time" token="tokTime" searchWhenChanged="true">
<label></label>
<default>
<earliest>-15m</earliest>
<latest>now</latest>
</default>
</input>
</fieldset>
<row>
<panel>
<title>Option 1 - Handler using append in Splunk Search</title>
<table>
<search>
<query>index=_internal sourcetype=splunkd log_level="ERROR"
| chart count as Counter by log_level
| append [| makeresults
| eval log_level="ERROR"
| eval Counter=0
| fields - _time]
| dedup log_level</query>
<earliest>$tokTime.earliest$</earliest>
<latest>$tokTime.latest$</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">20</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">none</option>
<option name="percentagesRow">false</option>
<option name="refresh.display">progressbar</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
</table>
</panel>
</row>
<row>
<panel>
<title>Option 2 - Handler using $job.resultCount$ and depends/rejects attributes</title>
<table depends="$tokShowResults$">
<search>
<query>index=_internal sourcetype=splunkd log_level="ERROR"
| chart count as Counter by log_level</query>
<earliest>$tokTime.earliest$</earliest>
<latest>$tokTime.latest$</latest>
<sampleRatio>1</sampleRatio>
<done>
<condition match="$job.resultCount$==0">
<unset token="tokShowResults"></unset>
</condition>
<condition>
<set token="tokShowResults">true</set>
</condition>
</done>
</search>
<option name="count">20</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">none</option>
<option name="percentagesRow">false</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
</table>
<html rejects="$tokShowResults$">
<div style="font-weight:bold;color:red;font-size:150%;text-align:center">No Results! Please expand search window.</div>
</html>
</panel>
</row>
</form>
@niketn your query is what I exactly looking for . Thank you so much
Hi
can you please try below search??
| chart count AS event_count by text | append [| stats count as event_count]
With above search, you will get text field BLANK, bcoz we don't have a data. If you want to place any static value into text fields then use below search.
| chart count AS event_count by text | append [| stats count as event_count| eval text="YOUR TEXT"]
I hope this will help you
Thanks
@kamlesh_vaghela - Using appendpipe
, rather than append
, will execute the pipeline against the current record set, and add the new results onto the end.
Then, if there are any results, you can delete the record you just created, thus adding it only if the prior result set is empty.
| appendpipe [| stats count as event_count| eval text="YOUR TEXT" | where event_count = 0 ]
FYI @niketnilay, this strategy is instead of dedup
, rather than in addition.
@DalJeanis, yes I agree and I first tested with appendpipe
, but I was getting two rows appended when result was found. So I tried append
as we need to add just one as default. Thanks for pitching in.
Shouldn't final pipe be | search event_count=0
rather than where
?
@niketnilay - search
and where
would be equivalent in the appendpipe
pipeline I posted. That code can only add either one or zero records.
@niketnilay and @DalJeanis I have a similar situation here but unable to implement the solution suggested. I am using | stats count by X, Y
at the end of my query. X has 4 possible values and so does Y resulting in 16 different combinations. I need a count of 0 for each combination that doesn't exist
I am trying | appendpipe [| stats count by X, Y | where count = 0]
to get additional rows with 0 count but it is not working. Can you please tell me what have I misunderstood here?
I can post a new question if required. I wrote it as a comment as I thought it is very relevant here.
Thanks in advance
@niketnilay, i tried you solution of adding the tokens , it works but when there are results i can still see the HTML messages.
@macadminrohit for us to assist you further, we would need to see your Simple XML code.
search event handler for timechart and the depends and rejects tokens that have been applied on timechart and html panel respectively. Since this is a question from last year, I would recommend you posting a new question with the required details.
@kamlesh_vaghela, | dedup text
needs to be added to your current search. In case event_count by certain text is returned, you need pick only the one returned by search and not the default zero count appended through append command.