topic Re: How to only display fields with values in a table in Splunk Search

How to only display fields with values in a table

alecl — Fri, 12 Oct 2012 20:29:43 GMT

I'm interested in doing a search for a number of fields and displaying the output in a | table ... of only the fields with values.

Basically I have a list of error description fields that I want to print out, but only if they have something in them. I don't want to routinely display 20 extra fields that are usually empty for the odd chance when they are not.

My search will be

where ErrorField1=* OR ErrorField2=*

etc

Re: How to only display fields with values in a table

Takajian — Fri, 12 Oct 2012 23:48:14 GMT

Can you use "table" command to display specified field value?

... | table ErrorField1 ErrorField2

Re: How to only display fields with values in a table

alecl — Sat, 13 Oct 2012 12:20:37 GMT

That will always display both fields whether they have a value or not which is what I want to avoid otherwise the table will scroll 30 columns wide with all the fields I'm looking for with only sparse data actually filling it.

Re: How to only display fields with values in a table

Takajian — Tue, 16 Oct 2012 09:25:02 GMT

Do you want to filter the fields which value is NULL? Then, does following search meet your requirement?

.. | table ErrorField1 ErrorField2 | where ErrorField1 NOT null AND ErrorField2 NOT NULL

Re: How to only display fields with values in a table

alecl — Fri, 19 Oct 2012 17:24:27 GMT

That is fine for the search, but I'm concerned about the list of fields in the table display.

ex1) ErrorField1 is null, ErrorField2 has a value
table should show ErrorField2 only

ex2) ErrorField1 has a value, ErrorField2 is null
table should show ErrorField1 only

The table options should be able to figure out when not to show a field somehow.

Re: How to only display fields with values in a table

marcellodesales — Mon, 17 Mar 2014 17:56:56 GMT

table a b c | where a="Value"... This worked liked a charm!

Re: How to only display fields with values in a table

marcellodesales — Mon, 17 Mar 2014 17:59:51 GMT

Here's my example with a "where", which verifies the value of a given field.

index=quadf_qdc_prod source="/usr/share/whp-tomcat-7/logs/catalina.out" " > x-forwarded-for:" | rex "x-forwarded-for: (?<IP_ADDRESSES>(.*))" | table IP_ADDRESSES | eval CLIENT_IP=mvindex(split(IP_ADDRESSES, ","), 0) | stats count by CLIENT_IP | lookup geoip clientip AS CLIENT_IP | eval client_geo=client_lat+" , "+client_lon | stats count by CLIENT_IP, client_city, client_region, client_country, client_geo | where client_country="Brazil"

Re: How to only display fields with values in a table

rtadams89 — Mon, 17 Mar 2014 22:15:37 GMT

Assuming that you will only have one "ErrorField" popualted with data for each event, yo ucan use the eval coalesce() function to create a new field that is equal to the value found in one of the ErrorField* fields. For example:

... | where ErrorField1=* OR ErrorField2=* | eval outputField=coalesce(ErrorField1,ErrorField2) | table outputField

If if is possible for there to be values in more than one "ErrorField" for each event, you can use strcat to concatenate all the "ErrorField" values together into a new field. For example:

... | where ErrorField1=* OR ErrorField2=* | strcat ErrorField1 ErrorField2 outputField | table outputField

Re: How to only display fields with values in a table

skawasaki_splun — Tue, 10 Mar 2015 21:49:52 GMT

A bit hacky but it should work:

... | streamstats count as temp_id | stats values(*) by temp_count | rename values(*) as * | fields - temp_id

Why it works?

Because stats values(...) won't show fields that don't have at least one non-null value. I also use streamstats count as temp_id to generate a unique "ID" for each row so we don't do some accidental grouping in stats. And of course if you already have a unique id like ... | stats ... by id previously then you won't need temp_id.

Re: How to only display fields with values in a table

ndoshi — Tue, 24 May 2016 19:11:46 GMT

I think you have a typo: It should be:

| streamstats count as temp_id | stats values(*) by temp_id| rename values(*) as * | fields - temp_id

Re: How to only display fields with values in a table

nisha_kapoor — Sat, 08 Jul 2017 03:06:15 GMT

I tried this command and it still displays the fields which have a null value.
stats values (fieldname) by itself works, but when I give the command as stats values (*), the result is all the fields with all distinct values, fields with null values also get displayed which kind of beats my purpose, which is to select and display those fields which have at least one non null value. Can someone help me figure out where I might be going wrong?

Re: How to only display fields with values in a table

romanwaldecker — Tue, 20 Feb 2018 07:43:59 GMT

Didn't work in my case - The empty columns persisted (Splunk 7.0.2).
Instead, I found this solution Link here in the forum: https://answers.splunk.com/answering/593190/view.html

Re: How to only display fields with values in a table

nick405060 — Thu, 23 Aug 2018 21:51:14 GMT

This worked like a charm! Thanks! It took me a while to figure out that the command has to go AFTER you table it.

But, it's interesting, I have multi-value (mv) fields that have the data value repeated twice in it - which I've been having to hack out using:

fillnull value="" | mvexpand field1 | mvexpand field2 | dedup _raw

This command though, somehow, for some unknown reason, does the same thing, and dedupes any mv data in the display table - at least if the value is repeated/the same. It could be a problem for anyone who has multiple/mv values in their data.

Re: How to only display fields with values in a table

nick405060 — Thu, 23 Aug 2018 21:54:38 GMT

Okay I'm sure the "for some unknown reason" part actually makes sense to someone who knows more than me, and has to do with the way stats values(*) works

Re: How to only display fields with values in a table

landen99 — Thu, 07 Nov 2019 17:07:15 GMT

I downvoted this post because this answers the questions on what the fields with values are but does not display only the fields with values with their values.

Re: How to only display fields with values in a table

koshyk — Fri, 14 Aug 2020 08:56:07 GMT

Just to addon and remove some of the default fields from Splunk

(This way you can recover user custom fields)

..your base search.. | streamstats count as temp_id | stats values(*) by temp_id | rename values(*) as * | fields - temp_id, date_*, index, host, info_*,source,sourcetype,linecount,user_watchlist,time*pos,unix_*