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 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:
very cool! thanks! it ask for the type of the log (file or folder) -edit line 343: $NoOutput = New-Item -type file $LogFile -Force
ReplyDeletebut otherwise perfect!
really liked the exclusion list and email notification, thanks a lot for sharing