Add total at the end of a column in Splunk

[tdunphy_]

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!

[mayurr98 · Accepted solution]

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_]

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!

[mayurr98]

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

[tdunphy_]

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!

[mayurr98]

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

[deepashri_123]

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_]

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

[deepashri_123]

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!!

[tdunphy_]

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?