Resource Management:サポートされているサンプルクエリテンプレート

名前

説明

SQL文

Alibaba Cloudリソースのカウント

権限を持つリソースの総数を照会します。

-- The COUNT() function is used to query the total number of resources. 
-- The resources table stores information about resource properties. 
SELECT
 COUNT(*)
FROM
 resources;

ECSインスタンスリソースのカウント

権限を持つECSインスタンスリソースの総数を照会します。

-- You can replace resource_type = 'ACS::ECS::Instance' with another condition to query the number of resources that meet the condition. 
-- You can click a resource type or resource property displayed in the left-side navigation tree of the Advanced Search page in the Resource Management console. The system creates a query condition and includes the condition in a query statement. 
SELECT
 COUNT(*)
FROM
 resources
WHERE
 resource_type = 'ACS::ECS::Instance';

すべてのAlibaba Cloudリソースの照会

権限を持つすべてのリソースを照会し、リソースタイプとリソースIDでリソースをソートします。

-- The sample code shows all core properties of a resource. You can add or remove a property or configure the properties parameter to specify extended properties of a resource. 
-- The ORDER BY clause is used to specify the rule based on which resources are sorted. You can configure the DESC or ASC parameter to change the order in which resources are sorted. If you do not specify an order, resources are automatically sorted in ascending order. 
-- Sorting data based on resource_type and resource_id can help accelerate queries. 
SELECT
 resource_id,
 resource_name,
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 vpc_id,
 v_switch_id
FROM
 resources
ORDER BY
 resource_type,
 resource_id
LIMIT
 1000 OFFSET 0;

すべてのECSインスタンスリソースの照会

権限を持つすべてのECSインスタンスリソースを照会し、リソースを名前で昇順に並べ替えます。

-- You can change the properties based on which you want to sort resources and the order in which you want to sort resources. If you do not specify an order, resources are automatically sorted in ascending order. 
-- We recommend that you do not use properties that have NULL values for resource sorting. If you use such properties, perform special processing. If you do not perform special processing on such properties, resources cannot be correctly sorted based on the properties. 
SELECT
 resource_id,
 resource_name,
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 vpc_id,
 v_switch_id
FROM
 resources
WHERE
 resource_type = 'ACS::ECS::Instance'
ORDER BY
 resource_name
LIMIT
 1000 OFFSET 0;

リソースタイプとリソースIDでソートされた最初の20個のリソースのクエリ

リソースタイプとリソースIDでソートされた最初の20個のリソースを照会します。

-- The LIMIT condition is used to specify the maximum number of entries that can be returned. The number of entries that can be returned ranges from 1 to 1,000. If you do not configure the LIMIT condition, a maximum of 1,000 entries can be returned by default. 
-- If the values of some properties based on which resources are sorted are duplicate, different results may be returned for multiple queries. In this case, you can add properties for sorting to ensure the consistency of the results. 
SELECT
 resource_id,
 resource_name,
 region_id,
 resource_type,
 account_id
FROM
 resources
ORDER BY
 resource_type,
 resource_id
LIMIT
 20;

リソースタイプとリソースIDでソートされた11〜30番目のリソースのクエリ

リソースタイプとリソースIDでソートされた11 ~ 30番目のリソースを照会します。

-- The LIMIT OFFSET condition is used to limit the scope of the returned result. LIMIT specifies the maximum number of entries that can be returned, and OFFSET specifies the location from which entries start to be returned. 
-- You can use the LIMIT OFFSET condition in paged queries. For each query, you can use LIMIT to limit the maximum number of entries that can be returned and use OFFSET to obtain the next page of entries. To ensure that consecutive entries can be returned, you need to use the ORDER BY clause to specify the sorting method. 
-- If the values of some properties based on which resources are sorted are duplicate, different results may be returned for multiple queries. In this case, you can add properties for sorting to ensure the consistency of the results. 
SELECT
 resource_id,
 resource_name,
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 vpc_id,
 v_switch_id
FROM
 resources
ORDER BY
 resource_type, 
 resource_id
LIMIT
 20 OFFSET 10;

