Simple Log Service:Use the new version of Cost Manager

The new version of Cost Manager stores data in Tablestore to ensure data accuracy and real-time performance. The new version of Cost Manager automatically updates full data every day. This topic describes how to use the new version of Cost Manager.

Syntax description

Basic syntax

The dedicated Tablestore table of the new version of Cost Manager is associated with the dedicated Logstore of Cost Manager as an external table. You can query billing data only by using the external table.

• Search statement: You can specify only an asterisk (*) before the vertical bar (|).

• Analytic statement: The name of the external table is fixed as instance_bill. You must specify all query conditions in a WHERE clause. Example:

* | select xxx from instance_bill where xxx group by xxx limit xxx

The following query statement is executed to query the daily expenses of Simple Log Service:

* |
select
  date_trunc('day', __time__) as day,
  sum(PretaxAmount) as cost
FROM  instance_bill
where
  productcode = 'sls'
group by
  day

Example 1: Query aggregated data

The following query statement is executed to obtain the total expenses of Simple Log Service:

• Query statement

  * |
  select
    sum(PretaxAmount) as cost
  FROM  instance_bill
  where
    productcode = 'sls'

• Query and analysis results

Example 2: Query data by group

The following query statement is executed to obtain the expenses of each service:

• Query statement

  * |
  select
    productcode,
    sum(PretaxAmount) as cost
  FROM  instance_bill
  group by
    productcode

• Query and analysis results

Example 3: Perform comparative analysis

The following query statement is executed to compare the expenses of the current month with the expenses of the previous month:

• Query statement

  * |
  SELECT
    diff [1] AS "Expenses of this month",
    diff [2] AS "Expenses of the previous month",
    diff [3] * 100 -100 as "Increased by (%)"
  FROM (
      SELECT
        compare(amount, 2592000) as diff
      FROM (
          SELECT
            sum(PretaxAmount) AS amount
          FROM instance_bill
        )
    )

• Query and analysis results

Example 4: Analyze data by billable item in a service

The following query statement is executed to query the expenses and usage of each billable item in Simple Log Service:

• Query statement

  * |
  SELECT
    BillingItem,
    sum(PretaxAmount) AS "Expense",
    sum(Usage) as "Usage"
  FROM  instance_bill
  where
    productcode = 'sls'
  GROUP by
    BillingItem

• Query and analysis results

Example 5: Analyze data by instance in a service

The following query statement is executed to query the expenses of each instance in Simple Log Service:

• Query statement

  * |
  SELECT
    InstanceID,
    sum(PretaxAmount) AS "Expense"
  FROM  instance_bill
  where
    productcode = 'sls'
  GROUP by
    InstanceID

• Query and analysis results

Example 6: Use the JOIN clause and an external table to query data

The following query statement is executed to query billing data by joining the instance_bill table with a custom external table.

In this example, a custom external table named instance_name_table is used. The custom table is used to map the instanceName field to the data in the instance_bill table based on the instanceID field.

• Query statement

  * | with t1 as (
    select
      InstanceID,
      sum(PretaxAmount) as PretaxAmount
    FROM    instance_bill
  ),
  t2 as (
    select
      InstanceID,
      InstanceName
    FROM    instance_name_table
  )
  select
    t1.InstanceID,
    t1.PretaxAmount,
    t2.InstanceName
  FROM  t1
    left join t2 on t1.InstanceID = t2.InstanceID

Fields in billing data