Splunk Search

How to fill in null or non existent fields in a custom dashboard?

jcspigler2010
Path Finder

Bare with me on this one...

Splunkers!!!!

Have a custom dashboard panel question.

I am building a dashboard for a customer who just wants to show their cisco switch and routers in a single table. All of it being the sourcetype of cisco:ios.

Building of the table is relatively straight forward along with creating the drop -own menus that are dynamically generated from Splunk searches. Here's the issue I am running into….

If I filter on a host that doesn’t have a particular field I'm referencing in the table I am populating, no results return at all. The way I have the search constructed is as follows…

index = network sourcetype=cisco:ios mnemonic="$mnemonic$" host="$host$" protocol = "$protocol$" reason="$reason$" message_text="$message_text$" severity="$severity$" src_interface="$src_interface$"  | table _time,mnemonic,host,hostname,protocol,reason,message_text,severity,src_interface

My default values for anything referencing a variable is “*”. So back to my issue, if the field doesn’t exist, Splunk doesn’t return results since it is an implicit AND. So for example, if I filter on the host “foo”, my search creating this table would ultimately look like this….

index = network sourcetype=cisco:ios mnemonic="*" host=“foo" protocol = "*" reason="*" message_text="*" severity="*" src_interface="*"  | table _time,mnemonic,host,hostname,protocol,reason,message_text,severity,src_interface

which yield no results since host foo, doesn’t have a couple of the specified fields.

I did some research and found the following post about a similar issue, but it only addresses when you are doing a static table and have missing fields. https://answers.splunk.com/answers/60048/how-to-include-completely-missing-fields-in-results.html

You essentially do an eval, and if null, fill in the field with a static text.

I wanted to pick everyone’s brains and see how they would approach this and if my way is really that efficient.

My thinking is this…

filter on the host first because we know we are always going to have a host value.

Then run an eval on each field we need in our table. If the value is null, then fill in with “missing” or whatever. Then, pipe that into a sub search where you apply your variables and since the missing fields now have a value in them, a =* value will work. Finally pipe that all into a table.

So it would look sorta kinda like this…

index = network sourcetype=cisco:ios host=“$host$” | eval mnemonic=if(is null(mnemonic),”missing”,mnemonic) … repeat for each field, then pipe into … | search mnemonic="$mnemonic$” … do this for each field finally pipe into a table … |   table _time,mnemonic,host,hostname,protocol,reason,message_text,severity,src_interface

Hopefully my banter makes sense 🙂

Any help is appreciated!!

1 Solution

woodcock
Esteemed Legend

Like this:

index = network sourcetype=cisco:ios host="$host$" | fillnull value="missing" mnemonic protocol reason message_text severity src_interface | search mnemonic="$mnemonic$" protocol="$protocol$" reason="$reason$" message_text="$message_text$" severity="$severity$" src_interface="$src_interface$"  | table _time mnemonic host hostname protocol reason message_text severity src_interface

But if you have more than 1 indexer this approach will drastically slow your performance, so if you are not using multiselect, I would instead use this trick to create a base search that will map/reduce: modify the inputs in your fieldset with populating searches like this:

 <populatingSearch fieldForLabel="name" fieldForValue="value">index=network sourcetype=cisco:ios | stats values(mnemonic) AS name | mvexpand name | eval value = "mnemonic=\"" . name . "\"" | append [|makeresults | stats count AS name | eval name="*" | eval value="_raw=*"] | streamstats count AS serial | eval serial=if(name="*", 0, serial) | sort 0 serial | fields name value</populatingSearch>

Then use a search like this for your panel:

index=network sourcetype=cisco:ios host="$host$" $mnemonic$ $protocol$ $reason$ $message_text$ $severity$ $src_interface$ | table _time mnemonic host hostname protocol reason message_text severity src_interface

View solution in original post

jcspigler2010
Path Finder

Thanks for the input guys

This was what I ultimately did. It checks if each field has a value in it before filtering. If the field doesn't exist for that event, fill in with N/A

