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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...