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 😛

[julienbonastre]

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 🚀🥰

[PickleRick]

OK. I added an idea https://ideas.splunk.com/ideas/EID-I-2471

Feel free to upvote and/or comment

[bowesmana]

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
]

[julienbonastre]

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:

| appendpipe [| makeresults | where hits=0]

| appendpipe [|makeresults | stats count(_raw) as count | where count=0 ]

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

[bowesmana]

Yes, you're right about timechart, but I wonder what the purpose of rendering a timechart where there are no datapoints at any time span.

I've certainly used an html panel instead of a null timechart. I can't see why you'd want to do that to display something empty

[bowesmana]

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$&gt;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>

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

[julienbonastre]

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?

[PickleRick]

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.