How to combine unique values of the field into one?

[srizan]

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.

[niketn · Accepted solution]

@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",",")

[srizan]

That worked like a charm, Thank you @niketnilay

[marycordova]

@srizan

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

[srizan]

@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