topic Re: Without using fillnull, how to output a default value when any defined sourcetypes in my search do not return any events? in Splunk Search

Without using fillnull, how to output a default value when any defined sourcetypes in my search do not return any events?

splunker1981 — Tue, 23 Aug 2016 19:51:32 GMT

Hello Splunk experts,

Hoping someone can help get me in the right path. I am running a search where I would like to default values when certain sourcetypes do not return events. For the sake of an example I'll use tstats

| tstats latest(_time) WHERE index=* (sourcetype=source1 OR sourcetype=source2 OR sourcetype=source3) by index host sourcetype |table host, sourcetype, latest(_time)

Now, how can I force or create an N/A value when any of the defined sourcetypes in my search do not return any events? ie; let's say I have events for the source1 and source2, but no results come back for source3. Is there a way to default a value or better way to approach this?

host1   source1  1471968565
host2   source2  1471968565
N/A     source3  N/A

Thanks for the help

Re: Without using fillnull, how to output a default value when any defined sourcetypes in my search do not return any events?

sundareshr — Tue, 23 Aug 2016 19:55:02 GMT

Try coalesce

... | eval source=coalesce(source, "N/A")

*OR*

.... | foreach * [eval <<FIELD>>=coalesce(<<FIELD>>, "n/a")]

Re: Without using fillnull, how to output a default value when any defined sourcetypes in my search do not return any events?

splunker1981 — Tue, 23 Aug 2016 20:02:32 GMT

Couldn't get either to work. I thought coalesce takes the first non-null value? Would it work in this case since nothing in theory would be coming back?

Re: Without using fillnull, how to output a default value when any defined sourcetypes in my search do not return any events?

sundareshr — Tue, 23 Aug 2016 20:10:32 GMT

How about forcing a row of "n/a"?

| tstats latest(_time) as time WHERE index=* (sourcetype=source1 OR sourcetype=source2) by index host sourcetype | table host, sourcetype, time | foreach * [eval <<FIELD>>=coalesce(<<FIELD>>, "n/a")] | append [| makeresults | eval host="n/a" | eval sourcetype="n/a" | eval time="n/a" | fields - _time]

Re: Without using fillnull, how to output a default value when any defined sourcetypes in my search do not return any events?

sheamus69 — Wed, 24 Aug 2016 12:34:10 GMT

Why can't fillnull be an option?

Re: Without using fillnull, how to output a default value when any defined sourcetypes in my search do not return any events?

splunker1981 — Mon, 29 Aug 2016 17:41:04 GMT

b/c it doesn't do anything in this case. Unless I am using it incorrectly it just doesn't force a value

Re: Without using fillnull, how to output a default value when any defined sourcetypes in my search do not return any events?

splunker1981 — Mon, 29 Aug 2016 17:46:15 GMT

sundareshr, thanks for the follow up reply.

Closer, but still not what I am looking for. The problem is that it doesn't tell me which of my defined sourcetypes in my WHERE statement are missing, I just get an n/a. So in this case i get something like below.

host         sourcetype        time
host1       source1              11:00:00
n/a          n/a               n/a

But I would like something like

host         sourcetype        time
host1       source1              11:00:00
n/a           source2            n/a

Re: Without using fillnull, how to output a default value when any defined sourcetypes in my search do not return any events?

dwaddle — Tue, 30 Aug 2016 05:40:09 GMT

When you're looking for a result event when there is truly nothing then there really isn't a built-in search command that will make something from nothing. An approach that @starcher and I have talked about in past .conf talks was to use the concept of a 'sentinel row' coming from a lookup table, like so:

| tstats max(_time) as maxtime WHERE 
      index=* (sourcetype=source1 OR sourcetype=source2 OR sourcetype=source3) 
      by index host sourcetype 
| inputlookup append=true source_sentinels.csv
| stats max(maxtime) as maxtime by index host sourcetype
| outputlookup source_sentinels.csv
| table host, sourcetype, maxtime

The main point here is that your lookup file contains placeholders for the results that might not be in the results returned by the search. What we do here is use an inputlookup followed by a 2nd call to stats to take either the value from tstats OR the value from the lookup. For good measure, we re-output-lookup the lookup file so that any new sourcetypes you add get more-or-less handled.

You can see the slides and audio/video where we discuss this at http://conf.splunk.com/session/2015/recordings/2015-splunk-38.mp4 or http://conf.splunk.com/session/2015/conf2015-LookupTalk.pdf

Re: Without using fillnull, how to output a default value when any defined sourcetypes in my search do not return any events?

dwaddle — Tue, 30 Aug 2016 18:44:41 GMT

Fillnull is great when you have results that are missing values. But, when the whole "result row" is missing because the search did not return anything for it, fillnull cannot make new result rows out of nothingness..