Automated Patching for Amazon Workspaces via SCCM and Powershell – Day 1

Do you have hundreds of Amazon Workspaces to patch monthly? and you are so tired of manually power them on to patch, and only reboot at a certain time? Here are my PowerShell scripts to automate all these issues. It fits our EC2 patching as well ( since our boss want those machines to be turned off when not in use )

Amazon Workspace Patching Script – Day 1 – Scan & Patching

 Get initial patching status report and email it to the stakeholder;

 Turn on all Amazon Workspaces;

 List all Workspace machines inside SCCM and Amazon;

 Compare the list and install SCCM agent on machines needed;

 Remove any dead Workspaces in SCCM and disabled them in AD;

 Create a new list of machines for patching and start them!;

 Inject workspace-ID into our AD( so in future we can power on Amazon workspace individually for our second-day patching & remediation)

# Automated SCCM AWS Workspaces Patching
# Rui Qiu
# 2/26/2019
# Verion 1.0

# Set up SQL Server address and where statement
$sqlInstance="name_of_your_sql_instance"
$sqlDatabase="name_of_sql"
$qry_broken = "(dbo.v_AssignmentState_Combined.StateID <> 1 AND dbo.v_AssignmentState_Combined.StateID <> 5) AND (dbo.v_CIAssignment.AssignmentName = N'name_of_your_SCCM_CI_AssignmentName')"
$qry_all = "  (dbo.v_CIAssignment.AssignmentName = N'name_of_your_SCCM_CI_AssignmentName')"

# Set up Envrionment for AWS
Set-AWSCredentials -StoredCredentials your_stored_credential_name
Set-DefaultAWSRegion your_aws_region

# Set up SQL Query Function
function SQLResult {
param ([string]$qry_where)
$qry=@"
    SELECT        TOP (100) PERCENT dbo.v_R_System.Name0 AS Name, dbo.v_R_System.operatingSystem0 AS OS, dbo.v_CIAssignment.AssignmentName, dbo.v_AssignmentState_Combined.StateType, 
                         dbo.v_AssignmentState_Combined.StateID, dbo.v_StateNames.StateName
    FROM            dbo.v_AssignmentState_Combined INNER JOIN
                         dbo.v_R_System ON dbo.v_AssignmentState_Combined.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                         dbo.v_CIAssignment ON dbo.v_AssignmentState_Combined.AssignmentID = dbo.v_CIAssignment.AssignmentID INNER JOIN
                         dbo.v_StateNames ON dbo.v_AssignmentState_Combined.StateType = dbo.v_StateNames.TopicType AND dbo.v_AssignmentState_Combined.StateID = dbo.v_StateNames.StateID
    WHERE        $qry_where
    ORDER BY dbo.v_StateNames.StateName
"@
$Remediation = @(Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -Query $qry)
$Remediation
}

# Set up Email Function
$style = "<style>BODY{font-family: Arial; font-size: 10pt;}"
$style = $style + "TABLE{border: 1px solid black; border-collapse: collapse;}"
$style = $style + "TH{border: 1px solid black; background: #dddddd; padding: 5px; }"
$style = $style + "TD{border: 1px solid black; padding: 5px; }"
$style = $style + "</style>"


function Email {
  param ( [string]$sub, [string]$content)
  $username = "myself"
  $password = ConvertTo-SecureString "password" -AsPlainText -Force
  $cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $password
  Send-MailMessage -From your_email -To your_email -Cc your_email -Subject "$sub"  -Credential $cred -Body "$content" -BodyAsHtml -SmtpServer name_of_your_smtp_server
}



#0.Email initial patching status
$Remediation = SQLResult -qry_where $qry_all
$Result = ($Remediation | Select Name, OS, AssignmentName, StateType, StateID, StateName | ConvertTo-Html -Head $style | Out-String)
Email -sub "SCCM AWS Workspace Automated Patching Report 1/4 - Initial Patching Status" -content "$Result"


