topic Re: Dedup multiple fields into one list in Splunk Search

Dedup multiple fields into one list

skirven — Thu, 12 Mar 2020 11:16:44 GMT

Hi! I'm trying to create a search that would return unique values in a record, but in one list.

The search "basesearch | table scn*" would come up with a table where I have values across scn01 to scn20. So what I want to do is make a unique list of values combined into one column, of all of the fields values. I don't need to preserve the previous field name.

How might I do that?
Thanks!
Stephen

Re: Dedup multiple fields into one list

493669 — Thu, 12 Mar 2020 11:31:18 GMT

@skirven, Use foreach loop.
For example below-

|makeresults|eval scn01="abc", scn02="bcd", scn10="ddd", scn14="ert",total="" | foreach scn* [eval total= total." <<FIELD>>"]|makemv total delim=" "|mvexpand total

Re: Dedup multiple fields into one list

vnravikumar — Thu, 12 Mar 2020 11:35:00 GMT

Check this

| makeresults 
| eval scn01=23,scn02=34,scn03=55 
| append 
    [| makeresults 
    | eval scn01=34,scn02=34,scn03=55] 
| eval temp="" 
| foreach scn* 
    [ eval temp=temp ." ". <<FIELD>>] 
| makemv temp 
| stats values(temp) as result delim="," 
| nomv result

Re: Dedup multiple fields into one list

manjunathmeti — Thu, 12 Mar 2020 11:40:50 GMT

hi @skirven,
Try this:

basesearch | eval scn_combine = "" | fillnull value="" | foreach scn* [eval scn_combine=scn_combine." ".'<<FIELD>>'] | table scn_combine

If you want scn_combine as multivalue field then:

basesearch | eval scn_combine = "" | fillnull value="" | foreach scn* [eval scn_combine=scn_combine." ".'<<FIELD>>'] | makemv  scn_combine | table scn_combine

Re: Dedup multiple fields into one list

skirven — Thu, 12 Mar 2020 11:54:58 GMT

Hmmm... This gets me closer, but nothing's quite right yet.

| eval scn_combine = "" | fillnull value="" | foreach scn* [append scn_combine scn*] | table scn_combine

I see the point about looping through scn(x). But I'm thinking I want to append the values to a temp table, then running another dedup on that temp table? This search tossed an error.
Thanks!
Stephen

Re: Dedup multiple fields into one list

wmyersas — Thu, 12 Mar 2020 13:48:40 GMT

Use transpose:

search>
| table scn*
| stats values(row1) as SCNs

Re: Dedup multiple fields into one list

skirven — Fri, 13 Mar 2020 07:40:10 GMT

This gets me closer, but it's dropping all of the values into one row. I wanted to get them all in different rows, so that I could run the dedup again to get unique values.
-Stephen

Re: Dedup multiple fields into one list

skirven — Fri, 13 Mar 2020 07:50:45 GMT

This might work for what I need anyway. I was hoping to get them all in one table list, but my ultimate goal was to get it to send to a API via the JSON endpoint, so one string in that may be OK. But I couldn't get makeenv to split the values back into a list by rows.
Thanks!
Stephen

Re: Dedup multiple fields into one list

skirven — Fri, 13 Mar 2020 08:06:12 GMT

AHA! mvexpand!

|  table scn*
 | eval temp="" 
 | foreach scn* 
     [ eval temp=temp ." ". <<FIELD>>] 
 | makemv temp 
 | stats values(temp) as result delim="," 
 | makemv delim="," result
 | mvexpand result

Thanks everyone!

Re: Dedup multiple fields into one list

vnravikumar — Fri, 13 Mar 2020 08:07:13 GMT

Try this

| makeresults 
| eval scn01=23,scn02=34,scn03=55 
| append 
    [| makeresults 
    | eval scn01=34,scn02=34,scn03=55] 
| eval temp="" 
| foreach scn* 
    [ eval temp=temp ." ". <<FIELD>>] 
| makemv temp 
| stats values(temp) as result 
| mvexpand result

values() - Returns the list of all distinct values