Category Archives: Uncategorized

PDT 2.6 Released

Version 2.6 of the PDT has been released with some updated features.  You can find the blog post for its release here.

When ever Rob releases a new version the first thing I do is update my XLS with the list of variables that are available so I can see what has changed and what new options now have.  Here is a copy of my XLS.

There are 3 tabs one that lists all roles that are found in the workflow.xml, One that lists all the variables that are found in the workflow.xml and one that lists variables that are used in the VMCreator.ps1 file.

Hope it Helps.

Davey

PowerShell to add LocalSystem to Sysadmin group in SQL 2012

Today I was working on a newly deployed Operations Manager system and there were a number of SQL servers that were not getting monitored due to default permissions that are implemented in the SQL management pack.

After a quick discussion with the client it was decided to add add the LocalSystem account back in to the Sysadmin group so it would work closer to the way that SQL 2008 / 2005 did.

Quick hunt around the internet and found some code that was posted David Brabant and thought that this looked like a good starting point.  In the case that I have the account exists and it just needs to be added in to the group.

function SQL-Get-Server-Instance
{
    param (
        [parameter(Mandatory = $true)][string] $DatabaseServer,
        [parameter(Mandatory = $true)][string] $InstanceName
    )

    if (!$InstanceName -or $InstanceName -eq "" -or $InstanceName -eq "MSSQLSERVER")
        { return $DatabaseServer }
    else
        { return "$DatabaseServer\$InstanceName" }
}


 function AddLocalSystemtoSysadmin
 {
     param (
         [parameter(Mandatory = $true)][string] $DatabaseServer,
         [parameter(Mandatory = $false)][string] $InstanceName = "MSSQLSERVER"
     )

    $sqlConnection = $null

    try
     {
         $Error.Clear()

        $ServerInstance = SQL-Get-Server-Instance $DatabaseServer $InstanceName
         $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
         $sqlConnection.ConnectionString = "Server=$ServerInstance;Database=master;Trusted_Connection=True;"

        $Command = New-Object System.Data.SqlClient.SqlCommand
         $Command.CommandType = 1
         $Command.Connection = $sqlConnection
        $Command.CommandText = "ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]"
         $sqlConnection.Open()
         $Command.ExecuteNonQuery() | Out-Null
     }

    catch
     {
         $str = (([string] $Error).Split(':'))[1]
         Write-Error ($str.Replace('"', ''))
     }

    finally
     {
         if ($sqlConnection)
             { $sqlConnection.Close() }
     }
 }

$dbServers = @("db01","db02","db03")
foreach ($Computername in $dbServers){
     Write-host "Updating group on $Computername"
     AddLocalSystemtoSysadmin -DatabaseServer $Computername
} 

Updated ImagePatcher.ps1

A quick post about an update I did a while ago.

There is a great patching script that is hosted on codeplex for patching wim / vhd. I have made some minor updates to it so it can also patch VHDx and will just run with windows 8 and require no additional components.

To patch a VHDX of 2012 R2 and only download what is needed
for that os.

 .\imagepatcher.ps1 -ImageOnly $true -imagefile F:\VHD\WS12R2DG2.vhdx

To Patch only image 1 in the WIM

 .\imagepatcher.ps1 -ImageOnly $true -imagefile F:\VHD\install.wim -patchimages 1

To patch Images 1 and 3 in the WIM

 .\imagepatcher.ps1 -ImageOnly $true -imagefile F:\VHD\install.wim -patchimages "1,3"

To patch all images in a WIM

 .\imagepatcher.ps1 -ImageOnly $true -imagefile F:\VHD\install.wim -patchimages All 

So there is an offline VM servicing tool for OS level
Patches.  There are some for the 2008R2 / Windows 2012 that need to have
the image updated manually as they depend on other patches that have not been
installed yet. 

So best is to run this over your image then power it up and
sysprep it then shut it down and re-run it and all updates should be installed.

I would be expecting to get the following message for each
image / patch run.

image

It is because there are updates that are listed that wont
install due to patch requirements.  (Requires cluster J or DC role etc..)

