Using PowerShell to quickly get SQL Server VM in Azure

This time, let's share a script written by ourselves. of course, any script is driven by the original demand. the function of this script is actually very simple. It can help us quickly filter out the VM of SQL Server in the Azure account. The reason for writing this script is also because someone asked, which VM in the environment is SQL Server, and it is very difficult to filter out this information through the Portal of the platform itself, so I wrote a script specially, of course. This script still has some restrictions. It can only filter out virtual machines in Azure VM+SQL License mode. For virtual machines that install SQL Server directly inside VM, because the platform itself does not record such information, it is impossible to filter it out from the platform level.

Here is the content of the script. Share it.

Function Write-DateTimeMessage {param ([parameter (Mandatory = $false)] [switch] $Warning, [parameter (Mandatory = $true)] [string] $Message [parameter (Mandatory = $false)] [string] $ForegroundColor) if ($Warning) {Write-Warning ($(Get-Date-UFormat'% Y/%m/%d% HV% MV% S') + "*" + $Message)} else {if ($ForegroundColor) {Write-Host ($(Get-Date) -UFormat'% Y/%m/%d% HV% MV% S') + "*" + $Message)-ForegroundColor $ForegroundColor} else {Write-Host ($(Get-Date-UFormat'% Y/%m/%d% HV% MV% S') + "*" + $Message)}} [pscustomobject []] $SQLVMObjects = $null$Subscriptions = Get-AzureRmSubscriptionforeach ($subscription in) $Subscriptions) {"Querying subscription:" $SubscriptionID = $Subscription.Id $SubscriptionName = $Subscription.Name Select-AzureRmSubscription-SubscriptionId $SubscriptionID-InformationAction SilentlyContinue Get-AzureRmResourceGroup |% {$RG = $_ Write-DateTimeMessage-Message "Checking ResourceGroup $($RG.ResourceGroupName)" $AzureVMs = Get-AzureRmVM-ResourceGroupName $RG.ResourceGroupName if ($null-ne $AzureVMs) {$AzureVMs | |% {$AzureVM = $_ if ($AzureVM.StorageProfile.ImageReference.Publisher-like "* SQLServer*") {Write-DateTimeMessage-Message "Find SQLServer VM $($AzureVM.Name) in resource group $($RG.ResourceGroupName)"-Warning $SQLVMObject = New-Object-TypeName psobject $SQLVMObject | Add-Member- MemberType NoteProperty-Name SubscriptionName-Value $SubscriptionName $SQLVMObject | Add-Member-MemberType NoteProperty-Name SubscriptionID-Value $SubscriptionID $SQLVMObject | Add-Member-MemberType NoteProperty-Name AzureVMName-Value $AzureVM.Name $SQLVMObject | Add-Member-MemberType NoteProperty-Name ResourceGroupName-Value $AzureVM.ResourceGroupName $SQLVMObject | Add-Member-MemberType NoteProperty-Name Location-Value $AzureVM.Location $SQLVMObjects + = $SQLVMObject }} $OutputPath = Join-Path-Path ([Environment]:: GetFolderPath ("Desktop"))-ChildPath ("SQLVM-" + $(Get-Date-Format "yyyyMMdd-HHmmss") + ".csv") if ($null-ne $SQLVMObjects) {$SQLVMObjects | Export-Csv-NoTypeInformation-LiteralPath $OutputPath Write-DateTimeMessage-Message "Please Check $OutputPath "- Warning} else {Write-DateTimeMessage" Maybe no SQL VM in the environment or didn't get information Please check "- warning}

The method of running is very simple, just run the command directly. Here are some screenshots.

After the run is finished, the information will be exported to the CSV file for easy sorting.

For privacy reasons, detailed information will not be displayed, ha. You can use it as needed.