Splunk Search

How to search my JSON data to output Name, Sum(items_sold), and grouped by Name?

ronak
Path Finder

I've following JSON format data...below is one sample record. I'm looking for output in the format [ name , sum(items_sold) ], essentially select name, sum(items_sold) group by name

The issue that I face when I run the search is : I get output like

C12 West Grill - 852
B15 W Island - 852
B04 E island - 852

Meaning, the search is doing something like (a) first sum the field items_sold (b) now give that total sum to each vendor name INSTEAD of grouping by vendor_name

Any pointers as to how I can write the search here?

thanks, ronak

{"epoch_start_datetime":"1432947300","report_parameters":{"venue_name":"MIDFLORIDA Credit Union Amphitheatre","api":"venue_vendor_grouping","execution_timestamp":"2015-05-30 01:28:48","start_date_epoch":"1432947300","end_date_epoch":"1432947599","venue_id":"197","timezone":"UTC","start_date":"2015-05-30 00:55:00","end_date":"2015-05-30 00:59:59","report":"items","grouping":"vendor","sorting":"value","filter":false,"filter_id":false,"filter2":false,"filter_id2":false,"filter3":false,"filter_id3":false},"sales_summary":{"total_orders":"460","total_sales":"8540.50","fees_collected":"0.00","total_tips":"304.93","tax_collected":"0.00","average_order_size":"18.57","total_items":"824"},"report_data":[{"vendor_id":"1740","name":"C03 East Grill","items_sold":"135","total_orders":"67","total_sales":"844.25"},{"vendor_id":"2240","name":"B04 E Island Bar","items_sold":"60","total_orders":"29","total_sales":"785.00"},{"vendor_id":"2230","name":"C12 West Grill","items_sold":"54","total_orders":"29","total_sales":"329.50"},{"vendor_id":"2251","name":"B15 W Island Bar","items_sold":"52","total_orders":"27","total_sales":"687.50"},{"vendor_id":"2247","name":"B11","items_sold":"51","total_orders":"32","total_sales":"459.25"},{"vendor_id":"2254","name":"B18 W Daq Shack","items_sold":"39","total_orders":"22","total_sales":"392.00"},{"vendor_id":"2263","name":"V02 VIP Club Bar","items_sold":"39","total_orders":"13","total_sales":"476.50"},{"vendor_id":"2237","name":"B01 E Bev Bar","items_sold":"37","total_orders":"20","total_sales":"375.50"},{"vendor_id":"2229","name":"C06 East Beverage","items_sold":"36","total_orders":"21","total_sales":"324.50"},{"vendor_id":"2243","name":"B07 East lawn","items_sold":"35","total_orders":"22","total_sales":"342.75"},{"vendor_id":"2253","name":"B17 W lawn","items_sold":"30","total_orders":"21","total_sales":"312.50"},{"vendor_id":"2781","name":"B03 Ice Cold Beer","items_sold":"29","total_orders":"18","total_sales":"343.75"},{"vendor_id":"2257","name":"B21 Lawn Bar","items_sold":"28","total_orders":"16","total_sales":"308.50"},{"vendor_id":"2262","name":"V01 VIP Box Seats","items_sold":"27","total_orders":"12","total_sales":"337.25"},{"vendor_id":"2241","name":"B05 E Cocktail","items_sold":"25","total_orders":"11","total_sales":"331.25"},{"vendor_id":"2782","name":"B03 SW Draft","items_sold":"25","total_orders":"15","total_sales":"301.00"},{"vendor_id":"2252","name":"B16 W Specialty Bar","items_sold":"21","total_orders":"12","total_sales":"182.25"},{"vendor_id":"2772","name":"B08 E Daq Shack","items_sold":"21","total_orders":"14","total_sales":"244.50"},{"vendor_id":"2250","name":"B14 W Beer Wall","items_sold":"19","total_orders":"10","total_sales":"188.75"},{"vendor_id":"2780","name":"B03 Bud Light Bar","items_sold":"17","total_orders":"12","total_sales":"165.00"},{"vendor_id":"2256","name":"B20 W Stairs Port","items_sold":"16","total_orders":"9","total_sales":"151.75"},{"vendor_id":"2245","name":"B09 Pub","items_sold":"15","total_orders":"7","total_sales":"155.00"},{"vendor_id":"2258","name":"B22E Grill","items_sold":"14","total_orders":"7","total_sales":"171.00"},{"vendor_id":"2242","name":"B06 E Specialty Bar","items_sold":"13","total_orders":"6","total_sales":"154.75"},{"vendor_id":"2238","name":"B02 E Beer Port","items_sold":"8","total_orders":"5","total_sales":"96.00"},{"vendor_id":"2774","name":"B08 Ice Cold Beer","items_sold":"7","total_orders":"3","total_sales":"80.50"}]}
Tags (4)
1 Solution

sideview
SplunkTrust
SplunkTrust

The main challenge here is that your events are too big, and the items you want to analyze are just the little dictionaries in the report_data array. Sometimes the answer is to change the index-time aggregation settings so that the events going into the index more closely match the granularity of the items actually being analyzed later. Here of course and quite often with json data, that's not really an option.

So we are have to break out some search language tools to kind of "un-multivalue" and "mvexpand" the data.

First I'm definitely assuming that you're already using the spath command. That seems obvious from the nature of your question. You've used spath to extract all the fields and then when you try to use stats on them, there's only one giant event with tons of multivalue values and it comes out all wrong.

So.. we need to zip up the names with the corresponding sales number (mvzip function inside eval command) and then turn each of those pairs into it's own row in our results (mvexpand). Then we have to split our mvexpand'ed field back into the name and the sales number. Then finally we can use stats and the output will make sense.

<your search terms here> | spath input=_raw | rename "report_data{}.name" as name "report_data{}.total_sales" as sales | eval combined=mvzip(name,sales,":::") | mvexpand combined | eval combined=split(combined,":::") | eval name=mvindex(combined,0) | eval sales=mvindex(combined,1) | stats count sum(sales) by name

View solution in original post

sideview
SplunkTrust
SplunkTrust

The main challenge here is that your events are too big, and the items you want to analyze are just the little dictionaries in the report_data array. Sometimes the answer is to change the index-time aggregation settings so that the events going into the index more closely match the granularity of the items actually being analyzed later. Here of course and quite often with json data, that's not really an option.

So we are have to break out some search language tools to kind of "un-multivalue" and "mvexpand" the data.

First I'm definitely assuming that you're already using the spath command. That seems obvious from the nature of your question. You've used spath to extract all the fields and then when you try to use stats on them, there's only one giant event with tons of multivalue values and it comes out all wrong.

So.. we need to zip up the names with the corresponding sales number (mvzip function inside eval command) and then turn each of those pairs into it's own row in our results (mvexpand). Then we have to split our mvexpand'ed field back into the name and the sales number. Then finally we can use stats and the output will make sense.

<your search terms here> | spath input=_raw | rename "report_data{}.name" as name "report_data{}.total_sales" as sales | eval combined=mvzip(name,sales,":::") | mvexpand combined | eval combined=split(combined,":::") | eval name=mvindex(combined,0) | eval sales=mvindex(combined,1) | stats count sum(sales) by name

ronak
Path Finder

Great explanation...makes perfect sense ..thanks for your detailed response

0 Karma

f5x6kb8
Explorer

We were having the same issue. I took your masterpiece and spun it up with our search and WHAMMO! Extreme Goodness! Thank you very much!

0 Karma

woodcock
Esteemed Legend
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...