topic Re: Merge similar field values in Splunk Search

Merge similar field values

koushiknandan — Fri, 06 Oct 2017 12:09:06 GMT

Running the following query gives me a result with different field values.

index="XXXX" host="POLO*" | stats count by URL | sort-count

URI                                         |       count
/pup/folks/xy/hollow/yellow/red                 |       7
/pup/folks/xy/hollow/yellow/1234567/usage       |       1
/pup/police/xy/laptop/MASTER/hollow/1234567 |       1
/pup/folks/xy/hollow/yellow/1234567/usage       |       1
/pup/police/xy/laptop/MASTER/hollow/123456      |       1
/pup/folks/xy/hollow/yellow/12345/usage         |       1
/pup/folks/xy/hollow/yellow                     |       1
/pup/police/xy/laptop/MASTER/hollow/12345       |       1
/pup/folks/xy/hollow/yellow/123456/usage        |       5
/pup/folks/xy/hollow/yellow/123456/usage        |       5
/pup/folks/xy/hollow/yellow/123456/usage        |       5
/pup/police/xy/laptop/MASTER/hollow/123456      |       5
/pup/police/xy/laptop/MASTER/hollow/123456      |       5
/pup/folks/xy/hollow/yellow/123456/usage        |       4

Is there a way to show them like this? (Merge). What I have done below is take all the strings that matches "/pup/folks/xy/hollow/yellow/*/usage", and took a consolidated count.

/pup/folks/xy/hollow/yellow/*/usage                 |             22
/pup/police/xy/laptop/MASTER/hollow/*          |             13

Re: Merge similar field values

cmerriman — Fri, 06 Oct 2017 12:36:15 GMT

if you used a replace command, i think it will work. using _ instead of *, as we all know that * is a wildcard and I tried to escape it with \, but couldn't get it to work. might be another way, though.

|replace "/pup/folks/xy/hollow/yellow/*/usage" with "/pup/folks/xy/hollow/yellow/_/usage"
|replace "/pup/police/xy/laptop/MASTER/hollow/*" with "/pup/police/xy/laptop/MASTER/hollow/_"

Re: Merge similar field values

koushiknandan — Fri, 06 Oct 2017 13:14:12 GMT

I should have been more clear in asking the question.

A close sample is given here - https://answers.splunk.com/answers/61646/combining-multivalues-together-inside-a-field.html

Re: Merge similar field values

cmerriman — Fri, 06 Oct 2017 13:28:26 GMT

if you're looking for a rex command, is this what you're looking for:

| rex field=User mode=sed "s/\/pup\/folks\/xy\/hollow\/yellow\/.*\/usage/\/pup\/folks\/xy\/hollow\/yellow\/*\/usage/" 
| rex field=User mode=sed "s/\/pup\/police\/xy\/laptop\/MASTER\/hollow\/.*/\/pup\/police\/xy\/laptop\/MASTER\/hollow\/*/"
|stats sum(count) as total by User

Re: Merge similar field values

koushiknandan — Thu, 12 Oct 2017 12:30:43 GMT

Thank You!