Splunk Search

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

splunker1981
Path Finder

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

1 Solution

dwaddle
SplunkTrust
SplunkTrust

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

View solution in original post

dwaddle
SplunkTrust
SplunkTrust

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

sheamus69
Communicator

Why can't fillnull be an option?

0 Karma

dwaddle
SplunkTrust
SplunkTrust

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

0 Karma

splunker1981
Path Finder

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

0 Karma

sundareshr
Legend

Try coalesce

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

*OR*

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

splunker1981
Path Finder

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?

0 Karma

sundareshr
Legend

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]
0 Karma

splunker1981
Path Finder

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
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...