Getting Data In

Zip values from JSON.

sandeepmakkena
Contributor

I want to get the total units by PartNumber. I tried using spath but it didnt work maybe I am doing something wrong, this is my 1st time work with Json bodies in Splunk, can someone help. Thanks.

{ [-]
app: { [+]
}
dtm: 2019-09-16 11:21:30.780 PDT
logger: transaction.com.WEB_ORDER_CREATE
msg: { [-]
data: { [-]
cartId: 00000000-784f-642d-d000-0000784f142d
casId: a493ff7e-2d87-416c-ba08-0663107e958c
clientSubType: web.common
clientType: web
cosId: 80f1a259-7187-4f01-9c85-acd7e054233d
deliveryGroup: SINGLE_ADDRESS
features: [ [-]
crd
]
fraudDecision: NF
lineItems: [ [-]
{ [-]
commitCode: 0
deliveryDate: Wed, Sep 18
deliveryType: STH
partNumber: MRYR2LL/A
product: Phone 64GB Black
qty: 2
resolvedDate: Wed, Sep 18
shipMethodCode: E2
}
{ [-]
commitCode: 0
deliveryDate: Within 5 days after shipping
deliveryType: STH
partNumber: MRW62ZM/A
product: Phone Case
qty: 1
resolvedDate: In Stock
shipMethodCode: UG
}
]
ops.response: SUCCEEDED
orderType: order
payments: [ [+]
]
pssId: Wb90d652a55994842b0474288ba0dea76
remoteHost: 17.123.345
storeFront: { [+]
}
webOrderNumber: W12345678
}
headerData: { [+]
}
}
}

0 Karma

poete
Builder

Hello @sandeepmakkena,

please find below the query that results in a table containing the number of partNumber per qty.

|makeresults|eval _raw="{
    \"app\": {
        \"foo1\": \"bar1\"
    },
    \"dtm\": \"2019-09-16 11:21:30.780 PDT\",
    \"logger\": \"transaction.com.WEB_ORDER_CREATE\",
    \"msg\": {
        \"data\": {
            \"cartId\": \"00000000-784f-642d-d000-0000784f142d\",
            \"casId\": \"a493ff7e-2d87-416c-ba08-0663107e958c\",
            \"clientSubType\": \"web.common\",
            \"clientType\": \"web\",
            \"cosId\": \"80f1a259-7187-4f01-9c85-acd7e054233d\",
            \"deliveryGroup\": \"SINGLE_ADDRESS\",
            \"features\": [\"crd\"],
            \"fraudDecision\": \"NF\",
            \"lineItems\": [{
                    \"commitCode\": 0,
                    \"deliveryDate\": \"Wed, Sep 18\",
                    \"deliveryType\": \"STH\",
                    \"partNumber\": \"MRYR2LL/A\",
                    \"product\": \"Phone 64GB Black\",
                    \"qty\": 2,
                    \"resolvedDate\": \"Wed, Sep 18\",
                    \"shipMethodCode\": \"E2\"
                },
                {
                    \"commitCode\": 0,
                    \"deliveryDate\": \"Within 5 days after shipping\",
                    \"deliveryType\": \"STH\",
                    \"partNumber\": \"MRW62ZM/A\",
                    \"product\": \"Phone Case\",
                    \"qty\": 1,
                    \"resolvedDate\": \"In Stock\",
                    \"shipMethodCode\": \"UG\"
                }
            ],
            \"ops.response\": \"SUCCEEDED\",
            \"orderType\": \"order\",
            \"payments\": [{
                \"foo2\": \"bar2\"
            }],
            \"pssId\": \"Wb90d652a55994842b0474288ba0dea76\",
            \"remoteHost\": \"17.123.345\",
            \"storeFront\": {
                \"foo3\": \"bar3\"
            },
            \"webOrderNumber\": \"W12345678\"
        },
        \"headerData\": {
            \"foo4\": \"bar4\"
        }
    }
}"|spath |rename msg.data.lineItems{}.partNumber as partNumber msg.data.lineItems{}.qty as qty |table partNumber, qty| eval x=mvzip(partNumber, qty)|table x |mvexpand x
  | eval x = split(x,",")|eval partNumber = mvindex(x,0)|eval qty=mvindex(x,1)|table partNumber,qty
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@sandeepmakkena

Can you please share valid JSON event into code block?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...