Splunk Search

How to only display fields with values in a table

Explorer

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

Tags (3)
1 Solution

Splunk Employee
Splunk Employee

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.

View solution in original post

Splunk Employee
Splunk Employee

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.

View solution in original post

Motivator

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.

0 Karma

Path Finder

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

0 Karma

Splunk Employee
Splunk Employee

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

Super Champion

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_*

 

0 Karma

Motivator

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.

0 Karma

Motivator

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

0 Karma

Path Finder

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?

0 Karma

Contributor

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

Path Finder

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"

Builder

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

... | table ErrorField1 ErrorField2

Path Finder

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

Explorer

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.

0 Karma

Builder

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

0 Karma

Explorer

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!