Intro

Let me present you in this Blog post a useful tool to asset your Azure environment and in a second step show you how to add Azure SQL managed instance in the assessment report.

ARI presentation

Azure Resource Inventory is an open-source project developed in PowerShell that allows you to quickly generate a complete Excel report of your Azure components.

The Excel integrate dynamic pivot tables and graphics…

Main dashboard

… and also, an xml file to import in Draw.io, building a network diagram:

I will let you check the prerequisites, and all command line options in the README.md project page.

How it works?

Graph API

This RESTful API provides a unified programmability model that you can use to access tremendous amount of data in Microsoft Cloud service resources. (More info here)

The ARI main script “AzureResourceInventory.ps1” uses between the lines 438 and 531 the AZ CLI commands that calls the graph API and returns the dataset in an optimized and paginated way (JSON format).

Graph API mechanism

Modules management

The tool comes with several folders and the most interesting one is the “Modules” folder which contains the integration code for each Azure resource category.

Main modules categories

As a DBA, if checking the “Data” folder, we will find several PowerShell scripts for IaaS, PaaS or Saas database managed services.

List of Data modules

At the end, the tool loop through the “Modules” folder and subfolders to find any PowerShell Script, execute them sequentially and merge detailed resources information in the Excel workbook. (Reporting block of “AzureResourceInventory.ps1” line 1068 to 1135).

Tips: If you want to exclude specific resources type from the report, just move the PowerShell scripts or certain folder from the “Modules” folder before launching the script in offline mode (default option, without -Online parameter).

Managed instance integration

As a SQL Server DBA I noticed that Azure SQL Managed Instances was unfortunately missing from the list of Data services-oriented resources 🙁

So, below is the guide to include them.

SQLMI module

Based on the SQLSERVER.ps1 module, I created the SQLMI.ps1 module by defining the resource type to ‘microsoft.sql/managedInstances’

To get the list of resource type available in Graph API, let’s check here

Next you have to retrieve properties related to Managed instances. Complete list of properties here

Complete code below (just save it as .\Modules\Data\SQLMI.ps1)

<#
.Synopsis
Inventory for Azure SQL Server

.DESCRIPTION
This script consolidates information for all microsoft.sql/servers resource provider in $Resources variable. 
Excel Sheet Name: SQL MI

.Link
https://github.com/microsoft/ARI/Modules/Data/SQLSERVER.ps1

.COMPONENT
This powershell Module is part of Azure Resource Inventory (ARI)

.NOTES
Version: 2.3.1
First Release Date: 19th November, 2020
Authors: Claudio Merola and Renato Gregio 

#>

<######## Default Parameters. Don't modify this ########>

param($SCPath, $Sub, $Intag, $Resources, $Task , $File, $SmaResources, $TableStyle, $Unsupported) 