Updated script can be found here. ImagePatcher.zip

Enable remote access for Event Viewer via PowerShell

Today I had a situation where I wanted to connect using eventvwr to a remote machine.  I had been able to use PowerShell to connect to the remote machine but wanted the Event Viewer Gui for some quick filtering.

So opened up Event Viewer and entered my remote machine name and waited.  After about 30 seconds the following error came back.

image

Ok so by the looks of things we need to enable some firewall rules Smile

First thing is to get a list of the firewall rule groups that are on the remote server.

Get-NetFirewallRule | select displaygroup | Sort-Object displaygroup -Unique

image

Excellent they have not changed the group name.  Now we need to enable all the rules in that group.

Get-NetFirewallRule -DisplayGroup "Remote Event Log Management" | Enable-NetFirewallRule

Ok time to check if things have worked.

image

Perfect we now have remote access to the event log.

Multi Boot Windows update using PSWindowsUpdate

I am often rebuilding my lab environment and finding that I want to run windows update on all of the guest vms in it.  This in its own is not a problem but I don’t want to set up a WSUS server or wait for all the clients to do the normal Windows Update check-in Cycle.

On looking around I found a great PowerShell module on technet (Windows Update PowerShell Module) that seemed like it would be a good starting point.

This module works great but it had a few things missing for what I wanted.

  1. If updates were needed after the first boot you had to manually run the module / script again
  2. If you don’t have Microsoft Update enabled you are not able to patch anything other than core OS updates.
  3. If you don’t have Use Include Recommended there would still be updates that could be missed.

Please don’t misunderstand me there is nothing wrong with the module itself but, for my list of requirements the module would not meet everything that I wanted to do.  So out came some more PowerShell and in a few hours all my requirements have been met.

So first issue was to enable the Microsoft Update module remotely.  This one is the easiest of the list above to fix.  There is a COM object for Microsoft Update that allows you to enable Microsoft Update programmatically.  For me the best way to update this is to just PowerShell remote in to the target machine and update the service.

Invoke-Command -ComputerName $computername -ScriptBlock {
$MicrosoftUpdate = New-Object -ComObject Microsoft.Update.ServiceManager -Strict
$MicrosoftUpdate.AddService2("7971f918-a847-4430-9279-4a52d1efe18d",7,"")
}

So now we can remotely enable Microsoft Update service on any machine that we can remotely execute PowerShell on.  Task two done.

Ok working on the list of tasks the 3rd was the 2nd easiest to get going.  This one took a bit of creative thinking but no big deal.  I was able to use the base of the code above and switching the COM object to Microsoft.Update.AutoUpdate you can enable the includeRecommendedUpdates.

 $WindowsUpdateSettings = (New-Object -com "Microsoft.Update.AutoUpdate").Settings
 $WindowsUpdateSettings.IncludeRecommendedUpdates = $true
 $WindowsUpdateSettings.save()
 (New-Object -com "Microsoft.Update.AutoUpdate").Settings
 

Running this gives an output something like this depending on what your individual settings are

image

Wow I was on a roll here 2 things fixed and at this stage I thought for the AutoUpdate settings let me just put that into an invoke-command to execute on the remote server.

 $computername = "sm03.contoso.com"
 Invoke-Command -ComputerName $computername -ScriptBlock {
 $WindowsUpdateSettings = (New-Object -com "Microsoft.Update.AutoUpdate").Settings
 $WindowsUpdateSettings.IncludeRecommendedUpdates = $true
 $WindowsUpdateSettings.save()
 }
 

This time I got a whole bunch of red hhmmm this works on local machine but not on remote.

image

Time to open up my search engine and try to find out what is going on.  Turns out that while the code is sound it seems that Microsoft has not enabled remote access to the Microsoft.update.autoupdate objects.  (I knew that you could not remotely call windows update but I didn’t think about changing settings having the same issue.)

So the best way to get around this is to enable the setting using a scheduled task.  The good news about doing this is the Invoke-WUInstall command from PSWindowsUpdate already does this so all that we need to do is add this script into the script that Invoke-WUInstall calls.  In my case I wanted to be able to set IncludeRecommended by variable.


