[Azure]使用Powershell导出所有订阅下的ARM虚拟机的信息

针对脚本中涉及到的知识点说明：

1. 脚本输出结果调用office的excel模块输出到一个excel的sheet中

2. 脚本中包含了通过powershell控制excel的cell格式，例如对齐方式，边框样式等等，行列宽度高度自适应（autofit），以及锁定（freeze）首行首列的方法

3. 由于ARM模式下虚拟机，IP，网卡这些资源都是独立的，互相之间引用，如果拿到每一台虚拟机信息后，再去根据其NIC的Id和PublicIP的ID获取对应的对象，那么整个程序执行效率会大打折扣。所以脚本里面采用在执行前用3次请求把虚拟机，网卡，IP的所有对象获取到本地内存，然后再后面引用的时候使用where条件筛选出需要的来提高执行效率

4. 脚本中其他的代码就不赘述了，根据资源属性得到每一列的值就好了

脚本如下：

Function GetResourceNameFromResourceId($resourceId)
{
    if ($resourceId -ne $null)
    {
        return $resourceId.Substring($resourceId.LastIndexOf('/') + 1);
    }
    return "";
}

Function GetResourcePropertyFromResourceId($resourceId, $propertyName)
{
    if ($resourceId -ne $null)
    {
        $propertyName = $propertyName + "/";
        $rgName = $resourceId.Substring($resourceId.IndexOf($propertyName) + $propertyName.Length);
        return $rgName.Substring(0, $rgName.IndexOf("/"));
    }
    return "";
}

$excel = New-Object -ComObject Excel.Application;
$workbook = $excel.Workbooks.add();
$sheet = $workbook.worksheets.Item(1);
$excel.Visible = $true;
# freeze the first column
$sheet.Application.ActiveWindow.SplitColumn = 1;
$sheet.Application.ActiveWindow.SplitRow = 1;
$sheet.Application.ActiveWindow.FreezePanes = $true;
$currentRow = 2;

$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type];
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type];
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type];
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type];
$VAlign = "microsoft.office.interop.excel.xlVAlign" -as [type];
$HAlign = "microsoft.office.interop.excel.xlHAlign" -as [type];

$titles = "Name", "Status", "Location", "Size", "OS Type", "Resource Group",  "Subscription ID", "Availability Set", "Use Managed Disk", "Data Disk Count",`
          "VNET", "Subnet", "Private IP", "Private IP Allocation Method", "Public IP", "Public IP Allocation Method", "DNS Name";

# set excel styles
for($i = 1; $i -le 17; $i++)
{
    $sheet.cells.item(1,$i).font.bold = $true;
    $sheet.cells.item(1,$i).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic;
    $sheet.cells.item(1,$i).borders.weight = $borderWeight::xlThin;     #xlThick/xlMedium
    $sheet.cells.item(1,$i) = $titles[$i-1];
}
  
# generate excel cells
$subscriptions = Get-AzureRmSubscription;
foreach ($subscription in $subscriptions)
{
    if ($subscription.State -eq "Enabled")
    {
        [void](Select-AzureRmSubscription -SubscriptionId $subscription.SubscriptionId);
        Write-Host ("Querying VM information under subscription {0}" -f $subscription.SubscriptionName);
        
        # query information
        $vms = Get-AzureRmVM -Status -WarningAction Ignore;
        $nics = $null;
        $pips = $null;
        if ($vms.Count -gt 0)
        {
            $nics = Get-AzureRmNetworkInterface;
            $pips = Get-AzureRmPublicIpAddress;
        }

        foreach ($vm in $vms)
        {
            $nicInfo = $vm.NetworkProfile.NetworkInterfaces | where {$_.Primary -eq $true};
            if ($nicInfo -eq $null)
            {
                $nicInfo = $vm.NetworkProfile.NetworkInterfaces[0];
            }
            $nic = $nics | where {$_.Id -eq $nicInfo.Id};
            $primaryIPCfg = $nic.IpConfigurations | where {$_.Primary -eq $true};
            $subnetId = $primaryIPCfg.Subnet.Id;
            $subnetName = GetResourceNameFromResourceId $subnetId;
            $vnetName = GetResourcePropertyFromResourceId $subnetId "virtualNetworks";
        
            $publicIPAddressStr = New-Object System.Text.StringBuilder;
            $publicIPAddressAllocationStr = New-Object System.Text.StringBuilder;
            $privateIPAddressStr = New-Object System.Text.StringBuilder;
            $privateIPAddressAllocationStr = New-Object System.Text.StringBuilder;
            $privateIPAddressDNSStr = New-Object System.Text.StringBuilder;
            foreach ($ipconfig in $nic.IpConfigurations)
            {
                $pipInfo = $ipconfig.PublicIpAddress;
                if ($pipInfo -ne $null)
                {
                    $pip = $pips | where {$_.Id -eq $pipInfo.Id};
                    [void]($publicIPAddressStr.AppendLine($pip.IpAddress));
                    [void]($publicIPAddressAllocationStr.AppendLine($pip.PublicIpAllocationMethod));
                    [void]($privateIPAddressDNSStr.AppendLine($pip.DnsSettings.Fqdn));
                } else {
                    [void]($publicIPAddressStr.AppendLine());
                    [void]($publicIPAddressAllocationStr.AppendLine());
                    [void]($privateIPAddressDNSStr.AppendLine());
                }
                [void]($privateIPAddressStr.AppendLine($ipconfig.PrivateIpAddress));
                [void]($privateIPAddressAllocationStr.AppendLine($ipconfig.PrivateIpAllocationMethod));
            }
        
            $sheet.cells.item($currentRow,1) = $vm.Name;
            $sheet.cells.item($currentRow,2) = $vm.PowerState;
            $sheet.cells.item($currentRow,3) = $vm.Location;
            $sheet.cells.item($currentRow,4) = $vm.HardwareProfile.VmSize;
            $sheet.cells.item($currentRow,5) = $vm.StorageProfile.OsDisk.OsType.ToString();
            $sheet.cells.item($currentRow,6) = $vm.ResourceGroupName;
            $sheet.cells.item($currentRow,7) = $subscription.SubscriptionId;
            $sheet.cells.item($currentRow,8) = (GetResourceNameFromResourceId $vm.AvailabilitySetReference.Id);
            $sheet.cells.item($currentRow,9) = ($vm.StorageProfile.OsDisk.ManagedDisk -ne $null);
            $sheet.cells.item($currentRow,10) = $vm.StorageProfile.DataDisks.Count;
            $sheet.cells.item($currentRow,11) = $vnetName
            $sheet.cells.item($currentRow,12) = $subnetName;
            $sheet.cells.item($currentRow,13) = $privateIPAddressStr.ToString().Trim();
            $sheet.cells.item($currentRow,14) = $privateIPAddressAllocationStr.ToString().Trim();
            $sheet.cells.item($currentRow,15) = $publicIPAddressStr.ToString().Trim();
            $sheet.cells.item($currentRow,16) = $publicIPAddressAllocationStr.ToString().Trim();
            $sheet.cells.item($currentRow,17) = $privateIPAddressDNSStr.ToString().Trim();
            $currentRow++;
        }
    }
}

# auto fit cell sizes
$range = $sheet.usedRange;
$range.VerticalAlignment = $VAlign::xlVAlignTop;
$range.HorizontalAlignment = $HAlign::xlHAlignLeft;
$range.EntireColumn.AutoFit() | out-null;
$range.EntireRow.AutoFit() | out-null;