Splunk Search
Highlighted

Multivalue fields- Count Values that match a value in multi value field

New Member

Hello SPlunk team,
my base query returns something like the table below . I need to find the count of all intents that do not have the values for case column as WDC and also find out count of all intents that match CCC in Case field . Note- case field is a multi value field.
ID | Intent| Case
111|reading|WDC-333
122|reading|WDC-345,CCC-666,I -888
123|reading|WDC-567,I-444
155|reading|WDC-43, S-888,B-999

0 Karma
Highlighted

Re: Multivalue fields- Count Values that match a value in multi value field

Esteemed Legend

Give us a mockup of what you would like your final output data to be. I don't get it at all.

0 Karma
Highlighted

Re: Multivalue fields- Count Values that match a value in multi value field

New Member

Hi Woodcock! For the above table.Question1) find the count of all intents that do not have at least 1 value for the column CASE as WDC

Answer1) I would like the value as 0 for the intent -Out of the 4 tuples, all of the them have alteast one of the CASE Column value as WDC- .
2nd Question)Find out count of all intents that match atleast one occurrence of CCC in Case field .
I would like the value as 1. Out of the 4 tuples, only one of them have the values as CCC for the column Case( 2nd record)

Column Case has multiple values for each record of intentEach of the values in the column CASE are alphanumeric. Have to search by prefix i.e. WDC or CCC

0 Karma
Highlighted

Re: Multivalue fields- Count Values that match a value in multi value field

Esteemed Legend

Like this:

|makeresults | eval raw="111|reading|WDC-333:122|reading|WDC-345,CCC-666,I-888:123|reading|WDC-567,I-444:155|reading|WDC-43, S-888,B-999"
| makemv delim=":" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<ID>[^\|]+)\|(?<Intent>[^\|]+)\|(?<Case>[^\|]+)$"
| makemv delim="," Case
| table ID Intent Case

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution."

| eval CasePrefix = Case
| rex field=CasePrefix mode=sed "s/-\d+$//"
| chart count BY ID CasePrefix

View solution in original post

0 Karma
Highlighted

Re: Multivalue fields- Count Values that match a value in multi value field

New Member

Thanks WoodcocK! What exactly does the sed command do ?

0 Karma
Highlighted

Re: Multivalue fields- Count Values that match a value in multi value field

Esteemed Legend
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.