Splunk Search

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

venkatrajan04
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
1 Solution

woodcock
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

woodcock
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

venkatrajan04
New Member

Thanks WoodcocK! What exactly does the sed command do ?

0 Karma

woodcock
Esteemed Legend
0 Karma

woodcock
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

venkatrajan04
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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!