index = network sourcetype=cisco:ios host = "$host$" | eval protocol=if(isnull(protocol),"N/A",protocol),mnemonic=if(isnull(mnemonic),"N/A",mnemonic),reason=if(isnull(reason),"N/A",reason),message_text=if(isnull(message_text),"N/A",message_text),severity=if(isnull(severity),"N/A",severity),src_interface=if(isnull(src_interface),"N/A",src_interface) | search mnemonic="$mnemonic$" protocol = "$protocol$" reason = "$reason$" message_text = "$message_text$" severity = "$severity$" src_interface="$src_interface$" | lookup dnslookup host as host OUTPUT hostname as hostname | table _time,mnemonic,host,hostname,protocol,reason,message_text,severity,src_interface

Fillnull seems like a WAY easier way of doing it.

0 Karma

woodcock
Esteemed Legend

You should click Accept on the best answer to close the question.

0 Karma

woodcock
Esteemed Legend

Like this:

index = network sourcetype=cisco:ios host="$host$" | fillnull value="missing" mnemonic protocol reason message_text severity src_interface | search mnemonic="$mnemonic$" protocol="$protocol$" reason="$reason$" message_text="$message_text$" severity="$severity$" src_interface="$src_interface$"  | table _time mnemonic host hostname protocol reason message_text severity src_interface

But if you have more than 1 indexer this approach will drastically slow your performance, so if you are not using multiselect, I would instead use this trick to create a base search that will map/reduce: modify the inputs in your fieldset with populating searches like this:

 <populatingSearch fieldForLabel="name" fieldForValue="value">index=network sourcetype=cisco:ios | stats values(mnemonic) AS name | mvexpand name | eval value = "mnemonic=\"" . name . "\"" | append [|makeresults | stats count AS name | eval name="*" | eval value="_raw=*"] | streamstats count AS serial | eval serial=if(name="*", 0, serial) | sort 0 serial | fields name value</populatingSearch>

Then use a search like this for your panel:

index=network sourcetype=cisco:ios host="$host$" $mnemonic$ $protocol$ $reason$ $message_text$ $severity$ $src_interface$ | table _time mnemonic host hostname protocol reason message_text severity src_interface

DalJeanis
SplunkTrust
SplunkTrust

Is there a technical reason to populate your mnemonic value selection with "_raw=(asterisk)" rather than simply "" (no search criteria)?

It seems odd to have multiple _raw=(asterisk) when you can just drop the clause out of the search altogether.

That change would look something like this -

<populatingSearch fieldForLabel="name" fieldForValue="value">
index=network sourcetype=cisco:ios 
| stats values(mnemonic) AS name 
| mvexpand name 
| eval value = "mnemonic=\"" . name . "\"" 
| streamstats count AS serial 
| append 
    [|makeresults 
     | stats count AS name 
     | eval name="*" 
     | eval value=""
     | eval serial=0
    ] 
| sort 0 serial 
| fields name value
</populatingSearch>

woodcock
Esteemed Legend

I was not sure if a null string would work or not and was unwilling to invest the time and effort to test it. I suspect that it will work (if anybody tests, please add a comment to let us know). All of the _raw=* strings will get optimized out and not impact the search at all.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

And, just in case you wanted to allow the dropdown to give the option of looking for NULL values in a particular field...

<populatingSearch fieldForLabel="name" fieldForValue="value">
$TheOverallSearch$  
| stats values($TheFieldName$) AS name 
| mvexpand name 
| eval value = "$TheFieldName$=\"" . name . "\"" 
| streamstats count AS serial 
| append 
    [|makeresults 
     | stats count AS name 
     | eval name="((ALL))" 
     | eval value=""
     | eval serial=-2
    ] 
| append 
    [|makeresults 
     | stats count AS name 
     | eval name="((NULL))" 
     | eval value="(NOT $TheFieldName$=*)"
     | eval nullspresent = 
            [| search  $TheOverallSearch$ (NOT $TheFieldName$=*) 
             | head 1 | stats count | rename count as search]
     | eval serial=-1
     | search nullspresent=1
    ] 
| sort 0 serial 
| fields name value
</populatingSearch>

Notes -

1) Replace $TheOverallSearch$ in two places with your overall search terms and indexes, and $TheFieldName$ with the field name in question.

2) Modify as desired for your UI preferences regarding ((ALL)) vs *, ((NULL)) vs NULL vs ((MISSING)).

3) If you'd prefer to have the NULL as the last value option line rather than the second one, move the append for that line just above the streamstats command. You can delete the eval serial= inside that append code, or levae it, the result will be the same.

4) This only gives the option to search for null values if there potentially are null values in that field.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...