Advanced Queries

Overview

Resource aggregation supports advanced queries. You can use ResourceQL to query configuration states of one or multiple aggregator accounts.

You can create custom queries using Query Editor.

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.

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 a long time, note the following restrictions:

• If the execution duration of a query statement exceeds15 seconds, a timeout error will be returned.
• If a query generates a large amount of data and an error is returned, you need to simplify the query statement.
• Only the first 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.

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 is 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 left navigation, choose Resource Aggregation > Advanced Queries.
4. Choose the Custom Queries tab and click New Query in the upper right corner.
5. On the Query Range area on the right, select the aggregator whose resource configuration needs to be queried. In the text box below, enter the query 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.

   The query name can contain only digits, letters, underscores (_), and hyphens (-).

7. Click OK.
   Figure 1 Save Query
   
   

   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." You will still be able to run custom queries and export the results, but no more custom queries can be saved.

8. Click Run and then view the query results. Only the first 4000 query results can be displayed and saved.
9. Click Export and select the format of the file to be exported (CSV or JSON).

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 Saving a 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 Query.
• To delete a custom query, see Deleting a Query. Default queries cannot be deleted.
  

  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.