異なるタイプのリソースのカウント

異なるタイプのリソースの数を照会し、リソースを降順の数量でソートします。

-- The GROUP BY clause is used to categorize and group resources based on a specific property. 
-- If the query result contains a string column and a numeric value column, you can view the query result in a chart. 
SELECT
 resource_type,
 COUNT(*) AS cnt
FROM
 resources
GROUP BY
 resource_type
ORDER BY
 cnt DESC;

異なるリージョンでのリソースのカウント

異なるリージョンのリソース数を照会し、降順でリソースをソートします。

-- If the query result contains a string column and a numeric value column, you can view the query result in a chart. 
SELECT
 region_id,
 COUNT(*) AS cnt
FROM
 resources
GROUP BY
 region_id
ORDER BY
 cnt DESC;

異なるリソースグループ内のリソースのカウント

異なるリソースグループ内のリソースの数を照会し、降順で数量ごとにリソースをソートします。

-- If the query result contains a string column and a numeric value column, you can view the query result in a chart. 
SELECT
 CASE
 WHEN resource_group_id IS NULL THEN 'Not connected to resource group'
 ELSE resource_group_id
 END AS resource_group_id,
 COUNT(*) AS cnt
FROM
 resources
GROUP BY
 resource_group_id
ORDER BY
 cnt DESC;

指定されたリソースグループ内の異なるタイプのリソースのカウント

指定したリソースグループ内のすべてのタイプのリソースの数を照会し、リソースを降順に並べ替えます。

-- If the query result contains a string column and a numeric value column, you can view the query result in a chart. 
SELECT
 resource_type,
 COUNT(*) AS cnt
FROM
 resources
WHERE
 resource_group_id = 'rg-xxx'
GROUP BY
 resource_type
ORDER BY
 cnt DESC;

すべてのタイプのECSリソースのクエリ

すべてのタイプのECSリソースを照会し、リソースタイプと作成時間でリソースをソートします。

-- ACS::ECS::% is used to match the resource type code that starts with ACS::ECS::. The query result contains all types of ECS resources. 
-- In the sample code, resources are first sorted by type in ascending order. Then, resources of the same type are sorted by creation time in ascending order. 
SELECT
 resource_id,
 resource_name,
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 vpc_id,
 v_switch_id
FROM
 resources
WHERE
 resource_type LIKE 'ACS::ECS::%'
ORDER BY
 resource_type,
 create_time;

指定されたタグを持つリソースのクエリ

指定されたタグを持つすべてのリソースを照会します。

-- You can replace KEY and VALUE based on your business requirements. 
SELECT
 resource_id,
 resource_name,
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 vpc_id,
 v_switch_id
FROM
 resources
WHERE
 tags ->> 'KEY' = 'VALUE';

タグキーがテストを含むリソースの照会

タグキーにキーワードtestが含まれているすべてのリソースを照会します。

-- In the sample code, a nested query is performed. The inner query is first executed, and the query result is used as a temporary table. The outer query is executed in the temporary table. 
-- The JSONB_OBJECT_KEYS() function is used to expand map-type objects into a collection of keys. The query is performed on the collection. 
-- In the sample code, %test% is used to match all tags whose key contain the keyword test. 
SELECT
 *
FROM
 (
 SELECT
 resource_id,
 resource_name,
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 vpc_id,
 v_switch_id,
 JSONB_OBJECT_KEYS(tags) AS tag_key
 FROM
 resources
 ) AS r
WHERE
 r.tag_key LIKE '%test%'
ORDER BY
 r.resource_type,
 r.resource_id;

指定されたIPアドレスを持つすべてのリソースのクエリ

指定されたIPアドレスを持つすべてのリソースを照会します。

-- The JSONB_ARRAY_ELEMENTS_TEXT() function is used to expand an array into a collection of text values. The query is performed on the collection. 
SELECT
 *
FROM
 (
 SELECT
 resource_id,
 resource_name,
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 vpc_id,
 v_switch_id,
 JSONB_ARRAY_ELEMENTS_TEXT(ip_addresses) AS ip
 FROM
 resources
 ) AS r
