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

[venkatrajan04]

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

[woodcock · Accepted solution]

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

[venkatrajan04]

Thanks WoodcocK! What exactly does the sed command do ?

[woodcock]

https://en.wikipedia.org/wiki/Sed

[woodcock]

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

[venkatrajan04]

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