AHB Workbook Incorrect SQL DB information

[arjenhuitema]

🐛 Problem

Elastic pool databases dont have the License Type property on the database itself, but rather on the Elastic Pool, as such they show up incorrectly in the result

👣 Repro steps

1. Create two SQL DB on two separate Elastic pools
2. Enable AHB on one of the two
3. Review the query results. Both database are reported as AHB Enabled.

🤔 Expected

Expect the databases to report their AHB correctly. Also it would be great that the results are divided into 3 categories. Enabled, Disabled and Not Applicable.

📷 Screenshots

Both databases show as enabled:

Pool1 is enabled and Pool 2 is disabled

ℹ️ Additional context

This issue happens because:

1. Elastic pool databases dont have the License Type property on the database itself, but rather on the Elastic Pool
2. The License Type property is passed to a string, in this case the property doesnt exist so it creates an empty string. Later the has statement in the Case looks for the value "LicenseIncluded", since both AHB enabled SQL DBs and Elastics DBs show empty values they are marked as AHB enabled.

Example of how the query could look like:

Resources
| where type =~ 'microsoft.sql/servers/databases'
| project databaseId = id, databaseName = name, elasticPoolId = tolower(tostring(properties.elasticPoolId)), databaseLicenseType = tostring(properties.licenseType), databaseKind = tostring(kind), subscriptionId
| join kind=leftouter (
    Resources
    | where type =~ 'microsoft.sql/servers/elasticpools'
    | project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolLicenseType = tostring(properties.licenseType), elasticPoolKind = tostring(kind))
    on elasticPoolId
| project-away elasticPoolId1
| where databaseName != 'master'
| extend AHUBState = case(
['databaseKind'] !has 'vcore' or ['databaseKind'] has 'serverless', "Not Applicable",
['databaseKind'] has 'vcore' and databaseLicenseType has 'LicenseIncluded' or elasticPoolLicenseType has 'LicenseIncluded', "Not Enabled", "Enabled"
)
| summarize count() by subscriptionId, AHUBState

🙋‍♀️ Ask for the community

We could use your help:

1. Please vote this issue up (👍) to prioritize it.
2. Leave comments to help us solidify the vision.

[github-actions]

Uh oh! @arjenhuitema, the image you shared is missing helpful alt text. Check your issue body.

Alt text is an invisible description that helps screen readers describe images to blind or low-vision users. If you are using markdown to display images, add your alt text inside the brackets of the markdown image.

Learn more about alt text at Basic writing and formatting syntax: images on GitHub Docs.

[arthurclares]

@sebassem Can you please work on this one? @arjenhuitema Thank you for the code!

[naseemhira1196]

Hi, just looking for an update: Were we able to fix this bug?

[sebassem]

Yes this was shipped as part of the November release