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"}]}
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
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
Great explanation...makes perfect sense ..thanks for your detailed response
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!