Splunk Search

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

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

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

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

Path Finder

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

0 Karma

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

Esteemed Legend
0 Karma