Splunk Search

Showing all fields in search (including empty)

djfang
Explorer

Hi,

I would like to know how to show all fields in the search even when results are all empty for some of the fields.

I've tried

| fillnull value="NA"

but that only works when there's at least a value in the empty field.

So, I would like splunk to show the following:

header 1 | header2 | header 3
value 1 | < empty > | value 3
value 1 | < empty > | value 3
value 1 | < empty > | value 3
value 1 | < empty > | value 3
value 1 | < empty > | value 3

I would appreciate for any suggestions on this.

Also, I understand that one can also possible do something like

| eval header2=""

but I have over 200 fields and about a handful of them are not filled out depending on situations and would hope to see if there's a better way to do this than listing all the header fields manually in the beginning.

Cheers!

0 Karma
1 Solution

woodcock
Esteemed Legend

I have 2 solutions:

You create a macro and use it like this:

... | `myfillnull()` | ...

Inside this macro you would iterate out every field like this:

eval header1=coalesce(header1,"N/A") | eval header2=coalesce(header2,"N/A") | ... | eval headerN=coalesce(headerN,"N/A")

Alternatively, you could create a lookup with each field laid out like this:

useThisToStripOut,header1,header2,...,headerN
useThisToStripOut,N/A,N/A,...,N/A

Then you could force each field to exist at least once by with append, then use fillnull, then remove the appended event like this:

... | append [|lookupcsv myfillnull] | fillnull value="N/A" | where isnotnull(usetThisToStripOut)

The latter is probably more efficient.

View solution in original post

woodcock
Esteemed Legend

I have 2 solutions:

You create a macro and use it like this:

... | `myfillnull()` | ...

Inside this macro you would iterate out every field like this:

eval header1=coalesce(header1,"N/A") | eval header2=coalesce(header2,"N/A") | ... | eval headerN=coalesce(headerN,"N/A")

Alternatively, you could create a lookup with each field laid out like this:

useThisToStripOut,header1,header2,...,headerN
useThisToStripOut,N/A,N/A,...,N/A

Then you could force each field to exist at least once by with append, then use fillnull, then remove the appended event like this:

... | append [|lookupcsv myfillnull] | fillnull value="N/A" | where isnotnull(usetThisToStripOut)

The latter is probably more efficient.

djfang
Explorer

This is a follow up to the question that was previously asked but no conclusion was achieved.

https://answers.splunk.com/answers/67740/how-to-detect-and-fill-default-value-to-empty-value-field.h...

0 Karma

woodcock
Esteemed Legend

That question not only has answers, one of them was "conclusively" Accepted as "the answer".

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to May Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars this month. This ...

They're back! Join the SplunkTrust and MVP at .conf24

With our highly anticipated annual conference, .conf, comes the fez-wearers you can trust! The SplunkTrust, as ...

Enterprise Security Content Update (ESCU) | New Releases

Last month, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...