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??
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
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
Thanks you~