Splunk Search

Count the total no of values in multivalue field - json response

vijaysubramania
Path Finder

HI, I need to get the count of all the packages from the json body and display the total no of packages available for that transaction

["143","144","172","205","241","350","364","365","385","449","486","683","764","1043","1220","1241","1287","1309","1381","1432","1456","1509","1578","1641","1727","1753","2835","3299","3309","3566","3626","3897","4127","4349","4456","4805","4835","4870","4872","4890","4954","5254","5485","5486","5487","5488","5489","5490","5491","5492","5493","5494","5495","5496"]

Log:

[2020-07-31 07:58:47,906] [INFO ] [http-nio-8080-exec-31] [txnId=1596182327895-1558302899] [clientIp=172.27.159.170] [accountNumber=] [charterId=] [sessionHashCode=] [methodPath=/services/v2/entitlements/packages/{macAddress}:GET] TxnLogging - [txnmarker=TXNEnd] [timestamp=Fri Jul 31 07:58:47 UTC 2020] [application=/lrmmiddle/] [methodPath=/services/v2/entitlements/packages/{macAddress}:GET] [duration=11] [clientIp=172.27.159.170] [entity=Response Status:200] [txnId=1596182327895-1558302899] [status=200] [responseJson={"macAddress":"3438B79C5638","blockAll":false,"packages":["143","144","172","205","241","350","364","365","385","449","486","683","764","1043","1220","1241","1287","1309","1381","1432","1456","1509","1578","1641","1727","1753","2835","3299","3309","3566","3626","3897","4127","4349","4456","4805","4835","4870","4872","4890","4954","5254","5485","5486","5487","5488","5489","5490","5491","5492","5493","5494","5495","5496"],"statusCode":null,"header":{"status":"SUCCESS","transaction":{"statusMessage":"0","returnCode":"0","returnMessage":"0","exception":null,"id":"1596182327895-1558302899","dateTime":"2020-07-31T07:58:47.906Z","duration":11,"hostname":"lrmmiddle-f56fd7d48-s2r4l"},"sourceTransaction":null}}]

Labels (1)
Tags (1)
0 Karma
1 Solution

to4kawa
Ultra Champion
| makeresults
| eval _raw="[2020-07-31 07:58:47,906] [INFO ] [http-nio-8080-exec-31] [txnId=1596182327895-1558302899] [clientIp=172.27.159.170] [accountNumber=] [charterId=] [sessionHashCode=] [methodPath=/services/v2/entitlements/packages/{macAddress}:GET] TxnLogging - [txnmarker=TXNEnd] [timestamp=Fri Jul 31 07:58:47 UTC 2020] [application=/lrmmiddle/] [methodPath=/services/v2/entitlements/packages/{macAddress}:GET] [duration=11] [clientIp=172.27.159.170] [entity=Response Status:200] [txnId=1596182327895-1558302899] [status=200] [responseJson={\"macAddress\":\"3438B79C5638\",\"blockAll\":false,\"packages\":[\"143\",\"144\",\"172\",\"205\",\"241\",\"350\",\"364\",\"365\",\"385\",\"449\",\"486\",\"683\",\"764\",\"1043\",\"1220\",\"1241\",\"1287\",\"1309\",\"1381\",\"1432\",\"1456\",\"1509\",\"1578\",\"1641\",\"1727\",\"1753\",\"2835\",\"3299\",\"3309\",\"3566\",\"3626\",\"3897\",\"4127\",\"4349\",\"4456\",\"4805\",\"4835\",\"4870\",\"4872\",\"4890\",\"4954\",\"5254\",\"5485\",\"5486\",\"5487\",\"5488\",\"5489\",\"5490\",\"5491\",\"5492\",\"5493\",\"5494\",\"5495\",\"5496\"],\"statusCode\":null,\"header\":{\"status\":\"SUCCESS\",\"transaction\":{\"statusMessage\":\"0\",\"returnCode\":\"0\",\"returnMessage\":\"0\",\"exception\":null,\"id\":\"1596182327895-1558302899\",\"dateTime\":\"2020-07-31T07:58:47.906Z\",\"duration\":11,\"hostname\":\"lrmmiddle-f56fd7d48-s2r4l\"},\"sourceTransaction\":null}}]"
| kv
| spath input=responseJson packages{} output=packages
| stats count(packages) sum(packages)

View solution in original post

