topic Re: How to search if a string exists in a variable number of columns? in Splunk Search

How to search if a string exists in a variable number of columns?

Kukkadapu — Tue, 19 Apr 2016 17:41:47 GMT

Hi,

I have multiple columns (number of columns may vary) and wanted to search a string if it exists in any of the columns. How do I do this using a simple search?

Log example: There are three statements

Id_1="abc" Id_2="xyz"  Id_3="123"  Id_4="abcd"  Id_5="abc"  Id_6="abc"
Id_1="abc" Id_2="xyz"  Id_3="123"  
Id_1="abc" Id_2="123"  Id_3="123"  Id_4="abcd"  Id_5="abc"  Id_6="abc" Id_7="123"

I've used the table command like this: | table Id_* to get a table of Id's
OUTPUT for the above search:

Id_1="abc" Id_2="xyz"  Id_3="123"  Id_4="abcd"  Id_5="abc"  Id_6="abc" Id_7="xyz"
Id_1="abc" Id_2="xyz"  Id_3="123"  Id_4=null         Id_5=null    Id_6=null    Id_7=null
Id_1="abc" Id_2="123"  Id_3="123"  Id_4="abcd"  Id_5="abc"  Id_6="abc" Id_7="123"

Now I wanted to search/get the records where Id_* is "xyz" . Can you help me with the search?

Thanks.

Re: How to search if a string exists in a variable number of columns?

sundareshr — Tue, 19 Apr 2016 18:09:26 GMT

You could add "xyz" to you search. Like this index=* "xyz" | table id_*

Re: How to search if a string exists in a variable number of columns?

Kukkadapu — Tue, 19 Apr 2016 18:36:48 GMT

Hi Sundareshr, Yeah , but the search string is also going to be in the other columns. I wanted to search only in the column Id_*

Thanks

Re: How to search if a string exists in a variable number of columns?

sundareshr — Tue, 19 Apr 2016 18:51:44 GMT

Try this

.. | table id_* | foreach ID_* [eval x=<<MATCHSTR>>."@@@"] | search x="xyz"

Re: How to search if a string exists in a variable number of columns?

Kukkadapu — Tue, 29 Sep 2020 09:30:52 GMT

Can you explain the query please? I'm seeing this error

Failed to parse templatized search for field 'Id_1'
Failed to parse templatized search for field 'Id_2'
Failed to parse templatized search for field 'Id_3'
Failed to parse templatized search for field 'Id_4'

Thanks

Re: How to search if a string exists in a variable number of columns?

javiergn — Tue, 19 Apr 2016 19:03:53 GMT

If you just want the field names you could use something like this instead (not tested so play around with the quotes):

| eval fieldnames = ""
| foreach Id_* [eval fieldnames = if(match(<<FIELD>>, "xyz"), "<<FIELD>>" . fieldnames, fieldnames)]

Re: How to search if a string exists in a variable number of columns?

Kukkadapu — Tue, 19 Apr 2016 19:59:01 GMT

Perfect , This worked. It created a new column - "fieldnames" with the original column name. I could just search the new column which has no nulls.

Thanks for your time Javiergn.