WHERE
 r.ip = 'xxx.xxx.xxx.xxx'
ORDER BY
 r.resource_type,
 r.resource_id;

指定日以降に作成されたECSインスタンスリソースのクエリ

指定した日付以降に作成されたECSインスタンスリソースを照会します。

-- The AND operator is used to connect multiple query conditions. Only resources that meet all query conditions can be returned.
SELECT
 resource_id,
 resource_name,
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 vpc_id,
 v_switch_id
FROM
 resources
WHERE
 resource_type = 'ACS::ECS::Instance'
 AND create_time > '2023-08-07'
ORDER BY
 create_time;

過去30日以内に作成されたリソースのクエリ

過去30日以内に作成されたリソースを照会し、リソースを作成時刻ごとに昇順で並べ替えます。

-- You can specify a calculation formula in the query condition to perform an exact query. 
SELECT
 resource_id,
 resource_name,
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 vpc_id,
 v_switch_id
FROM
 resources
WHERE
 create_time > TO_CHAR(NOW() - interval '30 day', 'YYYY-MM-DD')
ORDER BY
 create_time;

指定されたVPCのリソースのクエリ

指定されたVPCにデプロイされたリソースを照会し、昇順でタイプごとにリソースを並べ替えます。

SELECT
 resource_id,
 resource_name,
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 vpc_id,
 v_switch_id
FROM
 resources
WHERE
 vpc_id = 'vpc-xxx'
ORDER BY
 resource_type,
 resource_id;

ECSインスタンスの拡張プロパティのクエリ

ステータス、メモリ、仕様、課金方法など、ECSインスタンスの拡張プロパティを照会します。

-- You can specify the properties field for resources of different types to query the extended properties of the resources. 
-- You can click a resource type or resource property displayed in the left-side navigation tree of the Advanced Search page in the Resource Management console. The system creates a query condition and includes the condition in a query statement. 
-- The CASE clause is used to define different values for the same property, which helps you easily understand the query result. 
SELECT
 resource_id,
 resource_name,
 properties ->> 'Status' AS "Instance status",
 properties ->> 'InstanceNetworkType' AS "Network type",
 properties ->> 'Memory' AS "Memory",
 properties ->> 'Cpu' AS Cpu,
 properties ->> 'InstanceType' AS "Specifications",
 CASE
 properties ->> 'InstanceChargeType'
 WHEN 'PrePaid' THEN 'Subscription'
 WHEN 'PostPaid' THEN 'Pay-as--you-go'
 ELSE properties ->> 'InstanceChargeType'
 END AS "Billing method",
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 ip_addresses
FROM
 resources
WHERE
 resource_type = 'ACS::ECS::Instance'
ORDER BY
 resource_id;

オペレーティングシステム別に分類されたECSインスタンスのカウント

オペレーティングシステムごとに分類されたECSインスタンスの数を照会します。

-- If the query result contains a string column and a numeric value column, you can view the query result in a chart. 
SELECT
 properties ->> 'OSType' AS OSType,
 COUNT(*) AS num
FROM
 resources
WHERE
 resource_type = 'ACS::ECS::Instance'
GROUP BY
 properties ->> 'OSType';

停止したECSインスタンスリソースの照会

停止したECSインスタンスリソースを照会します。

-- The CASE clause is used to define different values for the same property, which helps you easily understand the query result. 
-- The CONCAT() function is used to combine multiple properties into one column, which helps you easily view the query result. In the sample code, forward slashes (/) are used as delimiters. 
SELECT
 resource_id,
 resource_name,
 CONCAT(region_id, '/', zone_id) AS "Region/Zone",
 CASE
 properties ->> 'Status'
 WHEN 'Pending' THEN 'Creating'
 WHEN 'Running' THEN 'Running'
 WHEN 'Starting' THEN 'Starting'
 WHEN 'Stopping' THEN 'Stopping'
 WHEN 'Stopped' THEN 'Stopped'
 END AS "Status",
 CONCAT(
 properties ->> 'Cpu',
 ' vCPU ',
 (properties -> 'Memory') :: int / 1024,
 ' GiB ',
 properties ->> 'InstanceType'
 ) AS "Configuration",
 properties ->> 'InstanceChargeType' AS "Billing method",
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 properties
FROM
 resources
