topic How do I combine unique values of a field into one for multiple fields? in Splunk Search

How do I combine unique values of a field into one for multiple fields?

srizan — Thu, 29 Nov 2018 23:17:26 GMT

I am trying to make a report with the unique combination of ID, AVER SRV, ZONE, IPADDR & host. Unfortunately, I am getting lots of duplicate values because I have multiple values for ZONE, IPADDR & host. Currently, I am using 3 different queries in Splunk and joining the table with SRV later. However, is there anyway I can combine multiple values of those fields in one field each so I won't have lots of duplication.

Currently I am using the following query:
I have a query that re

source=*prod*
| dedup SRV AVER ZONE  
| fields + SRV , IVER, AVER, ZONE 
| stats values(ZONE) as ZONE by SRV AVER  
| nomv ZONE 
| eval ZONE=replace(ZONE,"\s",",")

source=*prod*
| dedup SRV IPADDR
| fields +  SRV IPADDR
| stats values(IPADDR) as IPADDR by SRV 
| nomv IPADDR 
| eval IPADDR =replace(IPADDR ,"\s",",")

source=*prod*
| dedup SRV host
| fields + SRV , host
| stats values(host) as host by SRV 
| nomv host 
| eval host=replace(host,"\s",",")

Re: How do I combine unique values of a field into one for multiple fields?

srizan — Fri, 30 Nov 2018 15:01:09 GMT

So far I was able to combine 2 of those into one stats, introducing third value(X) as X would not yeild the last field in the final result. Here is what the query looks like:

 source=*prod*
| dedup SRV AVER ZONE  
| fields + SRV , IVER, AVER, ZONE 
| stats values(ZONE) as ZONE values(IPADDR) as IPADDR by SRV AVER  
| nomv ZONE 
| eval ZONE=replace(ZONE,"\s",",")
| nomv IPADDR 
| eval IPADDR =replace(IPADDR ,"\s",",")

Re: How do I combine unique values of a field into one for multiple fields?

srizan — Fri, 30 Nov 2018 15:10:29 GMT

Surprisingly it worked after I switched values(IPADDR ) at the end.
Before I was doing the following

    source=*prod*
     | dedup SRV AVER ZONE  
     | fields + SRV , IVER, AVER, ZONE 
     | stats values(ZONE) as ZONE, values(IPADDR) as IPADDR, values(host) as host by SRV AVER  
     | nomv ZONE 
     | eval ZONE=replace(ZONE,"\s",",")
     | nomv IPADDR 
     | eval IPADDR =replace(IPADDR ,"\s",",") 
     | nomv host 
     | eval host=replace(host,"\s",",")

It worked after I switched place between host & IPADDR:

  source=*prod*
 | dedup SRV AVER ZONE  
 | fields + SRV , IVER, AVER, ZONE 
 | stats values(ZONE) as ZONE, values(host) as host,  values(IPADDR) as IPADDR by SRV AVER  
 | nomv ZONE 
 | eval ZONE=replace(ZONE,"\s",",")
 | nomv host 
 | eval host=replace(host,"\s",",")
 | nomv IPADDR 
 | eval IPADDR =replace(IPADDR ,"\s",",")

I am not sure why did this happen? Can anyone explain me the difference, and why it might not have worked. BTW, when I tried the first command, it did not even show the host field at all in the final output.