Help with stats count comma delimited field

[mebra1]

Hello all,

I have an event that looks similar to the following:

field_A="US", field_B="true", field_C="AB/CD,XYZ, <>DD,CT", field_D= "60"

I am trying to get the count occurrence of field_C during the past 3 months by using below query:

field_A="US", field_B="true" | stats count as ruleFired by field_C

It works fine for all the other values that don't have comma "," in field_C.

but if there is any comma in field_C the count doesn't calculated correctly.

for example all below will count as a same group

A -- > field_C="AB/CD,XYZ, <>DD,CT"
A -- > field_C="AB/CD,XYZ, DD,CT"
A -- > field_C="AB/CD,ABC, <>DD,CT"
A -- > field_C="AB/CD,ABC, DD,CT"

the result will be 

AB/CD                     4

Versus 

AB/CD,XYZ, <>DD,CT        1
AB/CD,XYZ, DD,CT          1
AB/CD,ABC, <>DD,CT        1
AB/CD,ABC, DD,CT          1

Any help would be much appreciated.

[mebra1]

Thank you @ITWhisperer @PickleRick 

each event will be as below, where a tostring() of object will be print out, if this helps.

3 example events

message=~ object [field_A=US, field_B=true, field_C=rule1, field_D=X, field_E=Y, field_F=Z]

message=~ object [field_A=US, field_B=true, field_C=AB/CD,XYZ, <>DD,CT, field_D=X, field_E=Y, field_F=Z]

message=~ object [field_A=US, field_B=true, field_C=AB/CD,ABC, DD,CT, field_D=X, field_E=Y, field_F=Z]

I will discuss above internally.

Thank you

[ITWhisperer]

I don't think it is a problem with stats - here is a runanywhere example showing the correct result

| makeresults
| eval field_A="US", field_B="true", field_C="AB/CD,XYZ, <>DD,CT|AB/CD,XYZ, DD,CT|AB/CD,ABC, <>DD,CT|AB/CD,ABC, DD,CT", field_D=60
| eval field_C=split(field_C,"|")
| mvexpand field_C
| stats count as rull_fired by field_C

Perhaps it is a problem with the extract of field_C? How have you done the extract?

[mebra1]

Hi Thank you for the reply.

The issue is there are over few thousands unique value for field_C, and I can't list them all.

The way I'm doing it, as long as field_A = US and field_B= true, then I know there was rule that triggered and the name of the rule will be set in field_C.  So I need to know how many time each rule got triggered.

field_A="US", field_B="true" | stats count as ruleFired by field_C

it works fine with above for most of them as long as there is no comma ',' in field_C.

but if there is comma in field_c, it only capture whatever before ,  and if the difference between few rules name are after comma ',' , it wont be able to group them separately 

[ITWhisperer]

You haven't answered the question - how is field_C extracted? Perhaps you need to extract it a different way

| rex "field_C=\"(?<field_C>[^\"]+)\""

[mebra1]

field_A="US", field_B="true" | stats count as ruleFired by field_C

Sorry I'm new to Splunk. I'm not extracting field_C in my query at all, and for some reason it's working fine for the one that doesn't have comma. (above is the complete query)

do I need to modify it to below?

field_A="US", field_B="true" | rex "field_C=\"(?<field_C>[^\"]+)\"" |stats count as ruleFired by field_C

[PickleRick]

It's not about what you do but about how your splunk is configured (the extractions defined for this particular sourcetype). Most probably, the extractions are defined so they only  apture values up to the comma.

You can verify it by running something like this

AB/CD | table field_C

You will most probably get only the AB/CD part in your table which will mean that you can manually extract the field with the regex @ITWhisperer provided but in general your splunk configuration should be corrected to capture the field correctly.

[ITWhisperer]

It is difficult to say since you probably cannot share your actual events. However, the scenario you are describing fits with field_C not being extracted correctly. By using the rex I suggested, you may be able to re-extract the field to resolve the issue.

[mebra1]

I was able to solve it by using below query.

field_A="US" field_B="true" | rex field_C="(?<ruleNameFired>.*[0-9,;]*field_D)"|stats count as ruleFired by ruleNameFired

this way I get the complete string value of field_C (including any value after any comma) and next fields name ( that can later on be removed from excel).

Thank you.