#1.Turn on all AWS worksplaces and wait for 15 min
Get-WKSWorkspace -DirectoryId your_aws_directory_id | where {$_.State -eq 'Stopped'} | Start-WKSWorkspace
Start-Sleep -s 900

# List All Workspace Machines in AD
# $PC_AD = Get-ADComputer -SearchBase "OU=your_OU" -Filter * -Properties * | FT Name 

#2.List All Workspaces inside SCCM 
Import-Module "C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1"
Set-Location -path "$(Get-PSDrive -PSProvider CMSite):\" -verbose
$PC_SCCM = Get-CMDevice -CollectionID AMH00070 | Select-Object Name, ClientType

#3. List All AWS Workspaces
$AWS_FULL =  Get-WKSworkspace -DirectoryId your_aws_directoryId
$PC_AWS = $AWS_FULL| Select-Object ComputerName 

#4. Find out any AWS Workspaces are NOT in SCCM
$PC_NOSCCM = New-Object System.Collections.ArrayList
Foreach ($PC in $PC_AWS.ComputerName) {
 if($PC -notin $PC_SCCM.Name) {$PC_NOSCCM=$PC + "`n" + $PC_NOSCCM}}


#5. Remove Any Workspaces Not in AWS from SCCM & AD
$PC_NOAWS = New-Object System.Collections.ArrayList
Foreach ($PC in $PC_SCCM.Name) {
  if($PC -notin $PC_AWS.ComputerName) {
      $PC_NOAWS.Add($PC)    
      Get-ADComputer -Identity "$PC" | Move-ADObject -TargetPath 'OU=your_OU'
      Get-ADComputer -Identity "$PC" | Disable-ADAccount
      Remove-CMDevice -DeviceName "$PC" -Force
      }
}


#6. Generate a new list of machines
$PC_SCCM_YES = @()
$PC_SCCM_NO = @()
$PC_SCCM = Get-CMDevice -CollectionID AMH00070 | Select-Object Name, ClientType
foreach ($PC in $PC_SCCM) {
  if($pc.ClientType -eq "1") {$PC_SCCM_YES += $pc.Name}
  else {$PC_SCCM_NO += $pc.Name}
}

#7. Install any machines without SCCM agent from that list
foreach($PC in $PC_SCCM_NO ){ 
Install-CMClient -DeviceName "$PC" -SiteCode "AMH" -AlwaysInstallClient $True -IncludeDomainController $True
}  


# A few other ways to install SCCM client remotely
# Old PSEXEC CMD can be:
# .\pstools\PSExec.exe -S \\server_name cmd /c \\server_name\client\ccmsetup.exe

#foreach($PC in $PC_NOSCCM) {
#$Target = "\\$PC\c$\temp"
#if (!(Test-Path -path $Target)) {New-Item $Target -Type Directory}
#Copy-Item \\server_name\Client\ccmsetup.exe -Destination "$target\ccmsetup.exe" -Force
#Invoke-Command –ComputerName $PC –ScriptBlock {Start-Process C:\temp\ccmsetup.exe}
#} 


#8. For rest machines, start patching

foreach($PC in $PC_SCCM_YES ){ 
Write-host $PC
$Client = [wmiclass] "\\$PC\root\ccm:SMS_Client"
# Machine Policy Retrival
$Client.TriggerSchedule("{00000000-0000-0000-0000-000000000021}")
# Machine Policy Evaluation
$Client.TriggerSchedule("{00000000-0000-0000-0000-000000000022}")
# Software Update Scan Cycle 
$Client.TriggerSchedule("{00000000-0000-0000-0000-000000000113}")
# Software Updates Assignments Evaluation Cycle 
$Client.TriggerSchedule("{00000000-0000-0000-0000-000000000108}")
}


#9. At the same time, inject workspace ID with machine names in AD

