Splunk Search

Dedup multiple fields into one list

skirven
Communicator

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
1 Solution

vnravikumar
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

wmyersas
Builder

Use transpose:

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

skirven
Communicator

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

manjunathmeti
SplunkTrust
SplunkTrust

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

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

skirven
Communicator

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

skirven
Communicator

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

vnravikumar
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

skirven
Communicator

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

493669
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
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...