21 June 2016

PowerShell: Automatically Populate the Microsoft Office Updates folder via SCCM

One of the things that has annoyed me in the past is having to keep the Microsoft Office Updates folder up-to-date with the latest .MSP files. The reason I bother with keeping the Updates folder up-to-date is because it speeds up the process of building a golden image, which in my environment, Office is installed during that phase. To automate this process, I created an automatic deployment rule in SCCM that keeps the Microsoft Office software update group up-to-date with the latest Office updates. To bridge those updates with the Updates folder under the Office installation directory, I wrote the script below with the help of Sapien's PowerShell Studio that made this a breeze to write and properly code. 

The script can be executed either by a scheduled task or using Microsoft Orchestrator, which is what I am using. I also set all of my parameter values within the script, but if you do not feel comfortable doing that, you can also specify the parameter values at the command line.

The script checks the folder where the automatic deployment rule in SCCM writes the new updates to. If there are new updates since the last time it was checked, it will copy them over and email the appropriate IT staff so they know when new updates are being applied in their environment. It also has an exclusion file that allows you to exclude updates if they interfere in your environment. It keeps track of what updates have been copied by writing the copied updates to a text file. The parameters should be the only thing you need to change to adapt the script to your environment. I have put a couple of examples inside the script as how to call the script from command line, especially if you want the logs emailed.

The script can be downloaded from here.

NOTE: Last December, I wrote a version of this script to automatically inject updates into the Microsoft Office folder. I found the updates were hit and miss as to what got installed. The problem came from the fact that when the .MSP files were extracted, they would be named the same as previous versions, therefor overwriting other updates. I then thought that I would need to rename those files to a unique name so they would all get applied. I continued to rewrite the script. In talking online about it, a user pointed me to the fact that he wrote a script that did the same thing. I liked his script, but I also wanted additional features, such as email and reporting. 

NOTE: Also, Microsoft uses the same KB number in the metadata field for each patch within a service pack. Because of that, make sure to extract a service pack to the updates folder manually and don't rename them. I would add the KB number for the office service pack to the exclusion list. Here is a screenshot of how it should look for Office 2010 with the SP2 applied to the updates folder.


Here is a demo of the script being executed manually:



