Splunk Search

How to only display fields with values in a table

alecl
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

skawasaki_splun
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

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

landen99
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

romanwaldecker
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

ndoshi
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

koshyk
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

nick405060
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

nick405060
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

nisha_kapoor
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

rtadams89
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

marcellodesales
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"

Takajian
Builder

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

... | table ErrorField1 ErrorField2

marcellodesales
Path Finder

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

alecl
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

Takajian
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

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