Is it possible to filter API GET calls to Azure SQL

Copper Contributor

I have a need to find all DBs that are NOT within an elastic pool so that i can report on them to maintain costs. Therefore i want to filter my GET call so that it filters out any result set where name eq 'ElasticPool' therefore I try to use name ne 'ElasticPool' and it simply doesn't work.

 

In fact no attempted use of any value in my resultset is being honored with any filtering so i'm wondering if this isn't possible and i'm just wasting my time. 

 

Any help is greatly appreciated. 

 

GET https://management.azure.com/subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.Sql/servers/xx... ne 'ElasticPool'


"sku": { "name": "ElasticPool", "tier": "GeneralPurpose", "capacity": 0 }, "kind": "v12.0,user,vcore,pool", "properties": { "collation": "SQL_Latin1_General_CP1_CI_AS", "maxSizeBytes": 34359738368, "elasticPoolId": "/subscriptions/xxxxxxxx/resourceGroups/xxxxxxx/providers/Microsoft.Sql/servers/xxxxxxx/elasticPools/xxxxxxxxxxx", "status": "Online", "databaseId": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", "creationDate": "2023-05-04T18:49:58.027Z", "currentServiceObjectiveName": "ElasticPool", "requestedServiceObjectiveName": "ElasticPool", "defaultSecondaryLocation": "westus", "catalogCollation": "SQL_Latin1_General_CP1_CI_AS", "zoneRedundant": false, "licenseType": "LicenseIncluded", "maxLogSizeBytes": 193273528320, "earliestRestoreDate": "2023-07-13T16:04:15.8943859Z", "readScale": "Disabled", "currentSku": { "name": "ElasticPool", "tier": "GeneralPurpose", "capacity": 0 }, "currentBackupStorageRedundancy": "Geo", "requestedBackupStorageRedundancy": "Geo", "maintenanceConfigurationId": "/subscriptions/xxxxxxxxxxxxxxxx/providers/Microsoft.Maintenance/publicMaintenanceConfigurations/SQL_Default", "isLedgerOn": false, "isInfraEncryptionEnabled": false }, "location": "eastus", "tags": { "Compliance Scope": "ISO 27001" }, "id": "/subscriptions/xxxxxxx/resourceGroups/xxxxxxxx/providers/Microsoft.Sql/servers/xxxxxxx/databases/xxxxxxx", "name": "xxxxxxxxx", "type": "Microsoft.Sql/servers/databases" },

 

2 Replies
According to the following guidelines, this filtering should be available to me via a REST API GET call.

https://github.com/microsoft/api-guidelines/blob/vNext/azure/Guidelines.md#filter

Has anyone filtered a list operation to Azure SQL succesfully?

I would really appreciate any help or to be told: "yeah dude, this ain't gonna happen man, stop trying to make 'fetch' happen!"

@timalex. Yes, it's possible to do it. You can use the Microsoft Graph REST API POST request:

 

 
In the request body, you can filter really easy for what you want. To only return databases and not elastic pools:
 {
    "subscriptions": [
        "YOURSUBSCRIPTIONID"
    ],
    "query": "Resources | where type in~ (\"microsoft.sql/servers/databases\") | where type !in~ (\"microsoft.sql/servers/elasticpools\")"
}
 
To drill down into a specific resource group vs the whole subscription:
"query": "Resources | where resourceGroup in~ (\"YOURRESOURCEGROUPNAME\") | where type in~ (\"microsoft.sql/servers/databases\") | where type !in~ (\"microsoft.sql/servers/elasticpools\")"
 
See these links for additional info:
 
Hope this helps!