{"id":20100,"date":"2022-10-25T09:53:09","date_gmt":"2022-10-25T07:53:09","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=20100"},"modified":"2024-09-10T15:46:58","modified_gmt":"2024-09-10T13:46:58","slug":"azure-resource-inventory-managed-instance-integration","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/azure-resource-inventory-managed-instance-integration\/","title":{"rendered":"Azure Resource Inventory: Managed instance integration"},"content":{"rendered":"\n

Intro<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

ARI presentation<\/h2>\n\n\n\n

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

The Excel integrate dynamic pivot tables and graphics…<\/p>\n\n\n\n

\"\"
Main dashboard<\/figcaption><\/figure>\n\n\n\n

… and also, an xml file to import in Draw.io<\/a>, building a network diagram:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

I will let you check the prerequisites, and all command line options in the README.md <\/a>project page.<\/p>\n\n\n\n

How it works?<\/h2>\n\n\n\n

Graph API<\/h3>\n\n\n\n

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<\/a>)<\/p>\n\n\n\n

The ARI main script “AzureResourceInventory.ps1”<\/strong> 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).<\/p>\n\n\n\n

\"\"
Graph API mechanism<\/figcaption><\/figure>\n\n\n\n

Modules management<\/h3>\n\n\n\n

The tool comes with several folders and the most interesting one is the “Modules”<\/strong> folder which contains the integration code for each Azure resource category.<\/p>\n\n\n\n

\"\"
Main modules categories<\/figcaption><\/figure>\n\n\n\n

As a DBA, if checking the “Data”<\/strong> folder, we will find several PowerShell scripts for IaaS, PaaS or Saas database managed services.<\/p>\n\n\n\n

\"\"
List of Data modules<\/figcaption><\/figure>\n\n\n\n

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”<\/strong> line 1068 to 1135).<\/p>\n\n\n\n

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

Managed instance integration<\/h2>\n\n\n\n

As a SQL Server DBA I noticed that Azure SQL Managed Instances<\/a> was unfortunately missing from the list of Data services-oriented resources \ud83d\ude41<\/p>\n\n\n\n

So, below is the guide to include them.<\/p>\n\n\n\n

SQLMI module<\/h3>\n\n\n\n

Based on the SQLSERVER.ps1<\/strong> module, I created the SQLMI.ps1 <\/strong>module by defining the resource type to ‘microsoft.sql\/managedInstances’<\/strong><\/em><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To get the list of resource type available in Graph API, let’s check here<\/a><\/p>\n\n\n\n

Next you have to retrieve properties related to Managed instances. Complete list of properties here<\/a><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Complete code below <\/code>(just save it as .\\Modules\\Data\\SQLMI.ps1<\/strong>)<\/p>\n\n\n

