Large Json Array, Spath and stats

[jondukehds]

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.

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
|stats last(ClientConsumptionMB) by 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?

[quzen]

Doing nomv on the extracted field helped me:

| nomv Name

 After that stats by that field worked.

[tscroggins]

@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.