Splunk Search

How do I remove dynamically named columns from a table when their values contain a specific string?

Cuyose
Builder

I have an API input that returns a JSON object containing a nested element with multiple dynamically named columns, where date is the dynamic portion. ex. request.20181201.instructions, request.20181101.instructions, request.20181011.instructions, etc

90 % of these fields will have a literal value of "null" or actually be null. I want to only return the columns other than these.

Basically the psuedocode would be

| table request.* |where .value=*  AND .value!="null" 

Is there any way to do this?

Tags (4)
0 Karma
1 Solution

Cuyose
Builder

After a lot of searching and taking bits and pieces from a lot of answers that did not address my issue, I was able to get what I wanted with the following. the rename was required due to the special characters in the field names.

The first foreach actually sets the "null" values to NULL, the second sets the "" values to NULL. this allows the transpose option for include_empty = False to work.

| rename *.*.* AS *_*_* , *.*.*{}.* as *_*_*_* 
| foreach request* 
    [ eval <<FIELD>>=if(<<FIELD>>="null", NULL, <<FIELD>>) ] 
| foreach codeDropUploadMap* 
    [ eval <<FIELD>>=if(<<FIELD>>="", NULL, <<FIELD>>) ] 
| transpose include_empty=false

View solution in original post

0 Karma

Cuyose
Builder

After a lot of searching and taking bits and pieces from a lot of answers that did not address my issue, I was able to get what I wanted with the following. the rename was required due to the special characters in the field names.

The first foreach actually sets the "null" values to NULL, the second sets the "" values to NULL. this allows the transpose option for include_empty = False to work.

| rename *.*.* AS *_*_* , *.*.*{}.* as *_*_*_* 
| foreach request* 
    [ eval <<FIELD>>=if(<<FIELD>>="null", NULL, <<FIELD>>) ] 
| foreach codeDropUploadMap* 
    [ eval <<FIELD>>=if(<<FIELD>>="", NULL, <<FIELD>>) ] 
| transpose include_empty=false
0 Karma

DalJeanis
Legend

The standard way would be ...

| streamstats count as recno
| untable recno fieldname fieldvalue
| where NOT like(fieldname,"something")
| xyseries recno fieldname fieldvalue
| fields - recno
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...