topic Re: How do I remove dynamically named columns from a table when their values contain a specific string? in Splunk Search

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

Cuyose — Wed, 10 Oct 2018 19:32:05 GMT

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?

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

DalJeanis — Wed, 10 Oct 2018 19:59:25 GMT

The standard way would be ...

| streamstats count as recno
| untable recno fieldname fieldvalue
| where NOT like(fieldname,"something")
| xyseries recno fieldname fieldvalue
| fields - recno

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

Cuyose — Wed, 10 Oct 2018 20:37:28 GMT

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