\n<#\n.Synopsis\nInventory for Azure SQL Server\n\n.DESCRIPTION\nThis script consolidates information for all microsoft.sql\/servers resource provider in $Resources variable. \nExcel Sheet Name: SQL MI\n\n.Link\nhttps:\/\/github.com\/microsoft\/ARI\/Modules\/Data\/SQLSERVER.ps1\n\n.COMPONENT\nThis powershell Module is part of Azure Resource Inventory (ARI)\n\n.NOTES\nVersion: 2.3.1\nFirst Release Date: 19th November, 2020\nAuthors: Claudio Merola and Renato Gregio \n\n#>\n\n<######## Default Parameters. Don't modify this ########>\n\nparam($SCPath, $Sub, $Intag, $Resources, $Task , $File, $SmaResources, $TableStyle, $Unsupported) \n\nif ($Task -eq 'Processing') {\n\n    $SQLSERVERMI = $Resources | Where-Object { $_.TYPE -eq 'microsoft.sql\/managedInstances' }\n\n    if($SQLSERVERMI)\n        {\n            $tmp = @()\n\n            foreach ($1 in $SQLSERVERMI) {\n                $ResUCount = 1\n                $sub1 = $SUB | Where-Object { $_.id -eq $1.subscriptionId }\n                $data = $1.PROPERTIES\n               \n                $Tags = if(!!($1.tags.psobject.properties)){$1.tags.psobject.properties}else{'0'}\n\n                $pvteps = if(!($1.privateEndpointConnections)) {[pscustomobject]@{id = 'NONE'}} else {$1.privateEndpointConnections | Select-Object @{Name="id";Expression={$_.id.split("\/")[8]}}}\n\n                foreach ($pvtep in $pvteps) {\n                    foreach ($Tag in $Tags) {\n                        $obj = @{\n                            'ID'                    = $1.id;\n                            'Subscription'          = $sub1.Name;\n                            'Resource Group'        = $1.RESOURCEGROUP;\n                            'Name'                  = $1.NAME;\n                            'Location'              = $1.LOCATION;\n                            'SkuName'               = $1.sku.Name;\n                            'SkuCapacity'           = $1.sku.capacity;\n                            'SkuTier'               = $1.sku.tier;\n                            'Admin Login'           = $data.adminitrators.login;\n                            'AzureADOnlyAuthentication'           = $data.adminitrators.azureADOnlyAuthentication;\n                            'Private Endpoint'      = $pvtep.id;\n                            'FQDN'                  = $data.fullyQualifiedDomainName;\n                            'Public Network Access' = $data.publicDataEndpointEnabled;\n                            'licenseType'           = $data.licenseType;\n                            'managedInstanceCreateMode'               = $data.managedInstanceCreateMode;\n                            'Resource U'            = $ResUCount;\n                            'Zone Redundant'        = $data.zoneRedundant;\n                            'Tag Name'              = [string]$Tag.Name;\n                            'Tag Value'             = [string]$Tag.Value\n                        }\n                        $tmp += $obj\n                        if ($ResUCount -eq 1) { $ResUCount = 0 } \n                    }     \n                }          \n            }\n            $tmp\n        }\n}\nelse {\n    if ($SmaResources.SQLMI) {\n\n        $TableName = ('SQLMITable_'+($SmaResources.SQLMI.id | Select-Object -Unique).count)\n        $Style = New-ExcelStyle -HorizontalAlignment Center -AutoSize -NumberFormat 0\n\n        $condtxt = @()\n        $condtxt += New-ConditionalText FALSE -Range J:J\n        $condtxt += New-ConditionalText FALSO -Range J:J\n        $condtxt += New-ConditionalText FAUX -Range J:J\n        $condtxt += New-ConditionalText NONE -Range J:J\n        $condtxt += New-ConditionalText Enabled -Range L:L\n        $condtxt += New-ConditionalText VRAI -Range L:L\n\n        $Exc = New-Object System.Collections.Generic.List[System.Object]\n        $Exc.Add('Subscription')\n        $Exc.Add('Resource Group')\n        $Exc.Add('Name')\n        $Exc.Add('Location')\n        $Exc.Add('SkuName')\n        $Exc.Add('SkuCapacity')\n        $Exc.Add('SkuTier')\n        $Exc.Add('Admin Login')\n        $Exc.Add('ActiveDirectoryOnlyAuthentication')\n        $Exc.Add('Private Endpoint')\n        $Exc.Add('FQDN')\n        $Exc.Add('Public Network Access')\n        $Exc.Add('licenseType')\n        $Exc.Add('managedInstanceCreateMode')\n        $Exc.Add('Zone Redundant')\n        if($InTag)\n            {\n                $Exc.Add('Tag Name')\n                $Exc.Add('Tag Value') \n            }\n\n        $ExcelVar = $SmaResources.SQLMI\n\n        $ExcelVar | \n        ForEach-Object { [PSCustomObject]$_ } | Select-Object -Unique $Exc | \n        Export-Excel -Path $File -WorksheetName 'SQL MI' -AutoSize -MaxAutoSizeRows 100 -TableName $TableName -TableStyle $tableStyle -ConditionalText $condtxt -Style $Style\n\n    }\n}\n<\/pre><\/div>\n\n\n
SQLMI DBs module<\/h3>\n\n\n\n
To complete the Azure SQL MI assessment, I also included the Database information for each Managed instance following the same principle as before:<\/p>\n\n\n\n