This topic describes how to use SQL statements to analyze bills in the Simple Log Service console.
Billing data details
Billing data includes the following two categories:
Billing data of a service. This category of billing data is indicated by the field
source:bill
, as shown in the left section of the following figure. A billing record is generated for a service in every billing cycle.Billing data of an instance. This category of billing data is indicated by the field
source:instance_bill
, as shown in the right section of the following figure. A billing record is generated for an instance in every billing cycle. The billing data includes the information about the usage, attributes such as tag, alias, and name, and costs of the instance.
Examples
The built-in reports in the Cost Manager application are templates for data analysis. To satisfy your various requirements, you can customize SQL statements to analyze bills. Bills of ECS instances are used as an example to show how to use SQL statements for bill analysis.
Query bills that are crucial to your business
To query bills of ECS instances that are crucial to your business in the Logstore named aliyun_bill, enter
source:instance_bill and ProductCode:ECS
in the search box. The following figure shows the query results. For more information about query syntax, see Search syntax.Aggregate bills
Execute the following SQL statement to retrieve the total cost of all ECS instances. On the Graph tab, click Add to New Dashboard. A dedicated dashboard is created for the bills.
source:instance_bill and ProductCode:ECS | select sum(PretaxAmount)
Group bills
Execute the following SQL statement to retrieve the total cost of each ECS instance:
source:instance_bill and ProductCode:ECS | select InstanceID, sum(PretaxAmount) as Amount group by InstanceId order by Amount desc
The bills of ECS instances are queried based on their instance IDs. To query the bills of ECS instances based on other attributes such as region or alias, replace the attribute in the
group by
clause with the target attribute.Perform month-on-month analysis
Calculate the costs of ECS instance for this month and the month-on-month growth rate.
source:bill | select diff[1] as "costs of this month", diff[2] as "costs of the previous month", difference [3]* 100-100 as "month-on-month growth%" from (select compare(amount,604800) as diff from (select sum(PretaxAmount) as amount from log ))
Perform month-on-month analysis based on the product code
source:bill | select ProductCode, diff[1] as "costs of this month", diff[2] as "costs of the previous month", diff[3]* 100-100 as "month-on-month growth%" from (select productcode, compare(amount,604800) as diff from (select ProductCode, sum(PretaxAmount) as amount from log group by ProductCode ) group by productcode)
Categorize bills by tag
You can use tags to categorize bills for multiple services. A tag contains one or more key-value pairs. You can parse a key-value pair of an instance to calculate the costs of the instance.
source: instance_bill and ecs | select k,v , round(sum(PretaxAmount),3) "Costs" from( select split_to_map(Tag,';', '') as tags, pretaxAmount from log where tag <>'' ),unnest(tags) as t(k,v) group by k,v order by "Costs" desc limit 1000