[help]stats with key and value extracted by rex not return correct result.

[cheriemilk]

Hi team,

I have below 2 events:

C_BN="[{pmRating:3},{riskOfLoss:9}]"
C_BN="[{sysOverallPerformance:3},{sysOverallPotential:4},{pmRating:5},{compBonusTarget:3}]"

, and rex pattern to extract key and value (delim is ":") is "(?P<key>\w+):(?P<value>\w+)".

When i run below query:

sourcetype=perf_log_bizx AND ACT=SAVE_CALIBRATION_TEMPLATE AND PQ=CALIBRATION_SESSION_ADMIN
| rex field=C_BN max_match=0 "(?P<key>\w+):(?P<value>\d+)"
| stats count by key value

Splunk returns below table to me which is not correct:

key	value	count
compBonusTarget	3	2
compBonusTarget	4	1
compBonusTarget	5	1
pmRating	3	3
pmRating	4	1
pmRating	5	1
pmRating	9	1
riskOfLoss	3	1
riskOfLoss	9	1
sysOverallPerformance	3	2
sysOverallPerformance	4	1
sysOverallPerformance	5	1
sysOverallPotential	3	2
sysOverallPotential	4	1
sysOverallPotential	5	1

Expected Result:

key	value	count
compBonusTarget	3	1
pmRating	3	1
pmRating	5	1
riskOfLoss	9	1
sysOverallPerformance	3	1
sysOverallPotential	4	1

What is the issue with my query??

[richgalloway · Accepted solution]

The problem stems from the rex command producing multi-value fields.  Stats does not process those fields as expected.  The solution is to split the multi-value fields into separate events, but the process for doing that while preserving the relationship between key and value is a bit convoluted.

| makeresults | eval data="C_BN=\"[{pmRating:3},{riskOfLoss:9}]\"|
C_BN=\"[{sysOverallPerformance:3},{sysOverallPotential:4},{pmRating:5},{compBonusTarget:3}]\"" | eval data=split(data,"|") | mvexpand data | eval _raw=data | extract pairdelim="," kvdelim="="
```Above just defines test data```
| rex field=C_BN max_match=0 "(?P<key>\w+):(?P<value>\d+)"
```Combine key and value fields```
| eval zip=mvzip(key, value)
```Split the multi-value fields into events```
| mvexpand zip
```Separate the fields```
| eval split=split(zip,",")
| eval key=mvindex(split,0), value=mvindex(split,1)
| stats count by key value

[cheriemilk]

Thanks you~