1:  <#  
2:       .SYNOPSIS  
3:            Microsoft Office Updater  
4:         
5:       .DESCRIPTION  
6:            This script will keep the Updates folder populated with the latest Office  
7:            updates that SCCM has downloaded. It should be setup to execute as a  
8:            scheduled task on the SCCM server. I suggest executing it once a week.  
9:         
10:       .PARAMETER Country  
11:            The language-country of the updates  
12:         
13:       .PARAMETER EmailLogs  
14:            True or false on whether to email logs of the latest updates applied to the Office Updates folder.  
15:         
16:       .PARAMETER EmailRecipients  
17:            Email address to send report to  
18:         
19:       .PARAMETER EmailSender  
20:            Sender email address  
21:         
22:       .PARAMETER ExclusionFileName  
23:            Text file containing a list of updates to exclude  
24:         
25:       .PARAMETER LogFileName  
26:            Name of the log file to be written to  
27:         
28:       .PARAMETER ProcessedUpdatesFile  
29:            Name of the file containing a list of Microsoft Office updates that have already been copied over  
30:         
31:       .PARAMETER SMTPServer  
32:            fully qualified SMTP server name  
33:         
34:       .PARAMETER SourceFolder  
35:            The folder where SCCM stores the updates  
36:         
37:       .PARAMETER UpdatesFolder  
38:            The folder where Microsoft Office looks for the updates. This is typically <Office installer folder>\Updates.  
39:         
40:       .EXAMPLE  
41:            All parameters pre-populated  
42:            powershell.exe -executionpolicy bypass -file OfficeUpdater.ps1  
43:              
44:            All parameters pre-populated without email send (-command must be used when populating boolean values at the command line)  
45:            powershell.exe -executionpolicy bypass -command OfficeUpdater.ps1 -EmailLogs $false  
46:         
47:       .NOTES  
48:            ===========================================================================  
49:            Created with:     SAPIEN Technologies, Inc., PowerShell Studio 2016 v5.2.122  
50:            Created on:       6/15/2016 10:29 AM  
51:            Created by:       Mick Pletcher  
52:            Organization:  
53:            Filename:         OfficeUpdater.ps1  
54:            ===========================================================================  
55:  #>  
56:  [CmdletBinding()]  
57:  param  
58:  (  
59:            [ValidateNotNullOrEmpty()][string]$Country,  
60:            [ValidateNotNullOrEmpty()][boolean]$EmailLogs,  
61:            [string]$EmailRecipients,  
62:            [string]$EmailSender,  
63:            [ValidateNotNullOrEmpty()][string]$ExclusionFileName,  
64:            [ValidateNotNullOrEmpty()][string]$LogFileName,  
65:            [ValidateNotNullOrEmpty()][string]$ProcessedUpdatesFile,  
66:            [string]$SMTPServer,  
67:            [ValidateNotNullOrEmpty()][string]$SourceFolder,  
68:            [ValidateNotNullOrEmpty()][string]$UpdatesFolder  
69:  )  
70:    
71:  function Copy-Updates {  
72:  <#  
73:       .SYNOPSIS  
74:            Copy the Office updates to the updates folder  
75:         
76:       .DESCRIPTION  
77:            This will extract the .msp file from the .cab and then copy it to the Microsoft Office installation updates folder.  
78:         
79:       .PARAMETER UnprocessedUpdates  
80:            Updates that SCCM has downloaded but have not been copied to the updates folder.  
81:         
82:       .NOTES  
83:            Additional information about the function.  
84:  #>  
85:         
86:       [CmdletBinding()]  
87:       param  
88:       (  
89:                 [ValidateNotNullOrEmpty()][object]$UnprocessedUpdates  
90:       )  
91:         
92:       $RelativePath = Get-RelativePath  
93:       $LogFile = $RelativePath + $LogFileName  
94:       $ExclusionList = Get-ExclusionList  
95:       foreach ($Update in $UnprocessedUpdates) {  
96:            $ExtractedFolder = $SourceFolder + '\' + $Update.Name + '\extracted'  
97:            If ((Test-Path $ExtractedFolder) -eq $true) {  
98:                 $Files = Get-ChildItem -Path $ExtractedFolder  
99:                 foreach ($File in $Files) {  
100:                      if ($File.Extension -eq '.msp') {  
101:                           [string]$KBUpdate = Get-MSPFileInfo -Path $File.Fullname -Property 'KBArticle Number'  
102:                           $KBUpdate = 'KB' + $KBUpdate.Trim()  
103:                           $KBUpdateShortName = $KBUpdate + '.msp'  
104:                           $KBUpdateFullName = $File.DirectoryName + '\' + $KBUpdateShortName  
105:                           $DestinationFile = $UpdatesFolder + '\' + $KBUpdateShortName  
106:                           If ($KBUpdate -notin $ExclusionList) {  
107:                                Write-Host "Renaming"$File.Name"to"$KBUpdateShortName"....." -NoNewline  
108:                                $NoOutput = Copy-Item -Path $File.Fullname -Destination $KBUpdateFullName -Force  
109:                                If ((Test-Path $KBUpdateFullName) -eq $true) {  
110:                                     Write-Host "Success" -ForegroundColor Yellow  
111:                                } else {  
112:                                     Write-Host "Failed" -ForegroundColor Red  
113:                                }  
114:                                Write-Host "Copying"$KBUpdateShortName" to Office updates folder....." -NoNewline  
115:                                $NoOutput = Copy-Item -Path $KBUpdateFullName -Destination $UpdatesFolder -Force  
116:                                If ((Test-Path $DestinationFile) -eq $true) {  
117:                                     Write-Host "Success" -ForegroundColor Yellow  
118:                                     Add-Content -Path $LogFile -Value $KBUpdate -Force  
119:                                } else {  
120:                                     Write-Host "Failed" -ForegroundColor Red  
121:                                }  
122:                           } else {  
123:                                Write-Host $KBUpdate"....." -NoNewline  
124:                                Write-Host "Excluded" -ForegroundColor Green  
125:                           }  
126:                      }  
127:                 }  
128:            }  
129:            Start-Sleep -Seconds 1  
130:       }  
131:  }  
132:    
133:  function Expand-CABFiles {  
134:  <#  
135:       .SYNOPSIS  
136:            Extract the contents of the .CAB file  
137:         
138:       .DESCRIPTION  
139:            This will extract the contents of the CAB file to a temporary subfolder for all unprocessed updates. It filters the updates to expand by the country code specified in the Country parameter. It also expands all cab with none in the name.  
140:         
141:       .PARAMETER UnprocessedUpdates  
142:            Updates that SCCM has downloaded but have not been copied to the updates folder.  
143:         
144:       .NOTES  
145:            Additional information about the function.  
146:  #>  
147:         
148:       [CmdletBinding()]  
149:       param  
150:       (  
151:                 [ValidateNotNullOrEmpty()][object]$UnprocessedUpdates  
152:       )  
153:         
154:       $Executable = $env:windir + "\System32\expand.exe"  
155:       $Country = '*' + $Country + '*'  
156:       foreach ($Update in $UnprocessedUpdates) {  
157:            $Folder = $SourceFolder + '\' + $Update.Name  
158:            $Files = Get-ChildItem -Path $Folder  
159:            foreach ($File in $Files) {  
160:                 If (($File.Name -like $Country) -or ($File.Name -like "*none*")) {  
161:                      $ExtractedDirectory = $File.DirectoryName + '\extracted'  
162:                      If ((Test-Path $ExtractedDirectory) -eq $true) {  
163:                           $NoOutput = Remove-Item $ExtractedDirectory -Recurse -Force  
164:                      }  
165:                      $NoOutput = New-Item $ExtractedDirectory -ItemType Directory  
166:                      Write-Host "Extracting"$File.Name"....." -NoNewline  
167:                      $Parameters = [char]34 + $File.FullName + [char]34 + [char]32 + [char]34 + $ExtractedDirectory + [char]34 + [char]32 + "-f:*"  
168:                      $ErrCode = (Start-Process -FilePath $Executable -ArgumentList $Parameters -Wait -WindowStyle Minimized -Passthru).ExitCode  
169:                      If ($ErrCode -eq 0) {  
170:                           Write-Host "Success" -ForegroundColor Yellow  
171:                      } else {  
172:                           Write-Host "Failed" -ForegroundColor Red  
173:                      }  
174:                 }  
175:            }  
176:       }  
177:  }  
178:    
179:  function Get-ExclusionList {  
180:  <#  
181:       .SYNOPSIS  
182:            Office Updates Exclusion List  
183:         
184:       .DESCRIPTION  
185:            Contains a list of Microsoft Office updates to exclude from installation  
186:         
187:       .NOTES  
188:            Additional information about the function.  
189:  #>  
190:         
191:       [CmdletBinding()][OutputType([object])]  
192:       param ()  
193:         
194:       $RelativePath = Get-RelativePath  
195:       $ExclusionFile = $RelativePath + $ExclusionFileName  
196:       If ((Test-Path $ExclusionFile) -eq $true) {  
197:            $ExclusionList = Get-Content $ExclusionFile -Force  
198:       } else {  
199:            $NoOutput = New-Item -Path $ExclusionFile -Force  
200:            $ExclusionList = Get-Content $ExclusionFile -Force  
201:       }  
202:       Return $ExclusionList  
203:  }  
204:    
205:  function Get-ExtractedUpdatesList {  
206:  <#  
207:       .SYNOPSIS  
208:            List of updates already extracted  
209:         
210:       .DESCRIPTION  
211:            This function retrieves the list of updates that have already been extracted to the updates folder.  
212:         
213:       .NOTES  
214:            Additional information about the function.  
215:  #>  
216:         
217:       [CmdletBinding()]  
218:       param ()  
219:         
220:       $RelativePath = Get-RelativePath  
221:       $ExtractedUpdatesFile = $RelativePath + $ProcessedUpdatesFile  
222:       If ((Test-Path $ExtractedUpdatesFile) -eq $true) {  
223:            $File = Get-Content -Path $ExtractedUpdatesFile -Force  
224:            Return $File  
225:       } else {  
226:            $NoOutput = New-Item -Path $ExtractedUpdatesFile -ItemType File -Force  
227:            Return $null  
228:       }  
229:  }  
230:    
231:  function Get-MSPFileInfo {  
232:  <#  
233:       .SYNOPSIS  
234:            Extract MSP information  
235:         
236:       .DESCRIPTION  
237:            This function will extract MSP file information from the metadata table. It has been written to be able to read data from a lot of different MSP files, including Microsoft Office updates and most application patches. There are some MSP files that were not populated with the metadata table, therefor no data is obtainable.  
238:         
239:       .PARAMETER Path  
240:            Location of the MSP file  
241:         
242:       .PARAMETER Property  
243:            A pre-defined set of properties in the msi metadata table  
244:         
245:       .NOTES  
246:            Additional information about the function.  
247:  #>  
248:         
249:       param  
250:       (  
251:                 [Parameter(Mandatory = $true)][IO.FileInfo]$Path,  
252:                 [Parameter(Mandatory = $true)][ValidateSet('Classification', 'Description', 'DisplayName', 'KBArticle Number', 'ManufacturerName', 'ReleaseVersion', 'TargetProductName')][string]$Property  
253:       )  
254:         
255:       try {  
256:            $WindowsInstaller = New-Object -ComObject WindowsInstaller.Installer  
257:            $MSIDatabase = $WindowsInstaller.GetType().InvokeMember("OpenDatabase", "InvokeMethod", $Null, $WindowsInstaller, @($Path.FullName, 32))  
258:            $Query = "SELECT Value FROM MsiPatchMetadata WHERE Property = '$($Property)'"  
259:            $View = $MSIDatabase.GetType().InvokeMember("OpenView", "InvokeMethod", $null, $MSIDatabase, ($Query))  
260:            $View.GetType().InvokeMember("Execute", "InvokeMethod", $null, $View, $null)  
261:            $Record = $View.GetType().InvokeMember("Fetch", "InvokeMethod", $null, $View, $null)  
262:            $Value = $Record.GetType().InvokeMember("StringData", "GetProperty", $null, $Record, 1)  
263:            return $Value  
264:       } catch {  
265:            Write-Output $_.Exception.Message  
266:       }  
267:  }  
268:    
269:  function Get-NewUpdatesList {  
270:  <#  
271:       .SYNOPSIS  
272:            List new updates to install  
273:         
274:       .DESCRIPTION  
275:            Get the list of updates to process  
276:         
277:       .NOTES  
278:            Additional information about the function.  
279:  #>  
280:         
281:       [CmdletBinding()][OutputType([object])]  
282:       param ()  
283:         
284:       $UnprocessedFolders = @()  
285:       $ExtractedUpdatesList = Get-ExtractedUpdatesList  
286:       $List = Get-ChildItem $SourceFolder  
287:       foreach ($Update in $List) {  
288:            If ($Update.Name -notin $ExtractedUpdatesList ) {  
289:                 $UnprocessedFolders = $UnprocessedFolders + $Update  
290:            }  
291:       }  
292:       Return $UnprocessedFolders  
293:  }  
294:    
295:  function Get-RelativePath {  
296:  <#  
297:       .SYNOPSIS  
298:            Get the relative path  
299:         
300:       .DESCRIPTION  
301:            Returns the location of the currently running PowerShell script  
302:         
303:       .NOTES  
304:            Additional information about the function.  
305:  #>  
306:         
307:       [CmdletBinding()][OutputType([string])]  
308:       param ()  
309:         
310:       $Path = (split-path $SCRIPT:MyInvocation.MyCommand.Path -parent) + "\"  
311:       Return $Path  
312:  }  
313:    
314:  function New-LogFile {  
315:  <#  
316:       .SYNOPSIS  
317:            Generate a log file  
318:         
319:       .DESCRIPTION  
320:            Generate a log file containing a list of the KB updates copied into the updates folder  
321:         
322:       .NOTES  
323:            Additional information about the function.  
324:  #>  
325:         
326:       [CmdletBinding()][OutputType([boolean])]  
327:       param ()  
328:         
329:       $RelativePath = Get-RelativePath  
330:       $LogFile = $RelativePath + $LogFileName  
331:       If ((Test-path $LogFile) -eq $true) {  
332:            Write-Host 'Deleting old log file.....' -NoNewline  
333:            $NoOutput = Remove-Item $LogFile -Force  
334:            If ((Test-path $LogFile) -eq $false) {  
335:                 Write-Host "Success" -ForegroundColor Yellow  
336:            } else {  
337:                 Write-Host "Failed" -ForegroundColor Red  
338:                 $Success = $false  
339:            }  
340:       }  
341:       If ((Test-path $LogFile) -eq $false) {  
342:            Write-Host "Creating new log file....." -NoNewline  
343:            $NoOutput = New-Item $LogFile -Force  
344:            If ((Test-path $LogFile) -eq $true) {  
345:                 Write-Host "Success" -ForegroundColor Yellow  
346:                 $Success = $true  
347:            } else {  
348:                 Write-Host "Failed" -ForegroundColor Red  
349:                 $Success = $false  
350:            }  
351:       }  
352:       Return $Success  
353:  }  
354:    
355:  function Remove-ExtractionFolders {  
356:  <#  
357:       .SYNOPSIS  
358:            Delete the extraction folders  
359:         
360:       .DESCRIPTION  
361:            Delete the extracted folders and contents of the unprocessed updates.  
362:         
363:       .PARAMETER UnprocessedUpdates  
364:            Updates that SCCM has downloaded but have not been copied to the updates folder.  
365:         
366:       .NOTES  
367:            Additional information about the function.  
368:  #>  
369:         
370:       [CmdletBinding()]  
371:       param  
372:       (  
373:                 [ValidateNotNullOrEmpty()][object]$UnprocessedUpdates  
374:       )  
375:         
376:       foreach ($Update in $UnprocessedUpdates) {  
377:            $ExtractedFolder = $SourceFolder + '\' + $Update.Name + '\extracted'  
378:            $Deleted = $false  
379:            $Counter = 1  
380:            If ((Test-Path $ExtractedFolder) -eq $true) {  
381:                 Do {  
382:                      Try {  
383:                           Write-Host "Removing"$ExtractedFolder"....." -NoNewline  
384:                           $NoOutput = Remove-Item $ExtractedFolder -Recurse -Force -ErrorAction Stop  
385:                           If ((Test-Path $ExtractedFolder) -eq $false) {  
386:                                Write-Host "Success" -ForegroundColor Yellow  
387:                                $Deleted = $true  
388:                           } else {  
389:                                Write-Host "Failed" -ForegroundColor Red  
390:                                $Deleted = $false  
391:                           }  
392:                      } Catch {  
393:                           $Counter++  
394:                           Write-Host 'Failed. Retrying in 5 seconds' -ForegroundColor Red  
395:                           Start-Sleep -Seconds 5  
396:                           If ((Test-Path $ExtractedFolder) -eq $true) {  
397:                                $Deleted = $false  
398:                                Write-Host "Removing"$ExtractedFolder"....." -NoNewline  
399:                                $NoOutput = Remove-Item $ExtractedFolder -Recurse -Force -ErrorAction SilentlyContinue  
400:                                If ((Test-Path $ExtractedFolder) -eq $false) {  
401:                                     Write-Host "Success" -ForegroundColor Yellow  
402:                                     $Deleted = $true  
403:                                } else {  
404:                                     Write-Host "Failed" -ForegroundColor Red  
405:                                     $Deleted = $false  
406:                                }  
407:                           }  
408:                           If ($Counter = 5) {  
409:                                $Deleted = $true  
410:                           }  
411:                      }  
412:                 } while ($Deleted = $false)  
413:                 Start-Sleep -Seconds 1  
414:            }  
415:       }  
416:  }  
417:    
418:  function Send-UpdateReport {  
419:  <#  
420:       .SYNOPSIS  
421:            Send a report of new updates applied  
422:         
423:       .DESCRIPTION  
424:            Send the log file with a list of Office updates that were copied over to the Office updates folder.  
425:         
426:       .NOTES  
427:            Additional information about the function.  
428:  #>  
429:         
430:       [CmdletBinding()]  
431:       param ()  
432:         
433:       $RelativePath = Get-RelativePath  
434:       $LogFile = $RelativePath + $LogFileName  
435:       $Date = Get-Date -Format "dd-MMMM-yyyy"  
436:       $Subject = 'Microsoft Office Updates Report as of ' + $Date  
437:       $Body = 'List of Microsoft Office Updates added to the Office installation updates folder.'  
438:       $Count = 1  
439:       Do {  
440:            Try {  
441:                 Write-Host "Emailing report....." -NoNewline  
442:                 Send-MailMessage -To $EmailRecipients -From $EmailSender -Subject $Subject -Body $Body -Attachments $LogFile -SmtpServer $SMTPServer  
443:                 Write-Host "Success" -ForegroundColor Yellow  
444:                 $Exit = $true  
445:            } Catch {  
446:                 $Count++  
447:                 If ($Count -lt 4) {  
448:                      Write-Host 'Failed to send message. Retrying.....' -ForegroundColor Red  
449:                 } else {  
450:                      Write-Host 'Failed to send message' -ForegroundColor Red  
451:                      $Exit = $true  
452:                 }  
453:            }  
454:       } Until ($Exit = $true)  
455:         
456:  }  
457:    
458:  function Update-ProcessedUpdatesFile {  
459:  <#  
460:       .SYNOPSIS  
461:            Update the Processed Updates File with new updates  
462:         
463:       .DESCRIPTION  
464:            Add updates that were copied over to the processed updates file so they do not get processed again.  
465:         
466:       .PARAMETER UnprocessedUpdates  
467:            Updates that SCCM has downloaded but have not been copied to the updates folder.  
468:         
469:       .NOTES  
470:            Additional information about the function.  
471:  #>  
472:         
473:       [CmdletBinding()]  
474:       param  
475:       (  
476:                 [ValidateNotNullOrEmpty()]$UnprocessedUpdates  
477:       )  
478:         
479:       $RelativePath = Get-RelativePath  
480:       $LogFile = $RelativePath + $ProcessedUpdatesFile  
481:       foreach ($Update in $UnprocessedUpdates) {  
482:            $Success = $false  
483:            Do {  
484:                 Try {  
485:                      Write-Host 'Adding'$Update.Name'to Processed updates.....' -NoNewline  
486:                      Add-Content -Path $LogFile -Value $Update.Name -Force -ErrorAction Stop  
487:                      $Success = $true  
488:                      Write-Host "Success" -ForegroundColor Yellow  
489:                 } Catch {  
490:                      Write-Host "Failed" -ForegroundColor Red  
491:                      $Success = $false  
492:                 }  
493:            } while ($Success -eq $false)  
494:       }  
495:  }  
496:    
497:  Clear-Host  
498:  $UnprocessedFolders = Get-NewUpdatesList  
499:  If ($UnprocessedFolders -ne $null) {  
500:       $NewLog = New-LogFile  
501:       If ($NewLog -eq $true) {  
502:            Expand-CABFiles -UnprocessedUpdates $UnprocessedFolders  
503:            Copy-Updates -UnprocessedUpdates $UnprocessedFolders  
504:            Remove-ExtractionFolders -UnprocessedUpdates $UnprocessedFolders  
505:            Update-ProcessedUpdatesFile -UnprocessedUpdates $UnprocessedFolders  
506:            If ($EmailLogs -eq $true) {  
507:                 Send-UpdateReport  
508:            }  
509:       } else {  
510:            Write-Host "Failed to create log file."  
511:       }  
512:  } else {  
513:       Write-Host "No updates to process"  
514:  }  
515:    

1 comments:

  1. very cool! thanks! it ask for the type of the log (file or folder) -edit line 343: $NoOutput = New-Item -type file $LogFile -Force
    but otherwise perfect!
    really liked the exclusion list and email notification, thanks a lot for sharing

    ReplyDelete