topic Re: Excluding a field name from fields command exclusions in Splunk Search

Excluding a field name from fields command exclusions

DUThibault — Fri, 01 Dec 2017 15:46:08 GMT

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?

Re: Excluding a field name from fields command exclusions

damien_chillet — Fri, 01 Dec 2017 16:24:08 GMT

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

Eventually you could use rename instead of eval!

Re: Excluding a field name from fields command exclusions

DUThibault — Fri, 01 Dec 2017 18:54:14 GMT

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

Re: Excluding a field name from fields command exclusions

ddrillic — Fri, 01 Dec 2017 19:45:23 GMT

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

Re: Excluding a field name from fields command exclusions

somesoni2 — Fri, 01 Dec 2017 20:24:15 GMT

The name and number of default date_* fields are constant, so one option could be to create a macro, say date_fields_to_filter with definition as date_hour date_mday date_minute date_month date_second date_wday date_year. Your query will be like this

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

Re: Excluding a field name from fields command exclusions

DUThibault — Fri, 01 Dec 2017 21:02:45 GMT

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?

Re: Excluding a field name from fields command exclusions

DUThibault — Fri, 01 Dec 2017 21:08:55 GMT

The macro solution is a good one, simpler than inputlookup. By the same token, it may be easier to do:

| fields + `date_fields_to_keep`

(Although I do realize that the two solutions are not equivalent when the events don't have a uniform field signature)

Re: Excluding a field name from fields command exclusions

somesoni2 — Fri, 01 Dec 2017 21:18:47 GMT

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

Re: Excluding a field name from fields command exclusions

DUThibault — Fri, 01 Dec 2017 21:31:10 GMT

http://docs.splunk.com/Documentation/Splunk/7.0.0/Knowledge/Usefieldlookupstoaddinformationtoyourevents 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.

Re: Excluding a field name from fields command exclusions

DUThibault — Fri, 01 Dec 2017 21:36:29 GMT

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.

Re: Excluding a field name from fields command exclusions

DalJeanis — Fri, 01 Dec 2017 21:54:54 GMT

Try this

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

Re: Excluding a field name from fields command exclusions

woodcock — Sat, 02 Dec 2017 20:55:06 GMT

Like this:

... | rename foo1234 AS _foo1234
| fields - foo*
| rename _foo1234 AS foo1234

Fields that start with _ are not included in wildcard matches except for _*.

Re: Excluding a field name from fields command exclusions

DUThibault — Mon, 04 Dec 2017 14:25:34 GMT

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

Re: Excluding a field name from fields command exclusions

DUThibault — Mon, 04 Dec 2017 14:28:00 GMT

First mentioned by damien_chillet, this is by far the simplest approach. It would also be amenable to being made into a macro if I were so inclined.

Re: Excluding a field name from fields command exclusions

woodcock — Mon, 04 Dec 2017 14:48:52 GMT

I didn't notice that (I don't generally read the other comments, just the answers, when I am posting my answer), but there is big value in the _* portion that he did not mention, too. I upvoted his comment and you should do the same.