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