REST API call does not list SQL resources on Azure subscription

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

The Symptoms

Recently I came to a support issue where we tried to use REST API to list SQL resources on an Azure subscription. The output returned results, but it did not show any of the SQL resources that we expected to see. Filtering the result with a GREP only brought up a storage account that had "SQL" in its name, but none of the servers or databases.

 

These are the commands that were used:

 

az rest -m get -u 'https://management.azure.com/subscriptions/11111111-2222-3333-4444-555555555555/resources?api-version=2020-06-01'

 

GET https://management.azure.com/subscriptions/11111111-2222-3333-4444-555555555555/resources?api-version=2020-06-01

The Troubleshooting

The Azure portal showed the full list of Azure SQL servers and databases, for either drilling down through the subscription or going directly to the "SQL servers" or "SQL databases" blades. Commands like az sql db list or az sql server list also returned all SQL resources. Permission issues were excluded by using an owner account for subscription and resources. And it turned out that only one specific subscription was affected, whereas it worked fine for all other subscription.

 

The Cause

Some list operations divide the result into separate pages when too much data is returned and the results are too large to return in one response. A typical size limit is when the list operation returns more than 1,000 items.

In this specific case, the subscription contained so many resources that the SQL resources didn't make it onto the first result page. It required using the URL provided by the nextLink property to switch to the second page of the resultset.

 

The Solution

When using list operations, a best practice is to check the nextLink property on the response. When nextLink isn't present in the results, the returned results are complete. When nextLink contains a URL, the returned results are just part of the total result set. You need to skip through the pages until you either find the resource you are looking for, or have reached the last page.

 

The response with a nextLink field looks like this:

 

 

{   "value": [     <returned-items>   ],   "nextLink": "https://management.azure.com:24582/subscriptions/11111111-2222-3333-4444-555555555555/resources?%24expand=createdTime%2cchangedTime%2cprovisioningState&%24skiptoken=eyJuZXh0UG...<token details>...MUJGIn0%3d" }

 

 

This URL can be used in the "-u" parameter (or --uri/--url) of the REST client, e.g. in the az rest command.

 

Further Information

 

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.