When I started to look into this I thought initially there is a simple solution for this surely - Azure 'knows' what we have I just need to extract that. However when you start to get into the detail you start to find there are gaps in the dataset.....
Azure resource graph is the obvious choice to use - but this doesn't have the 'actual' os version info - all it records is the image/sku that the machine as created from - this may be cryptic (or wrong if os version updates have been done) or even null if custom images were used.
The heartbeat from the MMA agent will record some of the data in log analytics (but again not totally complete) - and the MMA agent won't work on all operating systems (NVA's and other appliances for example)
The vminsights agent does record everything for the os's that it works on - but that is even more limited than the MMA agent.
So what to do?
Well the best we seem to be able to do is make use of powershell and specifically the get-azvm command - with the -status switch. This only works specifically against a VM directly - so we have to execute it for every VM (thanks to Marcel from Microsoft for the hint on this).
As long as the machine is running this will return the 'real' os and version for any type of VM (even appliances such as netapp, checkpoint and the like). For us this still represents not full coverage as we have many machines that are only running at certain times of the day or are shut down and only used exceptionally. However a combination of the powershell data and the resource graph data gives us the coverage we need to enable upgrade planning.
I mocked up a powershell script to do all the above, however I wanted to build this in a way that didn't rely on a machine to have to execute the powershell on - this is a waste of a VM and the 'old' way to do things.
I mocked up a powershell script to do all the above, however I wanted to build this in a way that didn't rely on a machine to have to execute the powershell on - this is a waste of a VM and the 'old' way to do things.
The solution - a powershell functionapp - and that's what I'll explain now.
So first up I create a new resource group - I do this up front as we have policies that enforce certain tags have to be in place - this enables our governance on ownership and cost reporting - if I do the RG in line with creating the functionapp i can't specify these.
So create an RG wherever you like
This timeout can be increased up the max allows for the tier you are on - easiest way to do that in the portal is to make use of the app service editor - so click on that as shown below
Set tags if you need to (may not be mandatory in your env)
Wait for that to run (only few secs)
Next we create a functionapp in that resource group I just added - this is a 'bucket' to put our actual code in. You can see I choose specifically powershell as the language I'll be using - quite a few other languages are available now and you could achieve the same thing in all of them - but for me at least powershell is much easier than the other options
Next we choose a few other options , I just accepted default storage account, the os currently has to be windows for powershell (which seemed odd when powershell will run on linux). The final one here is quite important based on the size of your dataset as I found out later on when building this. By default the consumption based one (which is what I wanted to use) has a runtime time limit of 10 minutes - any longer than that and its killed. In our case we have so many machines that the script runtime is around 30 minutes so I had to choose a premium service instead which allows longer timeouts. If your script can run in less than 10 mins then just stick with the cheaper consumption model.
Then we get quick summary and click create.
And wait for completion - again only a few secs.
Now we have the function app container we have to create the actual function where our code will sit. There are a few options on how the code can get executed - you can see in the screenshot below. I chose to use the timer trigger - so the script just executes on a schedule - using standard cron style attributes. The other common choice would be a http trigger - this allows you to call the function via a rest api call - very handy for interfacing to a logic app if you want to build some sort of workflow around this extract process.
Click add and wait a few seconds and then its created.
The first thing i want to do is make use of what of the really nice features in here and that is to enable a managed identity - you can see that in the screenshot below. This creates an app registration in azure active directory for the function itself - what that means is that we can then grant rights specifically to the function itself - no messing around with somehow trying to authenticate as service accounts and having to handle all that - it's just done for you.
After few secs you'll see it created.
To enable the script to be able to read all the details across 'everything' i need to grant it the reader role everywhere - the easiest way to do that is to grant it at the tenant root group level and let it cascade down to everywhere - i do that in this screen. Of course you can do it however it works for you - the function app just needs read rights on whatever you want to report on.
Right we're getting closer - next up is to make the additional powershell modules needed for my script available to the function app. Now from what I read this is meant to be automatic for any modules starting with az. - for me at least this only worked for az.accounts for some reason - so instead I had to add the modules directly to the function app.
Now I'm sure there is more than one way to do this (and maybe a better way) but the method below worked for me.
First up launch the kudu tool from the link shown below from within the function app.
Then go to the console link
Then create the modules directory under site/wwwroot - this is where the module code needs to be stored
Then on your laptop download the modules locally (this wouldn't work in the console above which is annoying). In the case below I end up with 2 directories containing the module files.
Now zip up those directories (screenshot also shows az.accounts but that should be automatic as mentioned above)
Then drag that zip file into the kudu console (making sure you are set to the modules directory)
That will then upload and unzip the files ready to use.
Now we need to go back to the function app main window and go into the code + test window
Now this is the magic powershell script you've all been waiting for - we then paste this in - quick couple of comments on it:
1) I make use of the join-object function - that's from https://github.com/RamblingCookieMonster/PowerShell/blob/master/Join-Object.ps1- this is a nice function that enables you to join 2 powershell arrays in the same way you might join 2 tables in a relational database.
2) I run the get-azvm script to loop through all the subscriptions and store the data in an array
3) I run a resource graph query to get the results to fill in blanks for shut down machines
4) I join the 2 datasets based on a concatenation of vmname/rgname/subscription - to make sure we are not going to get duplicates.
5) I output the result to a storage account - this code needs improving to remove the hardcoded account key - that's possible just a little more fiddly - it's annoying that storage account doesn't just support the identity being passed straight in.
6) note that the resource graph query can be case sensitive in parts hence the use of some conversions - there are also going to be tags you don't have - so remove those
7) first few lines vary depending on the type of function you choose - the below is for the timer one - just grab the code below that if you choose a different type.
# Input bindings are passed in via param block. param($Timer) # Get the current universal time in the default string format. $currentUTCtime = (Get-Date).ToUniversalTime() # The 'IsPastDue' property is 'true' when the current function invocation is later than scheduled. if ($Timer.IsPastDue) { Write-Host "PowerShell timer is running late!" } # Write an information log with the current time. Write-Host "PowerShell timer trigger function ran! TIME: $currentUTCtime" function Join-Object { <# .SYNOPSIS Join data from two sets of objects based on a common value .DESCRIPTION Join data from two sets of objects based on a common value For more details, see the accompanying blog post: http://ramblingcookiemonster.github.io/Join-Object/ For even more details, see the original code and discussions that this borrows from: Dave Wyatt's Join-Object - http://powershell.org/wp/forums/topic/merging-very-large-collections Lucio Silveira's Join-Object - http://blogs.msdn.com/b/powershell/archive/2012/07/13/join-object.aspx .PARAMETER Left 'Left' collection of objects to join. You can use the pipeline for Left. The objects in this collection should be consistent. We look at the properties on the first object for a baseline. .PARAMETER Right 'Right' collection of objects to join. The objects in this collection should be consistent. We look at the properties on the first object for a baseline. .PARAMETER LeftJoinProperty Property on Left collection objects that we match up with RightJoinProperty on the Right collection .PARAMETER RightJoinProperty Property on Right collection objects that we match up with LeftJoinProperty on the Left collection .PARAMETER LeftProperties One or more properties to keep from Left. Default is to keep all Left properties (*). Each property can: - Be a plain property name like "Name" - Contain wildcards like "*" - Be a hashtable like @{Name="Product Name";Expression={$_.Name}}. Name is the output property name Expression is the property value ($_ as the current object) Alternatively, use the Suffix or Prefix parameter to avoid collisions Each property using this hashtable syntax will be excluded from suffixes and prefixes .PARAMETER RightProperties One or more properties to keep from Right. Default is to keep all Right properties (*). Each property can: - Be a plain property name like "Name" - Contain wildcards like "*" - Be a hashtable like @{Name="Product Name";Expression={$_.Name}}. Name is the output property name Expression is the property value ($_ as the current object) Alternatively, use the Suffix or Prefix parameter to avoid collisions Each property using this hashtable syntax will be excluded from suffixes and prefixes .PARAMETER Prefix If specified, prepend Right object property names with this prefix to avoid collisions Example: Property Name = 'Name' Suffix = 'j_' Resulting Joined Property Name = 'j_Name' .PARAMETER Suffix If specified, append Right object property names with this suffix to avoid collisions Example: Property Name = 'Name' Suffix = '_j' Resulting Joined Property Name = 'Name_j' .PARAMETER Type Type of join. Default is AllInLeft. AllInLeft will have all elements from Left at least once in the output, and might appear more than once if the where clause is true for more than one element in right, Left elements with matches in Right are preceded by elements with no matches. SQL equivalent: outer left join (or simply left join) AllInRight is similar to AllInLeft. OnlyIfInBoth will cause all elements from Left to be placed in the output, only if there is at least one match in Right. SQL equivalent: inner join (or simply join) AllInBoth will have all entries in right and left in the output. Specifically, it will have all entries in right with at least one match in left, followed by all entries in Right with no matches in left, followed by all entries in Left with no matches in Right. SQL equivalent: full join .EXAMPLE # #Define some input data. $l = 1..5 | Foreach-Object { [pscustomobject]@{ Name = "jsmith$_" Birthday = (Get-Date).adddays(-1) } } $r = 4..7 | Foreach-Object{ [pscustomobject]@{ Department = "Department $_" Name = "Department $_" Manager = "jsmith$_" } } #We have a name and Birthday for each manager, how do we find their department, using an inner join? Join-Object -Left $l -Right $r -LeftJoinProperty Name -RightJoinProperty Manager -Type OnlyIfInBoth -RightProperties Department # Name Birthday Department # ---- -------- ---------- # jsmith4 4/14/2015 3:27:22 PM Department 4 # jsmith5 4/14/2015 3:27:22 PM Department 5 .EXAMPLE # #Define some input data. $l = 1..5 | Foreach-Object { [pscustomobject]@{ Name = "jsmith$_" Birthday = (Get-Date).adddays(-1) } } $r = 4..7 | Foreach-Object{ [pscustomobject]@{ Department = "Department $_" Name = "Department $_" Manager = "jsmith$_" } } #We have a name and Birthday for each manager, how do we find all related department data, even if there are conflicting properties? $l | Join-Object -Right $r -LeftJoinProperty Name -RightJoinProperty Manager -Type AllInLeft -Prefix j_ # Name Birthday j_Department j_Name j_Manager # ---- -------- ------------ ------ --------- # jsmith1 4/14/2015 3:27:22 PM # jsmith2 4/14/2015 3:27:22 PM # jsmith3 4/14/2015 3:27:22 PM # jsmith4 4/14/2015 3:27:22 PM Department 4 Department 4 jsmith4 # jsmith5 4/14/2015 3:27:22 PM Department 5 Department 5 jsmith5 .EXAMPLE # #Hey! You know how to script right? Can you merge these two CSVs, where Path1's IP is equal to Path2's IP_ADDRESS? #Get CSV data $s1 = Import-CSV $Path1 $s2 = Import-CSV $Path2 #Merge the data, using a full outer join to avoid omitting anything, and export it Join-Object -Left $s1 -Right $s2 -LeftJoinProperty IP_ADDRESS -RightJoinProperty IP -Prefix 'j_' -Type AllInBoth | Export-CSV $MergePath -NoTypeInformation .EXAMPLE # # "Hey Warren, we need to match up SSNs to Active Directory users, and check if they are enabled or not. # I'll e-mail you an unencrypted CSV with all the SSNs from gmail, what could go wrong?" # Import some SSNs. $SSNs = Import-CSV -Path D:\SSNs.csv #Get AD users, and match up by a common value, samaccountname in this case: Get-ADUser -Filter "samaccountname -like 'wframe*'" | Join-Object -LeftJoinProperty samaccountname -Right $SSNs ` -RightJoinProperty samaccountname -RightProperties ssn ` -LeftProperties samaccountname, enabled, objectclass .NOTES This borrows from: Dave Wyatt's Join-Object - http://powershell.org/wp/forums/topic/merging-very-large-collections/ Lucio Silveira's Join-Object - http://blogs.msdn.com/b/powershell/archive/2012/07/13/join-object.aspx Changes: Always display full set of properties Display properties in order (left first, right second) If specified, add suffix or prefix to right object property names to avoid collisions Use a hashtable rather than ordereddictionary (avoid case sensitivity) .LINK http://ramblingcookiemonster.github.io/Join-Object/ .FUNCTIONALITY PowerShell Language #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true, ValueFromPipeLine = $true)] [object[]] $Left, # List to join with $Left [Parameter(Mandatory=$true)] [object[]] $Right, [Parameter(Mandatory = $true)] [string] $LeftJoinProperty, [Parameter(Mandatory = $true)] [string] $RightJoinProperty, [object[]]$LeftProperties = '*', # Properties from $Right we want in the output. # Like LeftProperties, each can be a plain name, wildcard or hashtable. See the LeftProperties comments. [object[]]$RightProperties = '*', [validateset( 'AllInLeft', 'OnlyIfInBoth', 'AllInBoth', 'AllInRight')] [Parameter(Mandatory=$false)] [string]$Type = 'AllInLeft', [string]$Prefix, [string]$Suffix ) Begin { function AddItemProperties($item, $properties, $hash) { if ($null -eq $item) { return } foreach($property in $properties) { $propertyHash = $property -as [hashtable] if($null -ne $propertyHash) { $hashName = $propertyHash["name"] -as [string] $expression = $propertyHash["expression"] -as [scriptblock] $expressionValue = $expression.Invoke($item)[0] $hash[$hashName] = $expressionValue } else { foreach($itemProperty in $item.psobject.Properties) { if ($itemProperty.Name -like $property) { $hash[$itemProperty.Name] = $itemProperty.Value } } } } } function TranslateProperties { [cmdletbinding()] param( [object[]]$Properties, [psobject]$RealObject, [string]$Side) foreach($Prop in $Properties) { $propertyHash = $Prop -as [hashtable] if($null -ne $propertyHash) { $hashName = $propertyHash["name"] -as [string] $expression = $propertyHash["expression"] -as [scriptblock] $ScriptString = $expression.tostring() if($ScriptString -notmatch 'param\(') { Write-Verbose "Property '$HashName'`: Adding param(`$_) to scriptblock '$ScriptString'" $Expression = [ScriptBlock]::Create("param(`$_)`n $ScriptString") } $Output = @{Name =$HashName; Expression = $Expression } Write-Verbose "Found $Side property hash with name $($Output.Name), expression:`n$($Output.Expression | out-string)" $Output } else { foreach($ThisProp in $RealObject.psobject.Properties) { if ($ThisProp.Name -like $Prop) { Write-Verbose "Found $Side property '$($ThisProp.Name)'" $ThisProp.Name } } } } } function WriteJoinObjectOutput($leftItem, $rightItem, $leftProperties, $rightProperties) { $properties = @{} AddItemProperties $leftItem $leftProperties $properties AddItemProperties $rightItem $rightProperties $properties New-Object psobject -Property $properties } #Translate variations on calculated properties. Doing this once shouldn't affect perf too much. foreach($Prop in @($LeftProperties + $RightProperties)) { if($Prop -as [hashtable]) { foreach($variation in ('n','label','l')) { if(-not $Prop.ContainsKey('Name') ) { if($Prop.ContainsKey($variation) ) { $Prop.Add('Name',$Prop[$Variation]) } } } if(-not $Prop.ContainsKey('Name') -or $Prop['Name'] -like $null ) { Throw "Property is missing a name`n. This should be in calculated property format, with a Name and an Expression:`n@{Name='Something';Expression={`$_.Something}}`nAffected property:`n$($Prop | out-string)" } if(-not $Prop.ContainsKey('Expression') ) { if($Prop.ContainsKey('E') ) { $Prop.Add('Expression',$Prop['E']) } } if(-not $Prop.ContainsKey('Expression') -or $Prop['Expression'] -like $null ) { Throw "Property is missing an expression`n. This should be in calculated property format, with a Name and an Expression:`n@{Name='Something';Expression={`$_.Something}}`nAffected property:`n$($Prop | out-string)" } } } $leftHash = @{} $rightHash = @{} # Hashtable keys can't be null; we'll use any old object reference as a placeholder if needed. $nullKey = New-Object psobject $bound = $PSBoundParameters.keys -contains "InputObject" if(-not $bound) { [System.Collections.ArrayList]$LeftData = @() } } Process { #We pull all the data for comparison later, no streaming if($bound) { $LeftData = $Left } Else { foreach($Object in $Left) { [void]$LeftData.add($Object) } } } End { foreach ($item in $Right) { $key = $item.$RightJoinProperty if ($null -eq $key) { $key = $nullKey } $bucket = $rightHash[$key] if ($null -eq $bucket) { $bucket = New-Object System.Collections.ArrayList $rightHash.Add($key, $bucket) } $null = $bucket.Add($item) } foreach ($item in $LeftData) { $key = $item.$LeftJoinProperty if ($null -eq $key) { $key = $nullKey } $bucket = $leftHash[$key] if ($null -eq $bucket) { $bucket = New-Object System.Collections.ArrayList $leftHash.Add($key, $bucket) } $null = $bucket.Add($item) } $LeftProperties = TranslateProperties -Properties $LeftProperties -Side 'Left' -RealObject $LeftData[0] $RightProperties = TranslateProperties -Properties $RightProperties -Side 'Right' -RealObject $Right[0] #I prefer ordered output. Left properties first. [string[]]$AllProps = $LeftProperties #Handle prefixes, suffixes, and building AllProps with Name only $RightProperties = foreach($RightProp in $RightProperties) { if(-not ($RightProp -as [Hashtable])) { Write-Verbose "Transforming property $RightProp to $Prefix$RightProp$Suffix" @{ Name="$Prefix$RightProp$Suffix" Expression=[scriptblock]::create("param(`$_) `$_.'$RightProp'") } $AllProps += "$Prefix$RightProp$Suffix" } else { Write-Verbose "Skipping transformation of calculated property with name $($RightProp.Name), expression:`n$($RightProp.Expression | out-string)" $AllProps += [string]$RightProp["Name"] $RightProp } } $AllProps = $AllProps | Select -Unique Write-Verbose "Combined set of properties: $($AllProps -join ', ')" foreach ( $entry in $leftHash.GetEnumerator() ) { $key = $entry.Key $leftBucket = $entry.Value $rightBucket = $rightHash[$key] if ($null -eq $rightBucket) { if ($Type -eq 'AllInLeft' -or $Type -eq 'AllInBoth') { foreach ($leftItem in $leftBucket) { WriteJoinObjectOutput $leftItem $null $LeftProperties $RightProperties | Select $AllProps } } } else { foreach ($leftItem in $leftBucket) { foreach ($rightItem in $rightBucket) { WriteJoinObjectOutput $leftItem $rightItem $LeftProperties $RightProperties | Select $AllProps } } } } if ($Type -eq 'AllInRight' -or $Type -eq 'AllInBoth') { foreach ($entry in $rightHash.GetEnumerator()) { $key = $entry.Key $rightBucket = $entry.Value $leftBucket = $leftHash[$key] if ($null -eq $leftBucket) { foreach ($rightItem in $rightBucket) { WriteJoinObjectOutput $null $rightItem $LeftProperties $RightProperties | Select $AllProps } } } } } } $RESULT = @() $Subscriptions = Get-AzSubscription foreach ($sub in $Subscriptions) { Get-AzSubscription -SubscriptionName $sub.Name | Set-AzContext $vms = get-azvm foreach ($vm in $vms) { $info = "" | Select-Object v_Name,v_ComputerName,v_OsName,v_OsVersion,v_HyperVGeneration,v_subname ,v_ResourceGroupName,v_joiner $VMINFO = get-azvm -resourcegroupname $vm.ResourceGroupName -name $vm.Name -status $info.v_Name = $VMINFO.Name.ToLower() $info.v_ComputerName = $VMINFO.ComputerName $info.v_OsName = $VMINFO.OsName $info.v_OsVersion = $VMINFO.OsVersion $info.v_HyperVGeneration = $VMINFO.HyperVGeneration $info.v_subname = $sub.Id.ToLower() $info.v_ResourceGroupName = $VMINFO.ResourceGroupName.ToLower() $info.v_joiner = "$($info.v_Name)$($info.v_ResourceGroupName)$($info.v_subname)" $RESULT +=$info } } $RESULT.v_joiner | Ft $Subscriptions $ARG = Search-AzGraph -Query '(Resources |where type == "microsoft.compute/virtualmachines" | project vmname=name,resourceGroup=toupper(resourceGroup),vmsize=properties.hardwareProfile.vmSize,sub=toupper(subscriptionId),os=properties.storageProfile.osDisk.osType,shut=tags.AutoShutdownSchedule,vmid=id,offer=properties.storageProfile.imageReference.offer,sku=properties.storageProfile.imageReference.sku | join kind=inner (ResourceContainers |where type=="microsoft.resources/subscriptions/resourcegroups" |project name=toupper(name),itera=tags.IteraplanID,own=tags.Owner,email=tags.Owner_email,sub2=toupper(subscriptionId) ) on $left.resourceGroup==$right.name and $left.sub==$right.sub2) | join kind=leftouter (ResourceContainers | where type=="microsoft.resources/subscriptions" | project SubName=name,sub=toupper(subscriptionId)) on $left.sub==$right.sub | project vmname=tolower(vmname),resourceGroup=tolower(resourceGroup),sub=tolower(sub),SubName,vmsize,os,itera,shut,own,email,vmid,offer,sku,joiner=strcat(vmname,resourceGroup,sub)' -First 5000 -subscription $Subscriptions.id Join-Object -Left $RESULT -Right $ARG -LeftJoinProperty v_joiner -RightJoinProperty joiner -Type OnlyIfInBoth | Export-Csv "temp.csv" $date = Get-Date -format dd-MM-yyyy $Context = New-AzStorageContext -StorageAccountName "storageaccounthere" -StorageAccountKey "secretkethere" Set-AzStorageBlobContent -Context $Context -Container "azure" -File "temp.csv" -Blob "azurevm$date.csv" -Force
Now when you execute that code it should chug away and spew out a csv file to your storage account on the time schedule you specified. You can always test manually by clicking the test/run option from the menu to trigger it. I scheduled it midday Monday to Friday - that is the time when most VM's would be 'on'.
To refer back to the timeout issue I hinted at earlier - this is how that gets reported in the debug output.
If you then navigate to the host.json file you can set the max timeout value as shown below to the max value allowed for your tier (sensible to just make it an hour or so to avoid a code mistake with an infinite loop running forever)
And there you have it - I was quite pleased - this pulled a lot of techniques together to create what I think is quite a neat solution.
Output looks like the below when you open the csv
Hope you find this useful - any feedback or any other ways of getting complete dataset I'd be interested to hear.
Picture at the top is nothing to do with anything in the article - i just liked it...
Comments
Post a Comment