Azure Automation Account Runbook – Update VM tags from SQL Server table

As you can probably see from previous posts, I spend a lot more time these days automating tasks with PowerShell using Azure Automation hybrid workers.

In this post, I wanted to show you a way of reading from a SQL Server database the opening hours of a remote site/office and inputting these times into Azure VMs tags.

This ensures that Azure VMs are available during office hours and also helps to keeps Azure running costs down.

# Get office opening times from SQL database

        [Parameter(Mandatory = $false)]
        [string] $SQLServer = "SQL1.test.local",

        [Parameter(Mandatory = $false)]
        [string] $Database = "Sites",

		[Parameter(Mandatory = $false)]
        [string] $RGbaseName = "RG-WIN10-",

        [Parameter(Mandatory = $false)]
        [string] $AzureSubscriptionName = "azuresub",

		[Parameter(Mandatory = $false)]
        [string] $Filter = "*WIN7*"

$ErrorActionPreference = "Stop"

Next is a simple function that calls a separate runbook that sends an email report on all the output of the last job run of the specified runbook (let me know if you want more details on this):

Function Send-EmailReport
    #Send email report of this runbook job
    $params = @{AutomationRunBookName="OpeningTimesToTags";SendReportByEmail=$true;SendToAddress="",""}
    Start-AzureRmAutomationRunbook -AutomationAccountName "AA-WE-SUB-01" -Name "Report-AutomationJobOutput" -ResourceGroupName "RG-AA-01" –Parameters $params -RunOn "AA-HybridWorker" >$null 2>&1 | out-null

Grab the credentials needed to run the remainder of the script from the AA credentials vault:

# Azure Global Admin
$AzureOrgIdCredential = Get-AutomationPSCredential -Name 'sv-azure-automation'

# Database Read Only account
$DB_RO = Get-AutomationPSCredential -Name 'DB Read Only'

# Login to Azure
Login-AzureRmAccount -Credential $AzureOrgIdCredential >$null 2>&1 | out-null

#Select the specified subscription now
Select-AzureRMSubscription -SubscriptionName $AzureSubscriptionName >$null 2>&1 | out-null

Load the SQLServer PowerShell module or install if not currently. This one is great as we no longer need SSMS:

