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

[vijaysubramania]

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}}]

[to4kawa · Accepted solution]

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

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

[thambisetty]

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

[vijaysubramania]

@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

[richgalloway]

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

[thambisetty]

Great. You got what you want.

[richgalloway]

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

[vijaysubramania]

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

[richgalloway]

It should work for all data that is in the same format. Just use the part below the comment.