All Products
Search
Document Center

Simple Log Service:Use SQL statements to analyze bills

Last Updated:Aug 11, 2023

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.

Cost Manager- Bill - 001

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.

    Cost Manager- Bill - 002
  • 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.Cost Manager- Bill - 004

  • 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
    Cost Manager- Bill - 005