# SqlServer PoSh Module
If(-not(get-module -ListAvailable -Name SqlServer)){
    write-output "Could not find module 'SqlServer', installing now..."
    Find-Module SqlServer | Install-Module

import-module -Name SqlServer -ErrorAction SilentlyContinue

If(Get-Module -Name SqlServer){
    write-output "Module 'SqlServer' loaded, continue"
} else {
    write-error "Could not load or install 'SqlServer' PoSh Module"

Get all the VMs from Azure where the Resource Group name is like $RGbaseName:

write-output "`nLooping through all RGs like $RGbaseName to find specific VMs"
$RGs = Get-AzureRmResourceGroup | ? ResourceGroupName -like "$RGbaseName*"
If(!$RGs){write-error "Could not find any Resource Groups with a name like $RGbaseName*";return} else
	write-output "Retrieved $($RGs.count) RGs"

Filter the VMs so we are only working with the ones we want, based on $Filter:

write-output "`nGetting filtered VMs from $($RGs.count) RGs using filter $Filter"
$FilteredVMs = $RGs | Get-AzureRmVM | ? Name -like $Filter
If(!$FilteredVMs){write-error "Could not find any VMs with a name like $Filter";return} else
	write-output "Retrieved $($FilteredVMs.count) filtered VMs"

If the days of the week in the database are using a custom format or start the week starts on a different day, we can convert with this simple function:

# 1 = "Sunday"
# 2 = "Monday"
# 3 = "Tuesday"
# 4 = "Wednesday"
# 5 = "Thursday"
# 6 = "Friday"
# 7 = "Saturday"
Function Convert-DBWeekday {
		[Parameter(Mandatory = $true)]
		[string] $DBweekday
	Switch ($DBweekday) {
				1 {
					$OutputDay = "Sunday"

				2 {
					$OutputDay = "Monday"

				3 {
					$OutputDay = "Tuesday"

				4 {
					$OutputDay = "Wednesday"

				5 {
					$OutputDay = "Thursday"

				6 {
					$OutputDay = "Friday"

				7 {
					$OutputDay = "Saturday"


	return $OutputDay


Finally, we are looping through all the filtered VMs to get a site specific code in the VM name – this is how we match the opening times to the office/site location stored in the SQL database.

Once we have the site and opening times, we subtract 60 mins from the opening time and add 30 mins to the close time. This provides a small maintenance window for the VM to complete any updates or SCCM software installs.

Then apply the VM Tags. Schedule_$Weekday
Obviously a separate Automation runbook is required to read the tags and shutdown/startup the VMs at the time specified in the tags.

ForEach($FilteredVM in $FilteredVMs){
	write-output "`nLooping through each filtered VM. Now using: $($FilteredVM.Name)"

	write-output "`nDetermine site code of VM $($FilteredVM.Name)"
	$Site = ($($FilteredVM.Name) -split "-")[2] -replace '[a-zA-Z]',''
	If(($Site.length -eq 3)-and($Site -match "\d{3}")){
		write-output "Site code determined as $Site"
	} else {
		write-error "Site code determined as $Site which does not match 3 digits, quitting"

	# Connect to SQL database and get Site info
	Write-Output "Getting side ID from SQL database for site $Site"
	$Site = Read-SqlTableData -ServerInstance $SQLServer -DatabaseName $Database -Credential $DB_RO -TableName site -SchemaName dbo | ? sitename -like "*$Site"
		Write-Output "Site ID identified as: $($"
	} else {
		write-error "Could not retrieve $Site from the $Database database."

	Write-Output "`nGetting opening hours for site $($Site.sitename)...."
	$DefaultHours = Read-SqlTableData -ServerInstance $SQLServer -DatabaseName $Database -Credential $DB_RO -TableName tblopenhours -SchemaName dbo | ? {$_.site_id -eq $($ -and $_.usertypecode -eq "Default"}


	write-output "`nGetting VMTags from $($FilteredVM.Name)"
	$VMTags = (Get-AzureRmResource -ResourceId ($FilteredVM).Id -ErrorAction Stop).Tags

	ForEach($Day in $DefaultHours){

		$StartDate = [datetime]$($Day.starttime)
		$EndDate = [datetime]$($Day.endtime)
		$StartTime = get-date $StartDate -Format HH:mm
		$EndTime = get-date $EndDate -Format HH:mm
		$WeekDay = Convert-DBWeekday -DBweekday $($Day.weekday)
		write-output "`nOpening hours for $WeekDay are $StartTime - $EndTime"

		write-output "-60 mins from $StartTime and +30 mins to $EndTime"
		$StartTime = get-date (Get-Date $StartTime).AddMinutes(-60) -Format HH:mm
		$EndTime = get-date (Get-Date $EndTime).AddMinutes(30) -Format HH:mm
		$SchedWeekday = "Schedule_$Weekday"
		write-output "Setting tag $SchedWeekday to value: $StartTime->$EndTime"
		$VMTags.$SchedWeekday = "$StartTime->$EndTime"


	write-output "`nWriting VM Tags back to Azure..."
	Set-AzureRmResource -ResourceGroupName $($FilteredVM.ResourceGroupName) -Name $($FilteredVM.Name) -ResourceType "Microsoft.Compute/VirtualMachines" -Tag $VMTags -Force -Confirm:$false


# Send report of job output by email


Modifying Group Policy Preferences Registry collections with PowerShell

Ok guys, loosely sticking with the VDI infrastructure in Azure topic, I needed some automation around Group Policies – seeing as VDI is all about automation and scalability, I didn’t want the solution being hindered by too many manual processes (like maintaining GPO’s for new sites/VMs etc).

The VDI solution is for libraries in a London borough and is accessible to members of the public using their library card to authenticate on Insight Media’s iCAM platform. The thin client’s need to startup and automatically log into an assigned desktop VM in Azure. The VMs are Windows 10 1607 CBB 64-bit.

As we all know, first time login to Windows10/8 is quite slow as it creates the appx packages for the Start Menu, but the main reason for the auto-login is iCAM.

iCAM locks out the Windows desktop until a user enters their library card#, but it first needs to load the explorer shell so that the iCAM app can then be launched. It’s the not greatest app, but that’s for later.

As the Azure VMs are deployed using Azure Automation, I need to create the appropriate Group Policy so that each VM automatically logs into Windows with a specific AD user account.

I have written a PowerShell function that updates Group Policy Preferences XML with Registry preferences.

The function clones an existing Registry collection and edits the properties with username/password/VM name etc. and saves it back to the domain.

This is the screenshot of the Reg collection I’m cloning and modifying: VM-L270-001

Reg GPO Prefs collection

[Parameter(Mandatory = $false)]
[string] $GPOName = "Win10 VDI-Computers",</code>

[Parameter(Mandatory = $false)]
[string] $VMName = "VM-270-002",

[Parameter(Mandatory = $false)]
[string] $UName,

[Parameter(Mandatory = $false)]
[string] $PWord,

[Parameter(Mandatory = $false)]
[string] $TemplateVMName = "VM-270-001"

If(-not(get-module -Name GroupPolicy)){
write-host "Importing GroupPolicy Module"
Import-Module GroupPolicy

$GPO = Get-GPO -Name $GPOName
    $GPOGuid = ($GPO.Id).Guid
    $Domain = $GPO.DomainName
    $GPOPath = "\\$Domain\SYSVOL\$Domain\Policies\{$GPOGuid}\Machine\Preferences\Registry\Registry.xml"
    $Site = ($VMName -split "-")[2] -replace '[a-zA-Z]',''
    $TemplateSite = ($TemplateVMName -split "-")[2] -replace '[a-zA-Z]',''

    #Retrieve XML
    $xml = [xml](Get-Content $GPOPath)

        write-output "XML retrieved from $Domain"
    } else {
        Write-Error "XML could not be retrieved from $Domain"

    # Check if collection for the specified site exists
    $SiteColUserAutoLoginVM = ((((($xml.ChildNodes).Collection | ? Name -EQ "iCAM Client VDI").Collection | ? Name -like "$Site*").Collection | ? Name -Like "User Auto Login").Collection | ? Name -Like $VMName)

        Write-Output "Site collection $Site exists and contains 'User Auto Login' with a sub collection $VMName already"
    } else
        Write-Output "No 'User Auto Login' collection with name $VMName for $Site found. Copying template $TemplateVMName Site collection...."
        # Many arrays in the GPO as it has a lot of collections (folders) for GPPref filtering
        # Get index of the icam client collection
        $ClientCollections = [Collections.Generic.List[Object]]($xml.DocumentElement.Collection)
        $indexclient = $ClientCollections.FindIndex( {$args[0].Name -eq "iCAM Client VDI"} )
        # Get index of the site collection
        $SiteCollections = [Collections.Generic.List[Object]]($xml.DocumentElement.Collection[$indexclient].Collection)
        $indexsite = $SiteCollections.FindIndex( {($args[0].Name -like "$site*")} )
        $indexsiteTemplate = $SiteCollections.FindIndex( {($args[0].Name -like "$TemplateSite*")} )

        # Get template VM collection
        $TemplateSiteVMCols = [Collections.Generic.List[Object]]($xml.DocumentElement.Collection[$indexclient].Collection[$indexsiteTemplate].Collection.Collection)
        $indexVMTemplate = $TemplateSiteVMCols.FindIndex( {($args[0].Name -like "$TemplateVMName")} )

        # Clone the 270 site node 
        $NodeToClone = $xml.RegistrySettings.Collection[$indexclient].Collection[$indexsiteTemplate].Collection.Collection[$indexVMTemplate].Clone()

        # Modify the XML before comitting it
        $ = $VMName
        $NodeToClone.uid = [string]"{$((new-guid).Guid)}".ToUpper()
        $ = $VMName
        $NodeToClone.Registry[0].uid = [string]"{$((new-guid).Guid)}".ToUpper()
        $NodeToClone.Registry[0].Properties.value = $PWord
        $NodeToClone.Registry[1].uid = [string]"{$((new-guid).Guid)}".ToUpper()
        $NodeToClone.Registry[1].Properties.value = $UName
        # Add the clone to the xml
            write-output "Writing cloned template to new site..."
        } else {
            write-error "Please create 'User Auto Login' registry collection under site $site in GPO: $GPOName"


Download all Office 2016 updates with PowerShell and SCCM

Hi Guys,

Sorry for the delay, been working on a big project to rollout thin clients across libraries in a London borough. I will be writing up a collection posts which will make up the automation of a cloud based VDI solution in Azure, starting off today with keeping the Office 2016 MSI/Volume License edition app up to date in SCCM.

When deploying any modern Office version with SCCM, you want to keep it up to date by adding all the latest .msp files to the /updates folder.

I adapted some popular scripts found online  and added what I thought were cool additions.

This one: maintaining-your-office-2016-installation-source involved creating a new SUG and SU Package in SCCM, a bit yucky.

This one: download-office-2016-proplus-x86-updates had a static list that has to be maintained by the script owner and used custom guids to name the files.

Both these issues are ‘resolved’ using the following code.

    • Access to the ConfigManager PSD1 PoSh module. You can load the module in a Admin PoSh prompt like:
        $ConfigMgr_psd1_path = $Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + '\ConfigurationManager.psd1'

        Import-Module $ConfigMgr_psd1_path
  • SCCM Sofware Updates sync configured to download Office 2016 metadata
  • Full Administrator access to SCCM Primary Site Server
$siteserver = "srv-sccm.domain.local"
$sitecode = "AB0"
$class = "SMS_SoftwareUpdate"
$NameSpace = "root\SMS\Site_$sitecode"
$StagingLocation = "T:\O2016_Updates"
$MSPsFolder = "$StagingLocation\ready"</code>

Function ConvertFrom-Cab
    $comObject = "Shell.Application"
    Write-Verbose "Creating $comObject"
    $shell = New-Object -Comobject $comObject
    if(!$?) { $(Throw "unable to create $comObject object")}
    Write-Verbose "Creating source cab object for $cab"
    $sourceCab = $shell.Namespace("$cab").items()
    if(-not (Test-Path $destination))
        Write-Verbose "Creating destination folder object for $destination"
        new-item $destination -ItemType Directory
    $DestinationFolder = $shell.Namespace($destination)
    Write-Verbose "Expanding $cab to $destination"
    ForEach($Cabitem in $sourceCab){
        If($ -like "*.msp"){
            Rename-Item -Path "$destination\$($" -NewName ($fileName)

Remove-Item $StagingLocation -Force -Confirm:$False -Recurse
$Office2016Updates = Get-CMSoftwareUpdate -Name "*Microsoft*2016*32-Bit*" -Fast | ? {($_.IsExpired -ne "False") -and ($_.IsSuperseded -ne "False")}
md $StagingLocation

ForEach($Update in $Office2016Updates){
    $CI_ID = $Update.CI_ID
    $ContentID = (get-wmiobject -ComputerName $siteserver -Query "select * from SMS_CItoContent where ci_id=$CI_ID" -Namespace $NameSpace).ContentID
    $objContent = Get-WmiObject -ComputerName $siteserver -Namespace $NameSpace -Class SMS_CIContentFiles -Filter "ContentID = $ContentID"
    $Filename1 = "KB$((([uri]$Update.LocalizedInformativeURL).AbsolutePath -split "/")[2])"
    $CabFileName = $objContent.Filename
    $FileName2 = ($CabFileName -split ".cab")[0]
    $FileNameWithoutExtension = $FileName2 + "_" + $Filename1
    write-host "FileNameWithoutExtension will be: $FileNameWithoutExtension"
    $URL = $objContent.SourceURL
    write-host "$URL" -ForegroundColor Green
        $FileNamePath = "$StagingLocation\$CabFileName"
        write-host "FileNamePath = $FileNamePath"
        Start-BitsTransfer -Source $URL -Destination $FileNamePath
        If(Test-Path $FileNamePath)
            Write-Output "$FileNamePath found, extracting files from cab..."
            ConvertFrom-Cab -cab $FileNamePath -destination $MSPsFolder -filename $($FileNameWithoutExtension+".msp")
            write-host "Deleting items cabs from $StagingLocation"
            Get-ChildItem -Path $StagingLocation -File -Filter *.cab | Remove-Item -Force -Confirm:$false
            write-host "Deleting items xmls from $MSPsFolder"
            Get-ChildItem -Path $MSPsFolder -File -Filter *.xml | Remove-Item -Force -Confirm:$false
        write-host "stopping here"

In your output folder $MSPsFolder you will have all the MSPs you need to copy into the /updates folder in the Office 2016 app/package source.