Splunk Search
Highlighted

Excluding a field name from fields command exclusions

Contributor

The fields - command expects a list of field names to exclude, and one can use wildcards in that list. But what I need to exclude a long list of fields that match a wildcard expression except for one? Here's an example:

| datamodel Authentication Successful_Authentication search | search sourcetype=audittrail | table * | fields - Authentication.user api* autojoin buckets date_* enable_lookups extra_fields file_modify_time max* savedsearch_name search* splunk_server_group timestamp ttl vendor_status + date_zone | fieldsummary

This excludes a bunch of fields named date_hour, date_mday, date_minute and so forth, but say I want to keep date_zone? One (clumsy) solution I've found is to copy the field before running the exclusions and then copy it back:

| datamodel Authentication Successful_Authentication search | search sourcetype=audittrail | table * | eval datezone=date_zone | fields - Authentication.user api* autojoin buckets date_* enable_lookups extra_fields file_modify_time max* savedsearch_name search* splunk_server_group timestamp ttl vendor_status | eval date_zone = datezone | fields - datezone | fieldsummary

Is there a better way?

0 Karma
Highlighted

Re: Excluding a field name from fields command exclusions

I think the way you did it is already very smart 🙂

Eventually you could use rename instead of eval!

Highlighted

Re: Excluding a field name from fields command exclusions

Contributor

Indeed, rename is one step better, allowing one to skip the second fields - call:

| from datamodel:Authentication.Successful_Authentication | search sourcetype=audittrail | table * | rename date_zone as datezone | fields - api* autojoin buckets date_* enable_lookups extra_fields file_modify_time max* savedsearch_name search* splunk_server_group timestamp ttl user vendor_status | rename datezone as date_zone | fieldsummary

However, I was hoping there was a way to use some sort of regex to replace the wildcard, that is to say, a way of specifying date_[^z].* instead of date_*.

Highlighted

Re: Excluding a field name from fields command exclusions

Ultra Champion

Maybe something in the spirit of @somesoni2 with a lookup at Search all fields except ____

0 Karma
Highlighted

Re: Excluding a field name from fields command exclusions

Contributor

I tried:

| datamodel Authentication Successful_Authentication search | search sourcetype=audittrail | table * [|inputlookup myfieldstolookup.csv | eval query="searchTerm=".fieldName | table query | format ] | fieldsummary

where I used the Lookup Editor app to create myfieldstolookup.csv but I'm not sure what the contents of that file should be. I tried a file that looks like:

fieldName,fieldNameOut
Authentication.action,Authentication.action
...
tag::eventtype,tag::eventtype

Far from filtering on the 17 fields I want, it added 21 fields ( addr, auid, dev...) to the unfiltered 49-field search. The fragment:

[|inputlookup myfieldstolookup.csv | eval query="searchTerm=".fieldName | table query | format ]

returns

( ("searchTerm=Authentication.action") OR ... )

instead of the expected

( (searchTerm=Authentication.action) OR ... )

What am I doing wrong?

0 Karma
Highlighted

Re: Excluding a field name from fields command exclusions

SplunkTrust
SplunkTrust

Your lookup should have only single column, say fieldName, with all the field names that you want to keep.

fieldName
 Authentication.action
 ...
 tag::eventtype,tag::eventtype

Now your search should be like this (see the rename and parameter to format command, that's the trick. Passing 6 empty double quote set separated by space)

 | datamodel Authentication Successful_Authentication search | search sourcetype=audittrail | table * [|inputlookup myfieldstolookup.csv | table fieldName | rename fieldName as query | format "" "" "" "" "" "" ] | fieldsummary
0 Karma
Highlighted

Re: Excluding a field name from fields command exclusions

Contributor

http://docs.splunk.com/Documentation/Splunk/7.0.0/Knowledge/Usefieldlookupstoaddinformationtoyoureve... says "The table in the CSV file should have at least two columns", which is what initially led me to put two columns in there.

0 Karma
Highlighted

Re: Excluding a field name from fields command exclusions

Contributor

It gets closer, but the fragment:

| inputlookup myfieldstolookup.csv | table fieldName | rename fieldName as query | format "" "" "" "" "" ""

now yields:

"Authentication.action" "Authentication.app" "Authentication.dest" "Authentication.src" "Authentication.src_user" "Authentication.tag" date_zone eventtype host index info linecount source sourcetype splunk_server "tag::action" "tag::eventtype"

which again fails to filter the fields. Looks like the quotes get added when "punctuation" (i.e. colons and periods) is present in the field names.

0 Karma
Highlighted

Re: Excluding a field name from fields command exclusions

SplunkTrust
SplunkTrust

Try this

 [
 | inputlookup myfieldstolookup.csv 
 | table fieldName 
 | format "" "" "" "" "" "" 
 | rex mode=sed field=search "s/\"//g"
 ]
0 Karma
Highlighted

Re: Excluding a field name from fields command exclusions

Contributor

That does get rid of the quotes in the fragment, but it turns out they're not the problem: the problem is very simply the '*' before the fragment, which voids the filtering being attempted. All that was needed was to change:

| datamodel Authentication Successful_Authentication search | search sourcetype=audittrail | table * [ | inputlookup myfieldstolookup.csv | table fieldName | rename fieldName as query | format "" "" "" "" "" "" ] | fieldsummary

into:

| datamodel Authentication Successful_Authentication search | search sourcetype=audittrail | table [ | inputlookup myfieldstolookup.csv | table fieldName | rename fieldName as query | format "" "" "" "" "" "" ] | fieldsummary
0 Karma