Splunk Search

How to combine unique values of the field into one?

srizan
Path Finder

I am trying to make a report with the unique combination of ID, AVER SRV & ZONE. However, since I am getting lots of duplicate values because I have multiple values for ZONE, is there anyway I can combine all the ZONE in one field so I won't have lots of duplication.

Currently I am using following query:

| dedup ID AVER SRV ZONE | fields + ID, SRV, ZONE

Now if the Zone has multiple values, I am getting multiple entries instead I am trying to have one entry with all the different zones combined.

Please advise.

0 Karma
1 Solution

niketn
Legend

@srizan, try the following:

<yourCurrentSearch>
| stats values(ZONE) as ZONE by ID AVER SRV
| nomv ZONE
| eval ZONE=replace(ZONE,"\s",",")

Following is a run anywhere example based on sample data provided. The query from | makeresults till | table generates dummy data as per the first table provided in comment.

| makeresults 
| eval data="123 1 2 01;123 1 2 02;123 1 2 03" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim=" " 
| eval ID=mvindex(data,0),AVER=mvindex(data,1),SRV=mvindex(data,2),ZONE=mvindex(data,3)
| table ID AVER SRV ZONE
| stats values(ZONE) as ZONE by ID AVER SRV
| nomv ZONE
| eval ZONE=replace(ZONE,"\s",",")
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@srizan, try the following:

<yourCurrentSearch>
| stats values(ZONE) as ZONE by ID AVER SRV
| nomv ZONE
| eval ZONE=replace(ZONE,"\s",",")

Following is a run anywhere example based on sample data provided. The query from | makeresults till | table generates dummy data as per the first table provided in comment.

| makeresults 
| eval data="123 1 2 01;123 1 2 02;123 1 2 03" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim=" " 
| eval ID=mvindex(data,0),AVER=mvindex(data,1),SRV=mvindex(data,2),ZONE=mvindex(data,3)
| table ID AVER SRV ZONE
| stats values(ZONE) as ZONE by ID AVER SRV
| nomv ZONE
| eval ZONE=replace(ZONE,"\s",",")
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

srizan
Path Finder

That worked like a charm, Thank you @niketnilay

0 Karma

marycordova
SplunkTrust
SplunkTrust

@srizan

... | stats values(*) as * by ID or whatever it is you want to group lines by

@marycordova
0 Karma

srizan
Path Finder

@marycordovacaa I apoligize for not being clear,
I have various values for ZONE and dedup for
ID | AVER | SRV | ZONE
123 1 2 01
123 1 2 02
123 1 2 03

I want it to have it something like this
ID | AVER | SRV | ZONE
123 1 2 01,02,03

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...