if ($Task -eq 'Processing') {

    $SQLSERVERMI = $Resources | Where-Object { $_.TYPE -eq 'microsoft.sql/managedInstances' }

    if($SQLSERVERMI)
        {
            $tmp = @()

            foreach ($1 in $SQLSERVERMI) {
                $ResUCount = 1
                $sub1 = $SUB | Where-Object { $_.id -eq $1.subscriptionId }
                $data = $1.PROPERTIES
               
                $Tags = if(!!($1.tags.psobject.properties)){$1.tags.psobject.properties}else{'0'}

                $pvteps = if(!($1.privateEndpointConnections)) {[pscustomobject]@{id = 'NONE'}} else {$1.privateEndpointConnections | Select-Object @{Name="id";Expression={$_.id.split("/")[8]}}}

                foreach ($pvtep in $pvteps) {
                    foreach ($Tag in $Tags) {
                        $obj = @{
                            'ID'                    = $1.id;
                            'Subscription'          = $sub1.Name;
                            'Resource Group'        = $1.RESOURCEGROUP;
                            'Name'                  = $1.NAME;
                            'Location'              = $1.LOCATION;
                            'SkuName'               = $1.sku.Name;
                            'SkuCapacity'           = $1.sku.capacity;
                            'SkuTier'               = $1.sku.tier;
                            'Admin Login'           = $data.adminitrators.login;
                            'AzureADOnlyAuthentication'           = $data.adminitrators.azureADOnlyAuthentication;
                            'Private Endpoint'      = $pvtep.id;
                            'FQDN'                  = $data.fullyQualifiedDomainName;
                            'Public Network Access' = $data.publicDataEndpointEnabled;
                            'licenseType'           = $data.licenseType;
                            'managedInstanceCreateMode'               = $data.managedInstanceCreateMode;
                            'Resource U'            = $ResUCount;
                            'Zone Redundant'        = $data.zoneRedundant;
                            'Tag Name'              = [string]$Tag.Name;
                            'Tag Value'             = [string]$Tag.Value
                        }
                        $tmp += $obj
                        if ($ResUCount -eq 1) { $ResUCount = 0 } 
                    }     
                }          
            }
            $tmp
        }
}
else {
    if ($SmaResources.SQLMI) {

        $TableName = ('SQLMITable_'+($SmaResources.SQLMI.id | Select-Object -Unique).count)
        $Style = New-ExcelStyle -HorizontalAlignment Center -AutoSize -NumberFormat 0

        $condtxt = @()
        $condtxt += New-ConditionalText FALSE -Range J:J
        $condtxt += New-ConditionalText FALSO -Range J:J
        $condtxt += New-ConditionalText FAUX -Range J:J
        $condtxt += New-ConditionalText NONE -Range J:J
        $condtxt += New-ConditionalText Enabled -Range L:L
        $condtxt += New-ConditionalText VRAI -Range L:L

        $Exc = New-Object System.Collections.Generic.List[System.Object]
        $Exc.Add('Subscription')
        $Exc.Add('Resource Group')
        $Exc.Add('Name')
        $Exc.Add('Location')
        $Exc.Add('SkuName')
        $Exc.Add('SkuCapacity')
        $Exc.Add('SkuTier')
        $Exc.Add('Admin Login')
        $Exc.Add('ActiveDirectoryOnlyAuthentication')
        $Exc.Add('Private Endpoint')
        $Exc.Add('FQDN')
        $Exc.Add('Public Network Access')
        $Exc.Add('licenseType')
        $Exc.Add('managedInstanceCreateMode')
        $Exc.Add('Zone Redundant')
        if($InTag)
            {
                $Exc.Add('Tag Name')
                $Exc.Add('Tag Value') 
            }

        $ExcelVar = $SmaResources.SQLMI

        $ExcelVar | 
        ForEach-Object { [PSCustomObject]$_ } | Select-Object -Unique $Exc | 
        Export-Excel -Path $File -WorksheetName 'SQL MI' -AutoSize -MaxAutoSizeRows 100 -TableName $TableName -TableStyle $tableStyle -ConditionalText $condtxt -Style $Style

    }
}

SQLMI DBs module

To complete the Azure SQL MI assessment, I also included the Database information for each Managed instance following the same principle as before:

  • Replace GRAPH resource type by ‘microsoft.sql/managedinstances/databases’
  • And fill in the database information properties according to your needs. Complete list of properties here

Complete code below (just save it as .\Modules\Data\SQLMIDB.ps1)

<#
.Synopsis
Inventory for Azure SQL Server

.DESCRIPTION
This script consolidates information for all microsoft.sql/servers resource provider in $Resources variable. 
Excel Sheet Name: SQL MI DBs

.Link
https://github.com/microsoft/ARI/Modules/Data/SQLSERVER.ps1

.COMPONENT
This powershell Module is part of Azure Resource Inventory (ARI)

.NOTES
Version: 2.3.1
First Release Date: 19th November, 2020
Authors: Claudio Merola and Renato Gregio 

#>

<######## Default Parameters. Don't modify this ########>

param($SCPath, $Sub, $Intag, $Resources, $Task , $File, $SmaResources, $TableStyle, $Unsupported) 

