Advanced Queries

Overview

Resource aggregation supports advanced queries. You can use ResourceQL to query configuration states of resources from one or more source accounts.

You can use ResourceQL and the query editor to customize queries for viewing and search for resources.

You can use the query statements preset by Config or customize query statements based on resource configuration attributes to query specific cloud resource configurations.

ResourceQL is a subset of structured query language (SQL) SELECT syntax to help you perform property-based queries and aggregations. The query complexity varies. You can query resources by tag or resource identifier, or by using complex SQL statements. For example, you can query an ECS with a specified OS version.

NOTE:

You can only use advanced queries to query, view, or export cloud resources. If you need to modify or delete resources, go to related service consoles.

Limitations

To prevent a single user from occupying resources for queries for too long, the following constraints are set on advanced queries:

• If the execution duration of a query statement exceeds 15 seconds, a timeout error will be returned.
• If the result set to be returned exceeds the size limit, an error will occur. Make sure that the data volume returned by each statement is within the size limit.
• Up to 4,000 records are returned for a single query.
• A single query statement can be used to perform a maximum of two join queries for tables.
• A maximum of 200 advanced queries can be created for each account.

• Advanced queries of resource aggregators do not support checksum and provisioning_state.

NOTICE:

To get full functionality of advanced queries, you need to enable the resource recorder. The following describes how the resource recorder may affect your use of advanced queries.

• If you have never enabled the resource recorder, no resources can be queried with an advanced query.
• If you have enabled the resource recorder and a monitoring scope is specified, only resources within the monitoring scope can be queried with an advanced query.
• If you enable the resource recorder and disable it after a period of time, only resource data collected during the period when the resource recorder was enabled can be queried with an advanced query.

For details about how to enable and configure the resource recorder, see Configuring the Resource Recorder.

Creating a Query

1. Log in to the management console.
2. Click in the upper left corner. Under Management & Governance, click Config.
3. In the navigation pane on the left, choose Resource Aggregation > Advanced Queries.
4. Choose the Custom Queries tab and click Create Query in the upper right corner.
5. In the Query Range area on the right, select a target aggregator. In the text box below, enter the statement.

   The Schema information used for advanced query is displayed on the left of the page. The properties parameter included in a request should be set to the Schema information which shows the detailed attributes of a cloud service resource. For details about the configuration example of the query statement, see Configuration Examples of Advanced Queries.

6. Click Save Query and enter the query name and description.

   A query name can contain only digits, letters, underscores (_), and hyphens (-). It cannot exceed 64 characters.

7. Click OK.
   Figure 1 Save Query
   
   NOTE:

   There is a limit to how many custom queries you can create. If you exceed this limit, you will receive a notification: "The maximum number of custom queries has been reached." Although the query cannot be saved, you can still run the query and export the results.

8. Click Run and then view the query results. Up to 4,000 query results can be displayed and exported.
9. Click Export above the list and select the format of the file to be exported (CSV or JSON).
10. Click Execution Records to view details about when the query was executed and the query statements.

    You can perform the following operations:

    • Run: running the query
    • Copy: copying the query statements
    • Save: saving the query as a new query
    NOTE:

    After you close the browser window or log out, the execution records of advanced queries will be cleared.

    Figure 2 Execution records
    

Other Operations

• You can modify the name, description, and query statement of a default query or an existing custom query. After you click Save As, a new query is generated. For details, see Using a Predefined Query.
• To view the name, description, and query statements of a query, see Viewing a Query.
• To modify the query statement of a custom query, see Modifying a Custom Query.
• To delete a custom query, see Deleting a Query. Default queries cannot be deleted.
  NOTE:

  To run an advanced query for an aggregator, you must specify this aggregator first.

Configuration Examples of Advanced Queries

Advanced queries use ResourceQL, a subset of SQL SELECT syntax, to query resource configuration data. You do not need to call specific APIs for the query or use multiple APIs to download full data and manually analyze the data. ResourceQL can only query data from the aggregator_resources table.

Example quires are as follows:

• Example 1: Querying the names of stopped ECSs in a resource aggregator

  SELECT domainId, name
  FROM aggregator_resources
  WHERE provider = 'ecs'
  	AND type = 'cloudservers'
  	AND properties.status = 'SHUTOFF'

• Example 2: Querying EVS disks of specified specifications in a resource aggregator

  SELECT *
  FROM aggregator_resources
  WHERE provider = 'evs'
  	AND type = 'volumes'
  	AND properties.size = 100
• Example 3: Fuzzily querying OBS buckets in the resource aggregator

  SELECT *
  FROM aggregator_resources
  WHERE provider = 'obs'
  	AND 'type' = 'buckets'
  	AND name LIKE '%figure%'
• Example 4: Querying the types of resources whose count is greater than 100 under each source account

  WITH counts AS ( 
       SELECT region_id, provider, type, count(*) AS number 
       FROM aggregator_resources
       GROUP BY domain_id, provider, type 
   ) 
   SELECT * 
   FROM counts 
   WHERE number > 100

  For details about query statements, see ResourceQL Syntax.