topic Large Json Array, Spath and stats in Splunk Search

Large Json Array, Spath and stats

jondukehds — Mon, 25 Jan 2021 21:19:49 GMT

So I have a large JSON array that is now being brought in and ingested correctly, but I cannot do any stats function on it. What I'd like to do are things like this, but the below search just brings in the same value for each name.

So then I tried to do this.

index=storage sourcetype="netbackup:license" | spath output=Name path=data{}.attributes.clientDetails{}.clientName | spath output=ClientConsumptionMB path=data{}.attributes.clientDetails{}.clientConsumptionMB | spath output=PolicyName path=data{}.attributes.clientDetails{}.policyDetails{}.policyName | spath output=PolicyType path=data{}.attributes.clientDetails{}.policyDetails{}.policyType | eval Name=upper(Name) | eval NameCount=mvzip(Name,ClientConsumptionMB) | mvexpand NameCount | eval mvNameCount=split(NameCount,",") | eval Name=mvindex(mvNameCount,0) | eval ClientConsumptionMB=mvindex(mvNameCount,1) | stats last(ClientConsumptionMB) by Name

And ran into a 300 line limit for mvexpand.

Help?

Re: Large Json Array, Spath and stats

tscroggins — Sun, 21 Feb 2021 18:31:18 GMT

@jondukehds

Assuming this is output from the NetBackup Licensing API, we can use mock test data:

{ "data": [ { "type": "string", "id": "string", "attributes": { "masterConsumptionMB": 0, "clientDetails": [ { "clientName": "client1", "clientConsumptionMB": 123, "policyDetails": [ { "backupId": "string", "policyName": "string", "masterServer": "string", "policyType": "string", "policyConsumptionMB": 0 } ] }, { "clientName": "client2", "clientConsumptionMB": 456, "policyDetails": [ { "backupId": "string", "policyName": "string", "masterServer": "string", "policyType": "string", "policyConsumptionMB": 0 } ] } ] } } ], "meta": { "pagination": { "prev": 0, "next": 0, "first": 0, "last": 0, "count": 0, "offset": 0, "limit": 0 } } }

We have clientName values client1 and client2 with clientConsumptionMB values 123 and 456, respectively.

Putting this into a synthetic search:

| makeresults
| eval capacity="{\"data\":[{\"type\":\"string\",\"id\":\"string\",\"attributes\":{\"masterConsumptionMB\":0,\"clientDetails\":[{\"clientName\":\"client1\",\"clientConsumptionMB\":123,\"policyDetails\":[{\"backupId\":\"string\",\"policyName\":\"string\",\"masterServer\":\"string\",\"policyType\":\"string\",\"policyConsumptionMB\":0}]},{\"clientName\":\"client2\",\"clientConsumptionMB\":456,\"policyDetails\":[{\"backupId\":\"string\",\"policyName\":\"string\",\"masterServer\":\"string\",\"policyType\":\"string\",\"policyConsumptionMB\":0}]}]}}],\"meta\":{\"pagination\":{\"prev\":0,\"next\":0,\"first\":0,\"last\":0,\"count\":0,\"offset\":0,\"limit\":0}}}"
| spath input=capacity
| rename "data{}.attributes.clientDetails{}.clientName" as clientName, "data{}.attributes.clientDetails{}.clientConsumptionMB" as clientConsumptionMB
| eval consumption=mvzip(clientName, clientConsumptionMB, "|")
| fields consumption
| mvexpand consumption
| eval consumption=split(consumption, "|"), clientName=mvindex(consumption, 0), clientConsumptionMB=mvindex(consumption, 1)
| fields - consumption
| stats latest(clientConsumptionMB) as clientConsumptionMB by clientName

yields:

clientName	clientConsumptionMB
client1	123
client2	456

mvexpand has no default result limit; it's memory bound by the max_mem_usage_mb setting in the mvexpand stanza in limits.conf. If you're hitting the memory ceiling, try reducing your footprint as I've done above. If that fails, try increasing (or asking your Splunk administrator to increase) max_mem_usage_mb.

Re: Large Json Array, Spath and stats

quzen — Wed, 08 Sep 2021 07:25:59 GMT

Doing nomv on the extracted field helped me:

| nomv Name

After that stats by that field worked.