Splunk Search

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

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

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

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

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

Super Champion

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.

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

SplunkTrust
SplunkTrust

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

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

Super Champion

Great. You got what you want.

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

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

Path Finder

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

0 Karma

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, an upvote would be appreciated.
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!