if ($Task -eq 'Processing') {

    $SQLSERVERMIDB = $Resources | Where-Object { $_.TYPE -eq 'microsoft.sql/managedinstances/databases' }

    if($SQLSERVERMIDB)
        {
            $tmp = @()

            foreach ($1 in $SQLSERVERMIDB) {
                $ResUCount = 1
                $sub1 = $SUB | Where-Object { $_.id -eq $1.subscriptionId }
                $data = $1.PROPERTIES
               
                $Tags = if(!!($1.tags.psobject.properties)){$1.tags.psobject.properties}else{'0'}

                $pvteps = if(!($data.privateEndpointConnections)) {[pscustomobject]@{id = 'NONE'}} else {$data.privateEndpointConnections | Select-Object @{Name="id";Expression={$_.id.split("/")[10]}}}

                foreach ($pvtep in $pvteps) {
                    foreach ($Tag in $Tags) {
                        $obj = @{
                            'ID'                    = $1.id;
                            'Subscription'          = $sub1.Name;
                            'MI parent'        = $1.id.split("/")[8];
                            'Name'                  = $1.NAME;
                            'Collation'              = $data.collation;
                            'CreationDate'               = $data.creationDate;
                            'DefaultSecondaryLocation'               = $data.defaultSecondaryLocation;
                            'Status'           = $data.status;
                            'Tag Name'              = [string]$Tag.Name;
                            'Tag Value'             = [string]$Tag.Value
                        }
                        $tmp += $obj
                        if ($ResUCount -eq 1) { $ResUCount = 0 } 
                    }     
                }          
            }
            $tmp
        }
}
else {
    if ($SmaResources.SQLMIDB) {

        $TableName = ('SQLMIDBTable_'+($SmaResources.SQLMIDB.id | Select-Object -Unique).count)
        $Style = New-ExcelStyle -HorizontalAlignment Center -AutoSize -NumberFormat 0

        $condtxt = @()
        $condtxt += New-ConditionalText FALSE -Range J:J
        $condtxt += New-ConditionalText FALSO -Range J:J
        $condtxt += New-ConditionalText FAUX -Range J:J
        $condtxt += New-ConditionalText offline -Range G:G

        $Exc = New-Object System.Collections.Generic.List[System.Object]
        $Exc.Add('Subscription')
        $Exc.Add('MI parent')
        $Exc.Add('Name')
        $Exc.Add('Collation')
        $Exc.Add('CreationDate')
        $Exc.Add('DefaultSecondaryLocation')
        $Exc.Add('Status')
        if($InTag)
            {
                $Exc.Add('Tag Name')
                $Exc.Add('Tag Value') 
            }

        $ExcelVar = $SmaResources.SQLMIDB

        $ExcelVar | 
        ForEach-Object { [PSCustomObject]$_ } | Select-Object -Unique $Exc | 
        Export-Excel -Path $File -WorksheetName 'SQL MI DBs' -AutoSize -MaxAutoSizeRows 100 -TableName $TableName -TableStyle $tableStyle -ConditionalText $condtxt -Style $Style

    }
}

Results

After putting the PowerShell files in the right place, you can run the following extraction command:

.\AzureResourceInventory.ps1" -SecurityCenter -TenantID abcdefg1234-4567-1234-abcd-xxxxxxxxxxxx  -Diagram -QuotaUsage

And bingo!! I can see my MI on my subscriptions report

The dashboard references the number of MIs, and the specific sheets contain the details of the instance and the database.
SQL MI sheet
SQL MI DB list

Conclusion

Such tool can be helpful for regular audit and to track the evolution of the configuration and use of Azure resources. In addition, the report not only include the resource state, but also advices, quotas and security threats of your Cloud environment.

  • Pros
  1. Centralized dashboard with all relevant information. Instead of using the Azure Web portal and check the resource-by-resource reports.
  2. Extraction time very fast with GRAPH API.
  3. Import-Excel PowerShell module to allow you generate Excel file without Office installed.
  4. Project still active and regularly maintained.
  • Constraints
  1. Some bugs with the number of inventoried resources that did not correspond to reality.
  2. Network diagram to improve.
  3. Time consuming if you need to personalize the tool (Extras, Modules …)

I also pushed a pull request on the ARI project to integrate the Azure SQL MI module. So, you will have to wait for these modifications to be validated/corrected to be able to use this module in the official version. And who knows? maybe some new features will be released before then.

To be continued…

Post Views: 2,319
Thumbnail [60x60]
by
Microsoft Team