topic Re: How to group rows together in a table and calculate the sum for each group? in Splunk Search

How to group rows together in a table and calculate the sum for each group?

asaste — Tue, 29 Sep 2020 09:50:27 GMT

Hi,

I need to create table as shown in this screenshot:

I have written this search for that:

index=em7_srm_summary_data srm_units_used>0 | dedup ticket_id | sort cust_name, ticket_id | eval "Closed Date" = if (ticket_date_closed!=0, strftime(ticket_date_closed,"%m/%d/%Y"), "") | stats list("ticket_id") as "Ticket Id", list("Closed Date") as "Closed Date" ,list("srm_units_used") as "SR Units Used" by cust_name | addcoltotals "SR Units Used" by cust_name

I need "SRU Units Used" count for each Customer, but I am not able to get the column total for each Customer using above search. Any idea how to do that?

Re: How to group rows together in a table and calculate the sum for each group?

sundareshr — Thu, 02 Jun 2016 13:18:09 GMT

Try this

index=em7_srm_summary_data srm_units_used>0 | dedup ticket_id | sort cust_name, ticket_id | eval "Closed Date" = if (ticket_date_closed!=0, strftime(ticket_date_closed,"%m/%d/%Y"), "") | stats list("ticket_id") as "Ticket Id", list("Closed Date") as "Closed Date" ,list("srm_units_used") as "SR Units Used" by cust_name | appendpipe [|stats sum(srm_units_used) as srm by cust_name | eval cust_name=cust_name." Total"] | sort cust_name

Re: How to group rows together in a table and calculate the sum for each group?

asaste — Thu, 02 Jun 2016 17:58:34 GMT

Thanks a lot for your quick reply.
Yes , your query is giving column total for each Customer. But I am not getting column total exactly below customer name. i am getting it in following order.

Abhi Corp
Abhi Corp MSP
*Abhi Corp MSP Total*
*Abhi Corp Total*
Aniket Corp
*Aniket Corp Total*

Also query is adding column header as 'srm'. Is it possible to have sum in same "SR Units Used" column ?

Re: How to group rows together in a table and calculate the sum for each group?

sundareshr — Thu, 02 Jun 2016 21:12:17 GMT

You can change the column name to be whatever you would like to be by changing the as clause. Like so...

.... | appendpipe [|stats sum(srm_units_used) as "SR Units Used" by cust_name  ] | sort cust_name | eval cust_name=if(isnull("Ticket Id"), cust_name." Total", cust_name))

Re: How to group rows together in a table and calculate the sum for each group?

somesoni2 — Thu, 02 Jun 2016 21:15:56 GMT

Little variation of above to give you proper order

index=em7_srm_summary_data srm_units_used>0 | dedup ticket_id | sort cust_name, ticket_id | eval "Closed Date" = if (ticket_date_closed!=0, strftime(ticket_date_closed,"%m/%d/%Y"), "") | stats list("ticket_id") as "Ticket Id", list("Closed Date") as "Closed Date" ,list("srm_units_used") as "SR Units Used" by cust_name | streamstats count as serialno | appendpipe [|stats max(serialno) sum(srm_units_used) as "SR Units Used" by cust_name | eval cust_name=cust_name." Total"] | sort serialno | fields - serialno

Re: How to group rows together in a table and calculate the sum for each group?

asaste — Mon, 06 Jun 2016 05:25:24 GMT

Thanks somesoni2.
I have tried your query but it is providing following order.
Abhi Corp
Abhi Corp MSP
Aniket Corp
Abhi Corp Total
Abhi Corp MSP Total
Aniket Corp Total

I need following order
Abhi Corp
Abhi Corp Total
Abhi Corp MSP
Abhi Corp MSP Total
Aniket Corp
Aniket Corp Total

Also Total it is giving as 1,2,3 instead of 35, 21, 50 as below

Customer_Name max(serialno)

Abhi Corp Total 1
Abhi Corp MSP Total 2
Aniket Corp Total 3

I am currently modyfying query you have provided to get correct Sum of "SR Units Used"

Thanks,
Abhi

Re: How to group rows together in a table and calculate the sum for each group?

harish1992 — Wed, 15 Nov 2017 16:49:02 GMT

Have you got any answer for this question? Did you get your table results in the above mentioned format?