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.

SQL Backup to URL (Azure Blob) behind authenticated proxy server

If you want to use SQL 2012 or above to backup directly to a URL and your servers are behind a corporate proxy that also requires auth, create the following .net config file so that the sql service will make the connection correctly:

c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\BackuptoURL.exe.config

Add the following code with your proxy address to the new file:

<?xml version ="1.0"?>
                                    <proxy  proxyaddress="http://proxylb.domain.local:8080" bypassonlocal="true" usesystemdefault="True" />
                                                <add address="[a-z]+.domain.local$" />

Detecting and dropping orphaned SQL database logins

Sometimes it might be useful to find all SQL and Windows logins inside all databases within an  instance of SQL that do not have associated logins. If the database is not in Contained mode, then instance level logins will probably be used to access the database.

This scripts finds all orphaned users in all databases in the instance and then drops those users accounts from the respective database. Note, use with caution and perform a database backup before testing.

RowID int not null primary key identity(1,1) ,

USE Test_Database
END) [LOGIN TYPE] FROM sys.sysusers
SID NOT IN (SELECT SID FROM sys.syslogins) AND sys.sysusers .name != 'dbo'


Declare @SQL as varchar (200)
Declare @DDBName varchar (100)
Declare @Orphanname varchar (100)
Declare @DBSysSchema varchar (100)
Declare @From int
Declare @To int
Select @From = 0, @To = @@ROWCOUNT
--Print @From
--Print @To
While @From < @To
Set @From = @From + 1

Select @DDBName = DBNAME, @Orphanname = USERNAME from ##ORPHANUSER
Where RowID = @From

Set @DBSysSchema = '[' + @DDBName + ']' + '.[sys].[schemas]'
print @DBsysSchema
Print @DDBname
Print @Orphanname
set @SQL = 'If Exists (Select * from ' + @DBSysSchema
+ ' where name = ''' + @Orphanname + ''')
Use ' + @DDBName
+ ' Drop Schema [' + @Orphanname + ']
print @SQL
Exec (@SQL)

Begin Try
Set @SQL = 'Use ' + @DDBName
+ ' Drop User [' + @Orphanname + ']'
Exec (@SQL)
End Try
Begin Catch
End Catch



Custom configuration of RD Web Pages


Customising the look and feel of Remote Desktop Web Access pages can provide the following:

  • Custom links
  • Branding
  • Intuitive interface
  • Reduced clutter

The RD Web pages are stored on each of the RD Web Access servers in the system drive location:

  • C:\Windows\Web\RDWeb

Custom Windows 10 help link on the Login page

#Set your RD Web servers
$RDBrokerWebGatewayServer1 = "rds-001"
$RDBrokerWebGatewayServer2 = "rds-002"

#Change to local help instead of out-dated 2008 R2 RDS help online
Invoke-Command -ComputerName $RDBrokerWebGatewayServer1,$RDBrokerWebGatewayServer2 -ScriptBlock {Set-WebConfigurationProperty -pspath 'MACHINE/WEBROOT/APPHOST/Default Web Site/RDWeb/Pages' -filter "appSettings/add[@key='LocalHelp']" -name "value" -value "true"}

Make the highlighted updates:


<td class="cellSecondaryNavigationBar" height="40">
 <xsl:comment>Login Page only contains Help link</xsl:comment>
<table border="0" cellpadding="0" cellspacing="0" class="linkSecondaryNavigiationBarHelp">
      <a id='PORTAL_HELP' href="javascript:onClickHelp()">
       <xsl:value-of select="$strings[@id = 'HelpLogin']"/>
<td width="30"></td>

Add a new section:

.linkSecondaryNavigiationBarHelp a
  color: #C00000;
  text-decoration: none;
  font-size: 14px;
  font-weight: bold;

Add a new section:

<string id="HelpLogin">Using Windows 10? Click here for Help</string>



Changing the default rap-help.htm page

Edit section:

	<li id=WINDOWS10_1607><a href="#UserTopic_Windows_1607">Windows 10 1607 Remote Desktop fix</a></li>
	<li id=REMOTE_APP_DESKTOP_CONNECTIONS><a href="#UserTopic_What_is_RemoteApp_and_Desktop_Connections">What is RemoteApp and Desktop Connections?</a></li>
	<li id=REMOTE_PROGRAMS><a href="#UserTopic_What_are_Remote_Programs">What is RemoteApp?</a></li>
	<li id=STARTING_REMOTE_PROGRAMS><a href="#UserTopic_Starting_a_RemoteProgram">Starting a RemoteApp program</a></li>
	<li id=STARTING_REMOTE_DESKTOP_TAB><a href="#UserTopic_What_is_the_Remote_Desktop_tab">What is the Remote Desktop tab?</a></li>
	<li id=PUBLIC_PRIVATE_MODE><a href="#UserTopic_Public_Private_Mode">Public vs. private computer settings</a></li>
	<li id=COMPUTER_REQ><a href="#UserTopic_Computer_requirements">Computer requirements</a></li>
	<li id=CONTROL><a href="#UserTopic_Get_ActiveX">I am prompted to run the Remote Desktop Services ActiveX Client control. How do I do that?</a></li>

Add a new section:


<h3><a name="UserTopic_Windows_1607"><id id=WINDOWS10_16072>Windows 10 1607 Remote Desktop fix</id></a></h3>
There is a known issue with Windows 10 version 1607.
    Type 'Winver' in start menu and press return to see your Windows 10 version.

Add the following registry value which you can do without administrator rights:

HKEY_CURRENT_USER\Software\Microsoft\Terminal Server Client
Name: RDGClientTransport
Type: Dword
Data: 1

For more information about this issue, see
    <a href="" target="_blank"></a>.

<a href="#_TopOfPage">Back to topics</a>