WHERE
 resource_type = 'ACS::ECS::Instance'
 AND properties ->> 'Status' = 'Stopped'
ORDER BY
 resource_id;

ECSインスタンスにアタッチされたディスクのクエリ

ECSインスタンスにアタッチされたディスクを照会します。

-- The JOIN keyword is specified to perform a joint query on multiple tables. The query result is returned based on the join conditions and filter conditions. 
SELECT
 a.resource_type AS resource_type_disk,
 b.resource_type AS resource_type_instance,
 a.resource_id AS disk_id,
 a.region_id AS instance_region_id,
 b.resource_id AS instance_id,
 b.region_id AS disk_region_id
FROM
 resources a
 LEFT JOIN resources b ON a.properties ->> 'InstanceId' = b.resource_id
WHERE
 a.resource_type = 'ACS::ECS::Disk'
 AND b.resource_type = 'ACS::ECS::Instance';

未接続ディスクの照会

未接続ディスクを照会します。

SELECT
 resource_id,
 resource_name,
 properties ->> 'Status' AS "Disk status",
 CASE
 properties ->> 'Type'
 WHEN 'system' THEN 'System disk'
 WHEN 'data' THEN 'Data disk'
 ELSE properties ->> 'Type'
 END AS "Disk type",
 properties ->> 'DiskChargeType' AS "Billing method of a disk",
 CASE
 properties ->> 'Portable'
 WHEN 'true' THEN 'Supported'
 ELSE 'Not supported'
 END AS "Detachable",
 CASE
 properties ->> 'DeleteWithInstance'
 WHEN 'true' THEN 'Disk released with instance, but automatic snapshot not deleted with disk'
 ELSE 'Disk not released with instance, and automatic snapshot not deleted with disk'
 END AS "Release mode",
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags
FROM
 resources
WHERE
 properties ->> 'Status' = 'Available'
 AND resource_type = 'ACS::ECS::Disk'
ORDER BY
 resource_id;

容量が40 GBを超えるECSディスクのクエリ

容量が40 GBを超えるECSディスクを照会し、容量ごとにディスクをソートします。

SELECT
 resource_id,
 resource_name,
 region_id,
 zone_id,
 resource_type,
 account_id,
 create_time,
 resource_group_id,
 tags,
 ip_addresses,
 vpc_id,
 v_switch_id,
 (properties ->> 'Size') :: int AS disk_size
FROM
 resources
WHERE
 resource_type = 'ACS::ECS::Disk'
 AND (properties ->> 'Size') :: int > 40
order by
 disk_size;

期限切れになる予定のサブスクリプションApsaraDB RDSインスタンスのクエリ

期限切れになる予定のサブスクリプションApsaraDB RDSインスタンスを照会します。

-- The sample code defines that the resources that will expire in less than 30 days are the resources that are about to expire. You can change the condition based on your business requirements. 
-- The TO_TIMESTAMP() function is used to convert time strings into formatted timestamps for related computing. 
SELECT
 *
FROM
 (
 SELECT
 resource_id,
 resource_name,
 account_id,
 resource_type,
 region_id,
 zone_id,
 create_time,
 to_timestamp(
 (properties ->> 'ExpireTime') :: varchar,
 'YYYY-MM-DD HH24:MI:SS'
 ) AS "Expiration time",
 properties ->> 'DBInstanceStatus' AS "Instance status",
 properties ->> 'DBInstanceType' AS "Instance type",
 CONCAT(
 properties ->> 'Engine',
 ' ',
 properties ->> 'EngineVersion'
 ) AS "Database type",
 vpc_id,
 tags
 FROM
 resources
 WHERE
 resource_type = 'ACS::RDS::DBInstance'
 and properties ->> 'PayType' = 'Prepaid'
 order by
 create_time
 ) AS t
WHERE
 t."Expiration time" < NOW() + interval '30 day'
ORDER BY
 t.resource_id;