Splunk Search

Large Json Array, Spath and stats

jondukehds
Explorer

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?

Labels (1)
0 Karma

tscroggins
Builder

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

clientNameclientConsumptionMB
client1123
client2456

 

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.

0 Karma