to4kawa
Ultra Champion
| makeresults
| eval _raw="[2020-07-31 07:58:47,906] [INFO ] [http-nio-8080-exec-31] [txnId=1596182327895-1558302899] [clientIp=172.27.159.170] [accountNumber=] [charterId=] [sessionHashCode=] [methodPath=/services/v2/entitlements/packages/{macAddress}:GET] TxnLogging - [txnmarker=TXNEnd] [timestamp=Fri Jul 31 07:58:47 UTC 2020] [application=/lrmmiddle/] [methodPath=/services/v2/entitlements/packages/{macAddress}:GET] [duration=11] [clientIp=172.27.159.170] [entity=Response Status:200] [txnId=1596182327895-1558302899] [status=200] [responseJson={\"macAddress\":\"3438B79C5638\",\"blockAll\":false,\"packages\":[\"143\",\"144\",\"172\",\"205\",\"241\",\"350\",\"364\",\"365\",\"385\",\"449\",\"486\",\"683\",\"764\",\"1043\",\"1220\",\"1241\",\"1287\",\"1309\",\"1381\",\"1432\",\"1456\",\"1509\",\"1578\",\"1641\",\"1727\",\"1753\",\"2835\",\"3299\",\"3309\",\"3566\",\"3626\",\"3897\",\"4127\",\"4349\",\"4456\",\"4805\",\"4835\",\"4870\",\"4872\",\"4890\",\"4954\",\"5254\",\"5485\",\"5486\",\"5487\",\"5488\",\"5489\",\"5490\",\"5491\",\"5492\",\"5493\",\"5494\",\"5495\",\"5496\"],\"statusCode\":null,\"header\":{\"status\":\"SUCCESS\",\"transaction\":{\"statusMessage\":\"0\",\"returnCode\":\"0\",\"returnMessage\":\"0\",\"exception\":null,\"id\":\"1596182327895-1558302899\",\"dateTime\":\"2020-07-31T07:58:47.906Z\",\"duration\":11,\"hostname\":\"lrmmiddle-f56fd7d48-s2r4l\"},\"sourceTransaction\":null}}]"
| kv
| spath input=responseJson packages{} output=packages
| stats count(packages) sum(packages)

vijaysubramania
Path Finder

This value is dynamic. I just need to split and count the no of channels present within the packages

vijaysubramania_0-1596526466216.png

Tags (2)
0 Karma

thambisetty
SplunkTrust
SplunkTrust

Add below search to your dataset.

| rex "packages\":\[(?<packages>[^\]]+)" 
| rex mode=sed field=packages "s/\"//g"
| eval packages=split(packages,",")
| mvexpand packages
| stats count, sum(packages) as sum
| table count, sum
————————————
If this helps, give a like below.

vijaysubramania
Path Finder

@thambisetty 
Thanks Buddy, I got an idea from your query and rewrote it. It works perfect now 😊. I couldn't execute the last 3 lines of query ( | mvexpand packages | stats count, sum(packages) as sum| table count, sum) So, I rewrote it.
| rex "packages\":\[(?<packages>[^\]]+)"
| rex mode=sed field=packages "s/\"//g"
| eval NoOfPackages=mvcount(packages)
| eval NoOfPackages=mvcount(split(packages,","))
| table NoOfPackages packages

vijaysubramania_0-1596567657037.png

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.

---
If this reply helps you, Karma would be appreciated.
0 Karma

thambisetty
SplunkTrust
SplunkTrust

Great. You got what you want.

————————————
If this helps, give a like below.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try this run-anywhere example search.

| makeresults
| eval _raw="[2020-07-31 07:58:47,906] [INFO ] [http-nio-8080-exec-31] [txnId=1596182327895-1558302899] [clientIp=172.27.159.170] [accountNumber=] [charterId=] [sessionHashCode=] [methodPath=/services/v2/entitlements/packages/{macAddress}:GET] TxnLogging - [txnmarker=TXNEnd] [timestamp=Fri Jul 31 07:58:47 UTC 2020] [application=/lrmmiddle/] [methodPath=/services/v2/entitlements/packages/{macAddress}:GET] [duration=11] [clientIp=172.27.159.170] [entity=Response Status:200] [txnId=1596182327895-1558302899] [status=200] [responseJson={\"macAddress\":\"3438B79C5638\",\"blockAll\":false,\"packages\":[\"143\",\"144\",\"172\",\"205\",\"241\",\"350\",\"364\",\"365\",\"385\",\"449\",\"486\",\"683\",\"764\",\"1043\",\"1220\",\"1241\",\"1287\",\"1309\",\"1381\",\"1432\",\"1456\",\"1509\",\"1578\",\"1641\",\"1727\",\"1753\",\"2835\",\"3299\",\"3309\",\"3566\",\"3626\",\"3897\",\"4127\",\"4349\",\"4456\",\"4805\",\"4835\",\"4870\",\"4872\",\"4890\",\"4954\",\"5254\",\"5485\",\"5486\",\"5487\",\"5488\",\"5489\",\"5490\",\"5491\",\"5492\",\"5493\",\"5494\",\"5495\",\"5496\"],\"statusCode\":null,\"header\":{\"status\":\"SUCCESS\",\"transaction\":{\"statusMessage\":\"0\",\"returnCode\":\"0\",\"returnMessage\":\"0\",\"exception\":null,\"id\":\"1596182327895-1558302899\",\"dateTime\":\"2020-07-31T07:58:47.906Z\",\"duration\":11,\"hostname\":\"lrmmiddle-f56fd7d48-s2r4l\"},\"sourceTransaction\":null}}]"
```Above just defines test data```
| rex "packages\":\[(?<packages>[^\]]+)" 
| rex mode=sed field=packages "s/\"//g"
| eval packages=split(packages,",")
| mvexpand packages
| stats count, sum(packages) as sum
| table count, sum
---
If this reply helps you, Karma would be appreciated.
0 Karma

vijaysubramania
Path Finder

Thanks Rich. But, this is dynamic data within the body and it varies for each customer. Will this work?

0 Karma

richgalloway
SplunkTrust
SplunkTrust
It should work for all data that is in the same format. Just use the part below the comment.
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...