Splunk Search
Highlighted

Dedup multiple fields into one list

Path Finder

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

0 Karma
Highlighted

Re: Dedup multiple fields into one list

Super Champion

@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
0 Karma
Highlighted

Re: Dedup multiple fields into one list

Champion

Hi

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

View solution in original post

0 Karma
Highlighted

Re: Dedup multiple fields into one list

Path Finder

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

0 Karma
Highlighted

Re: Dedup multiple fields into one list

Path Finder

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

0 Karma
Highlighted

Re: Dedup multiple fields into one list

Path Finder

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!

0 Karma
Highlighted

Re: Dedup multiple fields into one list

Champion

Hi

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

0 Karma
Highlighted

Re: Dedup multiple fields into one list

Influencer

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
0 Karma
Highlighted

Re: Dedup multiple fields into one list

Path Finder

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

0 Karma
Highlighted

Re: Dedup multiple fields into one list

Builder

Use transpose:

search>
| table scn*
| stats values(row1) as SCNs
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.