Return 0 If Search Returns "No Results Found"

[Bytes]

Hello All,

I am trying to make it so that when a search string returns the "No Results Found" message, it actually displays a zero.

Here's what I am trying to achieve. I have a single value panel. I have this panel display the sum of login failed events from a search string. However, when there are no events to return, it simply puts "No results found" or "N/A". I want this to display a 0 as it is much easier on the eye (you know there were no results as opposed to thinking "did my search fail?").

Here's the search I have so far that will either return the sum of all failed login events, or the "No results found" message:

index=infrastructure
sourcetype=linux_secure
"Too many authentiction failures"
| rex "failures for (?<account>[\w\.]+)"
| search account=* host=*
| stats count as failures by account
| stats sum(failures) as sub_failures
| eval total_failures = if(isnull(sub_failures),"0",sub_failures
| fields total_failuress

It will return the total number of login failed events if any are generated. However if there are none, it will display "No results found" hwere I really want it to just display 0.

Anyone willing to help a buried Splunker 😛

[jason_hotchkiss]

index=infrastructure
sourcetype=linux_secure
"Too many authentiction failures"
| rex "failures for (?<account>[\w\.]+)"
| search account=* host=*
| stats count as failures by account
| stats sum(failures) 

I know this is an old post, but like me, I ran into this same problem.  This is how I solved it, by adding: after the | stats sum
| appendpipe [| stats count as failures | where failures=0 ]

[nagaramsai]

Try this adding at the end of query.

index.... sourcetype..... |appendpipe [stats count | where count==0]

Thanks,
Sai
,Try this adding at the end of query.

index=...... sourctype...... |appendpipe [stats count | where count==0]

Thanks,
sai kiran

[benhooper]

For me, that works but causes dashboard panels to use the max range colours for some reason.

I've posted about it here: https://community.splunk.com/t5/Splunk-Search/Append-causing-dashboard-panels-to-use-colours-for-max-ranges/td-p/513293

[to4kawa]

https://community.splunk.com/t5/Splunk-Search/How-to-change-a-single-panel-color-based-on-text-resul...


@benhooper 
how about this?

[airalee]

thanks, this helped me

[proylea]

If you think outside the box a little you could use the panel display feature in the dashboard to just show a different/dummy display when there are no results returned.

For instance I have some single value metrics on a dashboard that normally show N/A if there are no results returned and it also makes the overall panel look a bit untidy.

So I did this:

      <single depends="$result1$">
        <title>'Share' Tracking</title>
        <search>
          <query>| inputlookup user_usage.csv  | search "click on \\\"SHARE\\\"" (name="Toll_DPM_BT_PADATA_DETAILEDUSERACTIONS_AllEnv") (application="*")  NOT (GomezAgent) UserName!="*tollgroup.com" | timechart count span=7d</query>
          <earliest>-30d@d</earliest>
          <latest>now</latest>
          <progress>
            <condition match="'job.resultCount' < 1">
              <set token="fill1">true</set>
              <unset token="result1"></unset>
            </condition>
            <condition>
              <set token="result1"></set>
              <unset token="fill1">true</unset>
            </condition>
          </progress>
        </search>
        <option name="drilldown">all</option>
        <option name="colorBy">value</option>
        <option name="colorMode">block</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x65a637","0x65a637"]</option>
        <option name="rangeValues">[1]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">1</option>
        <option name="link.visible">false</option>
        <option name="underLabel">Compared to Previous Week</option>
        <option name="refresh.time.visible">false</option>
        <drilldown>
          <set token="detail-track">true</set>
        </drilldown>
      </single>
      <single depends="$fill1$">
        <title>'Share' Tracking</title>
        <search>
          <query>| inputlookup fillnull.csv  | timechart count span=7d</query>
          <earliest>-30d@d</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">trend</option>
        <option name="colorMode">block</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x65a637","0x65a637"]</option>
        <option name="rangeValues">[1]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">1</option>
        <option name="link.visible">false</option>
        <option name="underLabel">Compared to Previous Week</option>
        <option name="refresh.time.visible">false</option>
      </single>

The top box shows if there is a result returned and the bottom one shows a dummy result containing zeros if there is no results returned.

The lookup table fillnull.csv would just be something like this

_time count
date 0

[guarisma]

Do you have to constantly update the fillnull.csv with a new date?

[javiergn · Accepted solution]

Use the fillnull command before your last eval:

http://docs.splunk.com/Documentation/Splunk/6.3.1511/SearchReference/Fillnull

[Bytes]

Succes! Fill null did work just needed a tweak.

Here's the code that provided the 0 I was after:

index=infrastructure
sourcetype=linux_secure
"Too many authentiction failures"
| rex "failures for (?<account>[\w\.]+)"
| search account=* host=*
| stats count as failures
| fillnull

This problem seems to be casued by me grouping by accounts (as I was reusing my code from another search string).

[guarisma]

This doesn't work if I'm using timechart instead of stats since I need to show the trend.
Any ideas?

[Nextbeat]

From my understanding, the whole point of using stats at the end of the query is to populate a result for count rather than seeing "No Results Found." Why would you want to do this? For me, I used this as an opportunity to substitute the value "0" for a customized message by using eval. For instance, if inputlookup can't find any results you could tell others to manually search elsewhere or in my case, I defined a conditional value to look for the message. If this condition was met, you could click on the message and it would direct you to a place where you could find what you're looking for. Unfortunately it does not work with timechart, streamstats, eventstats, etc. because they rely on results to be generated. Stats works because it generates a result even if the count is zero. How are you going to show a trend if there are no results?

| inputlookup <table> WHERE <search> | fields <field2> <field1> <field3>  | appendpipe [stats count] | eval <field>=if(isnull(<field>),"<message>",<field>) | fields - count

My example used inputlookup for the search. Fields was used to reorder the table. Appendpipe was used to join stats with the initial search so that the following eval statement would work. Without appending the results, the eval statement would never work even though the designated field was null. Stats served its purpose by generating a result for count=0. Before removing the field, the eval statement substituted a null value for one of the fields with a customized message. Finally, the final pipe removed the count field since it was no longer needed.

[Bytes]

Didn't work I'm affraid. I believe this is because there are no events to perform the stats functions on.

I am essentially telling it to count the events, but if there are no events to count then I think the stats functions won't create any inititated variables (so no variables with NULL to fill with fillnull).

I tried to fill any nulls after the first stats function with

| fillnull value=0 failures

But this didn't work. I have looked around the other answers but cannot find one that helps me in my case.

Any other ideas by chance?