#Find out computers without WorkspaceID in AD
$NeedData = Get-ADComputer -SearchBase "OU = your_OU" -Filter * -Properties * | where {!$_."Workspac
e-ID"}  | Select-Object Name
foreach($PC in $NeedData.Name) {
$ID = $AWS_FULL | ? {$_.ComputerName -eq "$PC"} | % { $_.WorkspaceID } | Out-String
Set-ADComputer -Identity $PC -Replace @{'Workspace-ID'="$ID"}
}



#10. Email initial report
if ([string]::IsNullOrWhiteSpace($PC_NOSCCM)) {$PC_NOSCCM = "Sucess! Our system haven't found any AWS Workspaces not in the SCCM database"}
if (!$PC_NOAWS) {$PC_NOAWS = "Sucess! Our system haven't found any non-exist AWS Workspaces still in AD & SCCM"}  
if (!$PC_SCCM_NO) {$PC_SCCM_NO = "Sucess! Our system haven't found any AWS Workspaces need SCCM client installation"}  
if (!$NeedData) {$NeedData = "Sucess! All Workspace-ID has been injected inside AD"}  


Email -sub "SCCM AWS Workspace Automated Patching Report 2/4 - Maitenance Job Status" -content "
<b>1. AWS Workspaces Not in SCCM:</b><br />
$PC_NOSCCM
<br />
<br />
<b>2. Devices removed in SCCM and Disabled in AD:</b><br />
$PC_NOAWS
<br />
<br />
<b>3. Devices need to inject Workspace-ID:</b><br />
$NeedData
<br />
<br />
<b>4. Devices need SCCM client Installation:</b><br />
$PC_SCCM_NO


" 


#11. Wait 80min each time re-initiate SCCM policy update ( to keep AWS workspaces machine alive and the patching alive)
$i = 8
while ( $i -gt 0 ) {

# Get PC with update issues
$Remediation = SQLResult -qry_where $qry_broken
$Result = ($Remediation | Select Name, OS, AssignmentName, StateType, StateID, StateName | ConvertTo-Html -Head $style | Out-String)
Email -sub "SCCM AWS Workspace Automated Patching Report 3/4 - Computers failed with update" -content "$Result"

# Power on any Workspaces if needed
Foreach ($PC in $Remediation.Name) {
$AWSPC = Get-ADComputer -Identity $PC -Properties "WorkSpace-ID" | Select-Object Name, WorkSpace-ID
Get-WKSWorkspace -WorkspaceId $AWSPC.'WorkSpace-ID'| where {$_.State -eq 'Stopped'} | Start-WKSWorkspace
}

# Wait 15min
Start-Sleep -s 900

# Start Patching again
foreach($PC in $Remediation.Name){ 
Write-host $PC
$Client = [wmiclass] "\\$PC\root\ccm:SMS_Client"
# Machine Policy Retrival
$Client.TriggerSchedule("{00000000-0000-0000-0000-000000000021}")
# Machine Policy Evaluation
$Client.TriggerSchedule("{00000000-0000-0000-0000-000000000022}")
# Software Update Scan Cycle 
$Client.TriggerSchedule("{00000000-0000-0000-0000-000000000113}")
# Software Updates Assignments Evaluation Cycle 
$Client.TriggerSchedule("{00000000-0000-0000-0000-000000000108}")
}

Start-Sleep -s 2400


# Show status
Foreach ($PC in $Remediation.Name) {
$AWSPC = Get-ADComputer -Identity $PC -Properties "WorkSpace-ID" | Select-Object Name, WorkSpace-ID
Get-WKSWorkspace -WorkspaceId $AWSPC.'WorkSpace-ID'
}


$i = $i - 1
}


#12.Email Final Patching Status
$Remediation = SQLResult -qry_where $qry_all
$Result = ($Remediation | Select Name, OS, AssignmentName, StateType, StateID, StateName | ConvertTo-Html -Head $style | Out-String)
Email -sub "SCCM AWS Workspace Automated Patching Report 4/4 - Final Patching Status" -content "$Result"