|
Nom
|
Description
|
SQL
|
|
Comptage des ressources Alibaba Cloud
|
Renvoie le nombre total de ressources auxquelles vous avez accès.
|
-- The COUNT() function is used to query the total number of resources.
-- The resources table stores information about resource properties.
SELECT
COUNT(*)
FROM
resources;
|
|
Comptage des ressources d'instance ECS
|
Renvoie le nombre total d'instances ECS auxquelles vous avez accès.
|
-- 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';
|
|
Requête de toutes les ressources Alibaba Cloud
|
Interroge toutes les ressources auxquelles vous avez accès, triées par type de ressource et ID de ressource.
|
-- 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;
|
|
Requête de toutes les ressources d'instance ECS
|
Interroge toutes les instances ECS auxquelles vous avez accès, triées par nom dans l'ordre croissant.
|
-- 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;
|
|
Requête des 20 premières ressources triées par type de ressource et ID de ressource
|
Interroge les 20 premières ressources, triées par type de ressource et ID de ressource.
|
-- 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;
|
|
Requête des ressources 11 à 30 triées par type de ressource et ID de ressource
|
Trie les ressources par type de ressource et ID de ressource, et renvoie les ressources 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;
|
|
Comptage des ressources par type
|
Compte les ressources par type, triées par quantité dans l'ordre décroissant.
|
-- 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;
|
|
Comptage des ressources par région
|
Compte les ressources par région, triées par quantité dans l'ordre décroissant.
|
-- 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;
|
|
Comptage des ressources par groupe de ressources
|
Compte les ressources par groupe de ressources, triées par quantité dans l'ordre décroissant.
|
-- 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;
|
|
Comptage des ressources par type dans un groupe de ressources spécifié
|
Compte les ressources par type dans un groupe de ressources spécifié, triées par quantité dans l'ordre décroissant.
|
-- 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;
|
|
Requête de tous les types de ressources ECS
|
Liste toutes les ressources ECS, triées par type de ressource et date de création.
|
-- 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;
|
|
Requête des ressources avec un tag spécifié
|
Interroge toutes les ressources avec un tag spécifié.
|
-- You can replace KEY and VALUE as needed.
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';
|
|
Requête des ressources dont les clés de tag contiennent test
|
Liste toutes les ressources dont la clé de tag contient « 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;
|
|
Liste des ressources associées à une adresse IP spécifique
|
Liste toutes les ressources qui incluent une adresse IP spécifiée.
|
-- 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;
|
|
Requête des ressources d'instance ECS créées après une date spécifiée
|
Interroge les instances ECS créées après une date spécifiée.
|
-- 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;
|
|
Requête des ressources créées au cours des 30 derniers jours
|
Interroge les ressources créées au cours des 30 derniers jours, triées par date de création dans l'ordre croissant.
|
-- 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;
|
|
Requête des ressources dans un VPC spécifié
|
Interroge les ressources dans un VPC spécifié, triées par type de ressource dans l'ordre croissant.
|
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;
|
|
Requête des propriétés étendues des instances ECS
|
Interroge les propriétés étendues des instances ECS, y compris l'état, la mémoire, les spécifications et la méthode de facturation.
|
-- 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;
|
|
Comptage des instances ECS par système d'exploitation
|
Compte les instances ECS par système d'exploitation.
|
-- 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';
|
|
Liste des instances ECS arrêtées
|
Liste les instances ECS arrêtées.
|
-- 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;
|
|
Requête des disques attachés aux instances ECS
|
Interroge les disques attachés aux instances 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';
|
|
Requête des disques non attachés
|
Interroge les disques non attachés.
|
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;
|
|
Requête des disques ECS d'une capacité supérieure à 40 Go
|
Interroge les disques ECS supérieurs à 40 Go, triés par capacité.
|
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;
|
|
Requête des instances ApsaraDB RDS en abonnement sur le point d'expirer
|
Interroge les instances ApsaraDB RDS en abonnement sur le point d'expirer.
|
-- 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 as needed.
-- 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;
|