if ($IncludeRecommended -eq $true ){
 Write-Host "Enabling Recommended Updates"
 $TempScriptBlock = ""
 $TempScriptBlock = {
 $WindowsUpdateSettings = (New-Object -com "Microsoft.Update.AutoUpdate").Settings
 $WindowsUpdateSettings.IncludeRecommendedUpdates = $true
 $WindowsUpdateSettings.save()
 (New-Object -com "Microsoft.Update.AutoUpdate").Settings | Out-File C:\IncludeRecommended.log
 }
 $script = $TempScriptBlock.ToString() + "`n" + $Script.ToString()
 }
 $tempScriptBlock = ""
$TempScriptBlock = {
ipmo PSWindowsUpdate;
 Get-WUInstall -MicrosoftUpdate -AcceptAll -AutoReboot -verbose  | Out-File C:\PSWindowsUpdate.log
 }
 $script = [ScriptBlock]::Create($Script.ToString() + $TempScriptBlock.ToString())
 Invoke-WUInstall -ComputerName $computername -OnlineUpdate -Script $Script -Confirm:$false -Verbose

Now when we call the Invoke-WUinstall it will create a remote scheduled task on the computer.  One of the commands that it will execute will be to enable Recommended updates.

Ok so two parts of the script are done.  Now for the most fun one.  I want to have the scheduled task run multiple times on the remote machine until either the number of times that I have set have run or all updates are applied.

The first step is to add in a trigger to the job that PSWindowsUpdate created.  As the machines that I am wanting to update could be running anything back to windows 2008 R2 and will most likely have PowerShell 3.0 installed I could only use the scheduler Com components to update the task.

