I am tinkering with Graph Explorer in Azure but can’t get a solution for what I’m looking for, which is to return a list of VMs on which SQL Server has been installed. Ultimately, I would like to get the SQL Server version and CU info but one step at a time.
The following query returns results but definitely an incomplete list. Any suggestions?
<code>resources
| where type == "microsoft.sqlvirtualmachine/sqlvirtualmachines"
| project id, name, resourceGroup, location, version = properties.sqlServerLicenseType, subscriptionId = tolower(subscriptionId)
| join kind=inner (
resourcecontainers
| where type == "microsoft.resources/subscriptions"
| extend subscriptionId = tolower(extract("subscriptions/([^/]+)", 1, id))
| project subscriptionId, subscriptionName = name
) on subscriptionId
| project subscriptionName, name, resourceGroup, location, version
</code>
<code>resources
| where type == "microsoft.sqlvirtualmachine/sqlvirtualmachines"
| project id, name, resourceGroup, location, version = properties.sqlServerLicenseType, subscriptionId = tolower(subscriptionId)
| join kind=inner (
resourcecontainers
| where type == "microsoft.resources/subscriptions"
| extend subscriptionId = tolower(extract("subscriptions/([^/]+)", 1, id))
| project subscriptionId, subscriptionName = name
) on subscriptionId
| project subscriptionName, name, resourceGroup, location, version
</code>
resources
| where type == "microsoft.sqlvirtualmachine/sqlvirtualmachines"
| project id, name, resourceGroup, location, version = properties.sqlServerLicenseType, subscriptionId = tolower(subscriptionId)
| join kind=inner (
resourcecontainers
| where type == "microsoft.resources/subscriptions"
| extend subscriptionId = tolower(extract("subscriptions/([^/]+)", 1, id))
| project subscriptionId, subscriptionName = name
) on subscriptionId
| project subscriptionName, name, resourceGroup, location, version
Tried various Graph Explorer queries