Getting Data In

Sum fields per event in multiple json objects

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

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

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

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