Dedup multiple fields into one list

[skirven]

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

[wmyersas]

Use transpose:

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

[skirven]

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

[manjunathmeti]

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

[vnravikumar · Accepted solution]

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

[skirven]

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

[skirven]

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

[vnravikumar]

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

[skirven]

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!

[493669]

@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