Splunk Search

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

asaste
Path Finder

Hi,

I need to create table as shown in this screenshot:
alt text
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?

0 Karma

sundareshr
Legend

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

asaste
Path Finder

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 ?

0 Karma

somesoni2
Revered Legend

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
0 Karma

asaste
Path Finder

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

0 Karma

harish1992
New Member

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

0 Karma

sundareshr
Legend

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))
0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...