topic Re: Sum categories from a main search. in Splunk Search

Sum categories from a main search.

JMPP — Fri, 31 May 2024 20:35:31 GMT

Hi Cummunity team,

I have a complex query to gather the data below, but a new request came up, it was asked to me to add in the report email subject the product category totals by Category. with the $result.productcat1$ and $result.productcat2$ I could apprach that,
but the way I'm calculating the totals I'm not getting the expected numbers, because I'm appeding the columns from a subquery and transposing the values with xyseries.

Could you please suggest how can I sum(Sales Total) by productcat1 and productcat2 in a new field but keeping the same output as I have now?,

e.g.:
something like if ProducCategory="productcat1"; then productcat1=productcat1+SalesTotal, else productcat2=productcat2+SalesTotal ``` But Print the original output ```

Consider productcat1 and productcat2 are fixed values.

ENV	ProducCategory	ProductName	SalesCondition	SalesTotal	productcat1	productcat2
prod	productcat1	productR	blabla	9	152	160
prod	productcat1	productj	blabla	8
prod	productcat1	productc	blabla	33
prod	productcat2	productx	blabla	77
prod	productcat2	productpp	blabla	89
prod	productcat2	productRr	blabla	11
prod	productcat1	productRs	blabla	6
prod	productcat1	productRd	blabla	43
prod	productcat1	productRq	blabla	55

Thanks in advance.

Re: Sum categories from a main search.

dtburrows3 — Fri, 31 May 2024 22:34:11 GMT

I think using eventstats can get you the desired output you are looking for if I am interpreting your question correctly.

<base_search> | eventstats sum(eval(case('ProductCategory'=="productcat1", 'Sales Total'))) as productcat1, sum(eval(case('ProductCategory'=="productcat2", 'Sales Total'))) as productcat2

Or for a more dynamic approach something like this may work.

<base_search> | eventstats sum("Sales Total") as overall_sales by ProductCategory | eval overall_sales_json=json_object("fieldname", 'ProductCategory', "value", 'overall_sales') | eventstats values(overall_sales_json) as overall_sales_json | foreach mode=multivalue overall_sales_json [ | eval fieldname=spath('<<ITEM>>', "fieldname"), field_value=spath('<<ITEM>>', "value"), combined_json=if( isnull(combined_json), json_object(fieldname, field_value), json_set(combined_json, fieldname, field_value) ) ] | fromjson combined_json prefix=dynamic_ | fields - combined_json, overall_sales_json, fieldname, field_value, overall_sales ``` Below code is if you only want the new fields on the first row ``` | streamstats count as line_number | foreach dynamic_* [ | eval <<FIELD>>=if( 'line_number'==1, '<<FIELD>>', null() ) ] | fields - line_number | rename dynamic_* as *

Re: Sum categories from a main search.

PickleRick — Sat, 01 Jun 2024 09:27:25 GMT

You can use eventstats as @dtburrows3 already pointed out, just keep in mind that Splunk will put those sums into every single row in your results. It's not an Excel, you cannot merge cells here.

Re: Sum categories from a main search.

JMPP — Tue, 04 Jun 2024 16:43:33 GMT

Hi @dtburrows3,

Thanks so much it helped me a lot your suggestions, for now I will go with eventstats solutions.

For foreach command I need to go deep on it since it is more complex.

@PickleRick I will try xyseries, same as I did before to have the expected single values for the productcat# fields. Need to push this report to Production ASAP.