topic Re: Return 0 If Search Returns "No Results Found" in Splunk Search

Return 0 If Search Returns "No Results Found"

Bytes — Tue, 22 Dec 2015 11:10:03 GMT

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 😛

Re: Return 0 If Search Returns "No Results Found"

javiergn — Tue, 22 Dec 2015 11:22:14 GMT

Use the fillnull command before your last eval:

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

Re: Return 0 If Search Returns "No Results Found"

Bytes — Tue, 22 Dec 2015 11:35:35 GMT

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?

Re: Return 0 If Search Returns "No Results Found"

Bytes — Tue, 22 Dec 2015 11:43:54 GMT

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

Re: Return 0 If Search Returns "No Results Found"

proylea — Fri, 21 Oct 2016 05:37:18 GMT

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

Re: Return 0 If Search Returns "No Results Found"

guarisma — Fri, 24 Feb 2017 17:12:08 GMT

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

Re: Return 0 If Search Returns "No Results Found"

guarisma — Fri, 24 Feb 2017 17:15:01 GMT

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

Re: Return 0 If Search Returns "No Results Found"

Nextbeat — Wed, 25 Apr 2018 10:32:25 GMT

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.

Re: Return 0 If Search Returns "No Results Found"

nagaramsai — Tue, 30 Jul 2019 17:49:00 GMT

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

Re: Return 0 If Search Returns "No Results Found"

airalee — Thu, 26 Mar 2020 15:04:55 GMT

thanks, this helped me

Re: Return 0 If Search Returns "No Results Found"

benhooper — Mon, 10 Aug 2020 11:41:59 GMT

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

Re: Return 0 If Search Returns "No Results Found"

to4kawa — Mon, 10 Aug 2020 11:53:36 GMT

https://community.splunk.com/t5/Splunk-Search/How-to-change-a-single-panel-color-based-on-text-result-with/m-p/513120/highlight/true#M143965

@benhooper
how about this?

Re: Return 0 If Search Returns "No Results Found"

jason_hotchkiss — Wed, 30 Sep 2020 19:10:07 GMT

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 ]

Re: Return 0 If Search Returns "No Results Found"

julienbonastre — Sun, 08 Dec 2024 13:29:09 GMT

So glad to see nearly a decade on Splunk still has no way around this issue 💩🤯🤪🚮

Unbelievable...

How hard is it to return a 0 value if there are just no samples within a timespan?

Re: Return 0 If Search Returns "No Results Found"

SanjayReddy — Sun, 08 Dec 2024 17:14:53 GMT

Hi @julienbonastre

thanks for your concern, however some of latest responses for this thread responses posted work for this query in splunk community

also This question was posted a while ago and might not get the attention you need for your own question to be answered. I suggest you please post a brand new question so your issue can get more visibility.

Anyways, I am also giving other posts urls where it is solved. hope this helps

https://community.splunk.com/t5/Splunk-Search/Replacing-quot-No-Results-Found-quot-with-quot-0-quot/m-p/304352

https://community.splunk.com/t5/Splunk-Search/How-can-I-resolve-return-0-for-quot-No-Results-Found-quot/m-p/645819

https://community.splunk.com/t5/Splunk-Search/Display-result-0-rather-than-quot-No-Results-Found-quot/m-p/511204

Re: Return 0 If Search Returns "No Results Found"

PickleRick — Sun, 08 Dec 2024 21:00:12 GMT

Adding to @SanjayReddy 's answer I'll take the opportunity to explain why this actually makes sense.

Firstly, Splunk executes search commands in a pipeline. Each subsequent command knows only the results from the previous step. That's why you have to make sure you have all the data you need for further processing at each step and you can't reference any data you've already filtered out or in any other way "lost" along the way.

Secondly, if stats count were to return 0 in case it got no events on input it would have to be implemented as an explicit exception to a normal stats behaviour. Remember that there are much more aggregation functions than just count for stats and for at least some of them returning a value for zero input rows would make no sense. Like average=0 is definitely not the same as no result at all.

