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, Karma 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, Karma would be appreciated.
0 Karma

cheriemilk
Path Finder

Thanks you~

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...