Getting Data In

Sum fields per event in multiple json objects

yoshispendiff
Explorer

I have a json event like this:

 {
        "BODY": {
            "user_id": "000",
            "type": "sale",
            "order": {
                "order_id": 0,
                "currency": "USD",
                "items": [
                    {
                        "item_id": 1234,
                        "catalog_id": "abcd",
                        "amount": 39,
                        "description": "Thing"
                    },
                    {
                        "item_id": 2345,
                        "catalog_id": "bcde",
                        "amount": 50,
                        "description": "Other thing"
                    }
                ]
            }
        }
    }

This is a (rough approximation, I've cut things down) to a sales event in our system. You can see in the items entry there is an array of objects that contains details of the sale, including price.

Essentially what I'd like to be able to do with this event is add a new field which has the total of all the items in the sale. After spath the amount is accessed via BODY.order.items{}.amount.

I've tried using foreach and eval but that seems to just output one field for all events rather than a field for every event.

I assume there are perhaps ways to rename the fields so the items are named individually but considering the items in the sale is variable I'm not sure how this would work.

What are my options here?

Edit:
I should add this is how I was trying to use foreach, perhaps I'm wrong here?
eval total=0 | foreach BODY.order.items{}.amount [eval total=total + '<>']

Edit 2:
I've just switched to a new user after a job change so can't comment. I think both solutions are valid really, it depends how you want to aggregate (i.e. aggregate per event or on a set value) as they both have different use cases. For me though aggregating per line is what I was after, so thanks.
I also found this which would also solve the problem and adds extra functionality: http://jordan.broughs.net/archives/2012/06/mvsum-for-splunk-summing-multi-valued-fields-within-a-sin...
From what I can tell as well it seems like the foreach command should actually generate a field per event so maybe it just doesn't play nice with multivalue fields.

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

Append this after your base search:

...  | spath BODY.order.items{}.amount output=amount | streamstats count as event_count | eventstats sum(amount) by event_count

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

Append this after your base search:

...  | spath BODY.order.items{}.amount output=amount | streamstats count as event_count | eventstats sum(amount) by event_count

somesoni2
SplunkTrust
SplunkTrust

I guess you would have to split all items as separate events and then calculate the sum. Try this

your base search giving fields BODY.user_id,BODY.type, BODY.order.order_id,BODY.order.currency, BODY.order.items{}.item_id, BODY.order.items{}.catalog_id,BODY.order.items{}.amountBODY.order.items{}.description | eval Amount='BODY.order.items{}.amount'  | mvexpand Amount | eventstats sum(Amount) as TotalAmount by BODY.user_id,BODY.order.order_id | fields - Amount | stats values(*) as * by BODY.user_id,BODY.order.order_id
0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...