$TaskName = "PSWindowsUpdate"
$Scheduler = New-Object -ComObject Schedule.Service
$Scheduler.Connect()
$rootFolder = $Scheduler.getfolder("\")
$ScheduledTask = $rootFolder.GetTasks(0) | where {$_.name -eq $TaskName}
if ($ScheduledTask -ne $null){
     $TaskDefinition = $ScheduledTask.Definition
     $triggers = @($TaskDefinition.Triggers)
     if ($triggers.count -eq 0){
         Write-host "Add boot Trigger"
         $trigger = $TaskDefinition.Triggers.Create(8)
         $trigger.Enabled = $True
         $trigger.Delay = "PT5M"
         $Rootfolder.RegisterTaskDefinition($Scheduledtask.Name, $Taskdefinition, 4, $null, $null, $null) | out-null
     }
 }
 

So that enabled a boot trigger that waits for 5 minutes after booting before running to allow for network connection and any GPO policy’s. 

Next would be to add in a counter to the registry so we can identify how many times the system has booted.

 [string]$MultiBootReg = "HKLM:\software\PSWindowsUpdate"
 if (!(test-path $MultiBootReg) ) {New-Item -Path $MultiBootReg | out-null; Write-host "Create boot reg key"}
 Set-ItemProperty -Path $MultiBootReg -name "BootNumber" -Value "0" -Force  -Type DWord
 

When putting the counter and enable task together so we can also increment the count of current times the boot has run, you end up with something like this.

[int]$MaxBoots = 2
[string]$MultiBootReg = "HKLM:\software\PSWindowsUpdate"

$Scheduler = New-Object -ComObject Schedule.Service
$Scheduler.Connect("localhost")
$rootFolder = $Scheduler.getfolder("\")
$ScheduledTask = $rootFolder.GetTasks(0) | where {$_.name -eq $TaskName}
if ($ScheduledTask -ne $null){
     $TaskDefinition = $ScheduledTask.Definition
     $triggers = @($TaskDefinition.Triggers)
     if ($triggers.count -eq 0){
         Write-host "Add boot Trigger"
         if (!(test-path $MultiBootReg) ) {New-Item -Path $MultiBootReg | out-null; Write-host "Create boot reg key"}
         Set-ItemProperty -Path $MultiBootReg -name "BootNumber" -Value "0" -Force  -Type DWord
         $trigger = $TaskDefinition.Triggers.Create(8)
         $trigger.Enabled = $True
         $trigger.Delay = "PT5M"
         $Rootfolder.RegisterTaskDefinition($Scheduledtask.Name, $Taskdefinition, 4, $null, $null, $null) | out-null
     } else {
         if (!(test-path $MultiBootReg) ) {
             New-Item -Path $MultiBootReg
             $BootNumber = 0 
         } else {
             $BootNumber = [int](Get-ItemProperty -Path $MultiBootReg -Name "BootNumber").bootnumber
         }
         if ($bootNumber -le $MaxBoots){
             write-host "Current Boot number $BootNumber"
             $nextBoot = $BootNumber+1
             Write-host "Next Boot number $nextboot"
             write-host "Max Boot Number $MaxBoots"
             Set-ItemProperty -Path $MultiBootReg -name "BootNumber" -Value $nextBoot -Force -Type DWord
         }
     }
 }
 

Now we need a way to detect if there are any outstanding patches or if we are ready to remove the task. 

 $numberOfUpdates = @(get-wulist -MicrosoftUpdate)
 

Ok so we now have the number of updates.  We just need to check if we have reached the max number of boots or if the updates are 0 and then remove the trigger if either condition is true.

write-host "Number of updates needed: "$numberofUpdates.count
$numberofUpdates.count | Out-File C:\NumberOfUpdates.log
If (($numberofUpdates.count -eq 0 ) -or ($bootNumber -ge $MaxBoots)){
     Write-host "Remove Remote Trigger"
     Remove-Item -Path $MultiBootReg -Recurse
     $trigger = $TaskDefinition.Triggers.Clear()
     $Rootfolder.RegisterTaskDefinition($Scheduledtask.Name, $Taskdefinition, 4, $null, $null, $null) | out-null
 }
 

Instead of having to manually copy the script out to all of the targeted machines, I wanted to include it in my original script with the ability to write it out to my target machine, if required.  The best way to do that is to use a here string in PowerShell.  When you are doing this you will just need make to sure that if you want to add in variables that you add the ` in front of the $ otherwise PowerShell will put the values for the variables in at run time and not write them out as you may want Smile.  I have only included a small snippet of the script here string showing that I added in some parameters and also checking if the destination folder that the script is to be written in exists.

 if ($bolMultiBoot -eq $true){
     $RemoteMultiRebootScriptPath = ""
     # Remove the drive letter 
     $RemoteMultiRebootScriptPath = $MicrosoftUpdateltiRebootScriptPath.Substring(3)
     $RemoteMultiRebootScriptDriveLetter = $MicrosoftUpdateltiRebootScriptPath.Substring(0,1)
     # Add in server name
     $RemoteMultiRebootScriptPath = "\\$computername\$RemoteMultiRebootScriptDriveLetter`$\$RemoteMultiRebootScriptPath"
     if (!(test-path "$remoteMultiRebootScriptPath")){
         Write-host "Create folder"
         New-Item -ItemType Directory -Path $RemoteMultiRebootScriptPath | Out-Null
     } else {
         Write-host "Remote Folder Exists"
     }
     if (!(test-path "$remoteMultiRebootScriptPath\$MicrosoftUpdateltiRebootScriptName")){
 $FileContents = @"
Param(
     [String]`$MicrosoftUpdateltiBootLogFile = "C:\PSWindowsUpdateMultiBoot.log",
     [string]`$TaskName = "PSWindowsUpdate",
     [bool]`$UseWSUS = `$False,
     [int]`$MaxBoots = 5
     )

write-host "Enabling Update across reboots"
 # register the task to run at first boot.

write-host "Enabling Update across reboots"
 [string]`$MicrosoftUpdateltiBootReg = "HKLM:\software\PSWindowsUpdate"
 @"

}
 

So that was task one done.  At this stage it is now complete.  I have a script that checks and enables Windows Update, Auto Updates, Recommended updates on remote machines allowing them to reboot a number of times and re-run updates until they are fully patched.

The full script can be downloaded here: ApplyWindowsUpdates.zip