topic Re: Replacing "No Results Found" with "0" in Splunk Search

Replacing "No Results Found" with "0"

santosh_hb — Mon, 22 Jun 2020 22:32:27 GMT

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

Re: Replacing "No Results Found" with "0"

kamlesh_vaghela — Thu, 12 Oct 2017 11:31:36 GMT

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

Re: Replacing "No Results Found" with "0"

niketn — Tue, 29 Sep 2020 16:09:35 GMT

@santosh_hb, you have several options to handle no data found scenario gracefully. As stated by @kamlesh_vaghela, handling the same using appendand 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>

Re: Replacing "No Results Found" with "0"

niketn — Tue, 29 Sep 2020 16:09:39 GMT

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

Re: Replacing "No Results Found" with "0"

DalJeanis — Thu, 12 Oct 2017 15:27:08 GMT

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

Re: Replacing "No Results Found" with "0"

niketn — Thu, 12 Oct 2017 16:34:36 GMT

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

Re: Replacing "No Results Found" with "0"

DalJeanis — Thu, 12 Oct 2017 16:51:11 GMT

@niketnilay - search and where would be equivalent in the appendpipe pipeline I posted. That code can only add either one or zero records.

Re: Replacing "No Results Found" with "0"

macadminrohit — Fri, 29 Jun 2018 15:22:58 GMT

@niketnilay, i tried you solution of adding the tokens , it works but when there are results i can still see the HTML messages.

Re: Replacing "No Results Found" with "0"

niketn — Sat, 30 Jun 2018 05:52:17 GMT

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

Re: Replacing "No Results Found" with "0"

woodcock — Sun, 01 Jul 2018 02:31:31 GMT

Add this to the bottom of your search SPL string:

| appendpipe [stats count | where count=0]

Re: Replacing "No Results Found" with "0"

ebruozys — Tue, 28 Aug 2018 07:47:28 GMT

@woodcock, works like a charm, thanks!

Re: Replacing "No Results Found" with "0"

woodcock — Tue, 28 Aug 2018 13:18:05 GMT

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.

Re: Replacing "No Results Found" with "0"

woodcock — Fri, 08 Mar 2019 17:24:52 GMT

Hey, @santosh_hb, come back here and click Accept to close your question!

Re: Replacing "No Results Found" with "0"

vinaysathyanara — Thu, 13 Feb 2020 10:09:38 GMT

@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

Re: Replacing "No Results Found" with "0"

dharudiya — Thu, 18 Jun 2020 10:24:32 GMT

@niketn your query is what I exactly looking for . Thank you so much

Re: Replacing "No Results Found" with "0"

Siddharthnegi — Wed, 11 Oct 2023 09:46:32 GMT

what if I want to print 100.00% instead of zero