Splunk Search

Add total at the end of a column in Splunk

tdunphy_
Explorer

Hello,

I have a splunk query that goes into our AWS bill and outputs totals for various AWS resources:

index=prd_aws_billing (source="/*2017-09.csv") LinkedAccountId="123456789"  ProductName="Amazon Elastic Compute Cloud" user_Engagement="" | stats sum(UnBlendedCost) AS Cost by ResourceId,UsageType,user_Name,user_Engagement |rename ResourceId as "AWS Resource Name" UsageType as "Usage Type" user_Name as "Resource Name" user_Engagement as Engagement | fieldformat Cost="$".tostring(Cost)| eval Cost=round(Cost,2)

How can I get a total of JUST the Cost column at the end of the column? So at the bottom of the Cost column I want to have the total of that column.

Thanks!

0 Karma
1 Solution

mayurr98
Super Champion

I think you want something like this. Also you need to rearrange your query a bit to get the desired output.

index=prd_aws_billing (source="/*2017-09.csv") LinkedAccountId="123456789" ProductName="Amazon Elastic Compute Cloud" user_Engagement="" 
| stats sum(UnBlendedCost) AS Cost by ResourceId,UsageType,user_Name,user_Engagement 
| rename ResourceId as "AWS Resource Name" UsageType as "Usage Type" user_Name as "Resource Name" user_Engagement as Engagement 
| addcoltotals Cost label="Total Cost" 
| eval Cost=round(Cost,2) 
| fieldformat Cost="$".tostring(Cost)

let me know if this helps!

View solution in original post

mayurr98
Super Champion

I think you want something like this. Also you need to rearrange your query a bit to get the desired output.

index=prd_aws_billing (source="/*2017-09.csv") LinkedAccountId="123456789" ProductName="Amazon Elastic Compute Cloud" user_Engagement="" 
| stats sum(UnBlendedCost) AS Cost by ResourceId,UsageType,user_Name,user_Engagement 
| rename ResourceId as "AWS Resource Name" UsageType as "Usage Type" user_Name as "Resource Name" user_Engagement as Engagement 
| addcoltotals Cost label="Total Cost" 
| eval Cost=round(Cost,2) 
| fieldformat Cost="$".tostring(Cost)

let me know if this helps!

tdunphy_
Explorer

Hey, thanks! I tried your exact query. Only substituting the actual account number for '123456789'. And for some reason the "Total Cost" label isn't applied. It's still showing a total for the column, but with no label.

It's nice to have that. But nicer with the label! Here's my exact query again just so you can see!

index=prd_aws_billing (source="/*2017-10.csv") LinkedAccountId="1234567890" ProductName="Amazon Elastic Compute Cloud"  
 | stats sum(UnBlendedCost) AS Cost by ResourceId,UsageType,user_Name,user_Engagement 
 | rename ResourceId as "AWS Resource Name" UsageType as "Usage Type" user_Name as "Resource Name" user_Engagement as Engagement 
 | addcoltotals Cost label="Total Cost" 
 | eval Cost=round(Cost,2) 
 | fieldformat Cost="$".tostring(Cost)

Any ideas? Thanks again!

0 Karma

mayurr98
Super Champion

Hey you would need to assign labelfield=

Try
| addcoltotals Cost label="Total Cost" labelfield=Engagement

You can give any column name as labelfield.

Refer this for more info
https://docs.splunk.com/Documentation/Splunk/7.0.2/SearchReference/Addcoltotals

0 Karma

tdunphy_
Explorer

Thanks! That worked perfectly. Sorry I didn't see that earlier!

However what I don't understand is why we're chosing labelfield=Engagement. If we leave that out, this doesn't work. And the label doesn't get applied. But I don't know why. Would you please explain that part?

Thanks!

0 Karma

mayurr98
Super Champion

Well, it will not work just by removing labelfield you need to remove label as well. both works in conjunction.

labelfield
    Syntax: labelfield=<fieldname>
    Description: Specify a field name to add to the result set.
    Default: none


label
    Syntax: label=<string>
    Description: Used with the labelfield argument to add a label in the summary event. If the labelfield argument is absent, the label argument has no effect.
    Default: Total

Look at this doc for more info
http://docs.splunk.com/Documentation/Splunk/7.0.3/SearchReference/Addcoltotals

0 Karma

deepashri_123
Motivator

Hey tdunphy_,

You can add this to your query:

index=prd_aws_billing (source="/*2017-09.csv") LinkedAccountId="123456789"  ProductName="Amazon Elastic Compute Cloud" user_Engagement="" | stats sum(UnBlendedCost) AS Cost by ResourceId,UsageType,user_Name,user_Engagement |rename ResourceId as "AWS Resource Name" UsageType as "Usage Type" user_Name as "Resource Name" user_Engagement as Engagement | fieldformat Cost="$".tostring(Cost)| eval Cost=round(Cost,2) | addcoltotals Cost  label="Total Cost"

Let me know if this helps!!

tdunphy_
Explorer

Hey that's great! Thank you. That does just what I want.

However when I do it, the label="Total Cost" isn't getting applied. It would be really nice to have that!

Here's my query as it is now:

index=prd_aws_billing (source="/*2017-09.csv") LinkedAccountId="832839043616" UsageType="BoxUsage:*" user_Engagement="" ProductName="Amazon Elastic Compute Cloud" | stats sum(UnBlendedCost) AS Cost by ResourceId,UsageType,user_Name,user_Engagement |rename ResourceId as "AWS Resource Name" UsageType as "Usage Type" user_Name as "Resource Name" user_Engagement as Engagement | fieldformat Cost="$".tostring(Cost)| eval Cost=round(Cost,2) | addcoltotals Cost label="Total Cost"

That produces a total at the bottom of the Cost column. However the "Total Cost" label isn't there. Any idea how we can correct that?

Thanks

0 Karma

deepashri_123
Motivator

You can add labelfield

To create seperate column labelfield for total
|addcoltotals Cost labelfield=Total label="Total Cost"

If you want to add "Total" field in other existing Column then add this:
| addcoltotals Cost labelfield= Engagement label="Total Cost"

Please accept the answer if this helped for future reference!!

0 Karma

tdunphy_
Explorer

Ok thanks! What I want is the second option. To add a "Total" field in the existing Cost column.

If I try adding the line you gave me at the end of the query, I get this at the very bottom of the output:

29846226482482    $16288.86

I think what that's doing is adding up the Engagements field. That's not what I want. What I want that to read is "Total" $16288.86". How can I do that?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...