Thirdly, even count can be over some field. How is stats supposed to know whatever values should be expected in those fields?

So this behaviour while maybe a bit inconvenient to handle (actually, it could be worth posting an idea for a generalized "default result" command if there isn't one yet; haven't checked it) it is consistent with the overall stats mechanics.

Re: Return 0 If Search Returns "No Results Found"

julienbonastre — Mon, 09 Dec 2024 01:18:09 GMT

Wow thanks for rapid responses @SanjayReddy and @PickleRick 🙏🏼

Didn't expect such turnaround on my vent in this dead/old thread.

However I really do appreciate the constructive feedback, and I certainly do understand the justification for why the stats/timechart functions as it does, it's just a shame I've been trawling most of those other linked threads and many hours of google searches to find many different suggested approaches, none which oddly seem to fit the bill for what is actually a fairly small, simple query/resultant expectation..

It's one of those things you just think, meh, this takes 30 secs in ANSI SQL, noSQL or any other RDBMS to produce the desired resultant matrix/vector, but in Splunk, I need my masters in SPL 🤣

Anyway, thank you again, wholeheartedly appreciate your positive and responsive attitudes given my pretty low-contribution post 🤗

I will check those threads you've provided which I haven't looked at before and if all else fails, as you've suggested, I'll post afresh 🚀🥰

Re: Return 0 If Search Returns "No Results Found"

bowesmana — Mon, 09 Dec 2024 03:13:16 GMT

Just to reiterate here the general simple solution to this issue in case it gets read again, which has already been posted in this thread.

All you need to do is to add the appendpipe clause to the end of the search like this - where "NOUSER" is assumed not to exist, so without the appendpipe, will return no results found.

index=_audit user=NOUSER | appendpipe [ | stats count | where count=0 ]

Re: Return 0 If Search Returns "No Results Found"

bowesmana — Mon, 09 Dec 2024 03:25:14 GMT

And a technique I use a reasonable amount in dashboards is to have a panel for results and a panel for no results hidden behind tokens, e.g.

<form version="1.1" theme="light"> <label>tmp4</label> <fieldset submitButton="false"> <input type="text" token="user" searchWhenChanged="true"> <label>User</label> </input> </fieldset> <row> <panel> <html depends="$no_results$"> <h1>No results found</h1> </html> <table depends="$has_results$"> <search> <progress> <unset token="has_results"></unset> <unset token="no_results"></unset> </progress> <done> <eval token="has_results">if($job.resultCount$=0, null(), "true")</eval> <eval token="no_results">if($job.resultCount$>0, null(), "true")</eval> </done> <query>index=_audit user=$user|s$</query> <earliest>-24h</earliest> <latest></latest> <sampleRatio>1</sampleRatio> </search> <option name="count">100</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> </form>

Re: Return 0 If Search Returns "No Results Found"

julienbonastre — Mon, 09 Dec 2024 03:25:39 GMT

Thanks @bowesmana , yes, this is the typical "solution" I've seen around, however this does not work on `timechart` and similar time bucket constrained expressions.

Certainly if one is after just a solve for `stats` this definitely does work.

This is my query:

index=* source=squid_proxy_logs | search (warn* OR error*) AND _raw!="*SendEcho*" AND (NOT url=*) AND _raw!="*setrlimit: RLIMIT_NOFILE*" | timechart span=5m count(_raw) as hits

I've tried appendpipe, append etc tricks with a variety of expressions such as:

and a few other alternates I've seen around, but all have the same issue, they work great on a single vector stats return result being null/empty, but with the timechart this doesn't really play well unfortunately..

I think the closest I can get is where I have to makeresults myself into the spans and bins I need and then use a query to aggregate the counts into those predefined bins I've carved up, and these bins of course would be generated based on the search query timerange so it would work for historical periods as well as realtime ...

Just need to rejig my query I think to do something like this so it always produces a fixed matrix/tabular output and with the respective count values for that point in time, rather than trying to build a dataset from where there are just zero values to start with (as is the case if there are NO records matching)... so it kinda makes sense why this happens...