topic Re: How to make list(x) include all values? in Splunk Search

How to make list(x) include all values?

jpayne1 — Tue, 14 Nov 2017 01:43:38 GMT

list(x) does not return all values. If I have white space as my value, list omits it. Here is a simplified example of my use case:

Desired output:

     Address    Flag       Names
     ----------------------------
     IP1                 Jack Jill
     IP2                 Todd Tammy
     IP3        Bill     Bill Bob

Partial code snippet:

...
| rex field=Names ".*(?<Flag>Bill).*" 
| fillnull value=" " Flag
| stats list(Flag) list(names) by Address

But I'm getting:

     Address    Flag       Names
     ----------------------------
     IP1        Bill      Jack Jill
     IP2                  Todd Tammy
     IP3                  Bill Bob

I tried using a fillnull value of 0, running the list command, then replacing the 0 with " ", but this still doesn't preserve the order. The value Bill shoots back up to the first line as soon as I replace the zeros.

I'm currently using a table instead, but this adds a lot more rows and is not as easy to read, especially if an IP has many records. I could change fillvalue to a non-whitespace character, but that would look a bit sloppy to the end user, although it is better than resorting to table.

Has anyone else dealt with this? Should I be going at this a completely different way than list?

Re: How to make list(x) include all values?

niketn — Tue, 14 Nov 2017 02:08:11 GMT

@jpayne1, try the following searchmatch() command instead of rex:

 <YourBaseSearch>
|  eval Flag=if(searchmatch("\<Flag\>Bill"),"Bill","NA")
|  stats list(Flag) as Flag list(names) as Names by Address

Can your data have multiple values of Flag and Names for same IP Addresses? Can you please share the data for IP1, IP2 and IP3? Specially the <Flag> portion of the code as to what it looks like when the value is not Bill? You can mock anonymize data if required.

Re: How to make list(x) include all values?

kamlesh_vaghela — Tue, 14 Nov 2017 05:21:20 GMT

Hi @jpayne1

Can you please try below search?

...
| rex field=Names ".*(?<Flag>Bill).*" 
| fillnull value=" " Flag
| eval tempField= mvzip(mvzip(Flag,names),Address) 
| stats count by tempField
| eval Flag = mvindex(split(tempField,","),0), names= mvindex(split(tempField,","),1), Address=mvindex(split(tempField,","),2) 
| table Address Flag Names

Thanks