Splunk Search

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

cheriemilk
Path Finder

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??

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

cheriemilk
Path Finder

Thanks you~

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!