PowerShell ISE Add-on for Remote ConfigMgr Session

You may have seen the great post by Microsoft’s Matt Shadbolt on how to create an add-on for the Powershell ISE that will import the ConfigMgr module for you.  This is very useful if you are working on the Site Server itself, or have a fast remote connection to it, but in my environment, I work an a different geographical location to the Site Server.  Using the ConfigMgr module installed on my local workstation is a bit slow, so I usually use an RDP session to the server itself.

Implicit remoting, where you import a remote module, doesn’t seem to work because you need the PSDrive for the remote session.

So why not just create an ISE add-on for a remote Powershell session?  Then I can work on my local workstation, but run all the commands on the SCCM server itself with no latency penalty.

It’s easy enough to do: add the following code to your Powershell ISE profile script, located at $home\Documents\WindowsPowershell\Microsoft.PowerShellISE_profile.ps1. Add your SCCM Site Server to the $SiteServer variable.


$SiteServer = "MySCCMServer"
$CurrentTab = $psISE.CurrentPowerShellTab
  if ("$SiteServer" -notin @($psISE.PowerShellTabs.DisplayName)) {
$psISE.PowerShellTabs.Add().DisplayName = "$SiteServer"
}
$RemoteTab = $psISE.PowerShellTabs | ? DisplayName -eq $SiteServer
  while (-not $RemoteTab.CanInvoke) { Sleep 1 }
$RemoteTab.Invoke({ Enter-PSSession -ComputerName $SiteServer })
  while (-not $RemoteTab.CanInvoke) { Sleep 1 }
$RemoteTab.Invoke({ import-module ($Env:SMS_ADMIN_UI_PATH.Substring(0, $Env:SMS_ADMIN_UI_PATH.Length - 5) + '\ConfigurationManager.psd1')})
  while (-not $RemoteTab.CanInvoke) { Sleep 1 }
$RemoteTab.Invoke({ $PSD = Get-PSDrive -PSProvider CMSite })
  while (-not $RemoteTab.CanInvoke) { Sleep 1 }
$RemoteTab.Invoke({ CD "($PSD)" })
  while (-not $RemoteTab.CanInvoke) { Sleep 1 }
$RemoteTab.Invoke({ cls })

Then simply run the add-on after opening your ISE to get a remote session tab to your SCCM server with the ConfigMgr module imported 🙂

2015-01-28 15_47_40-Windows PowerShell ISE

Deploying KB3025945 Using the ConfigMgr Package Model

We recently had some reports of IE9 crashing after installing a MS patch KB3008923 released December 9th, 2014.  A few days ago on January 22nd, 2015 MS released an update that fixes the issue, KB3025945.  Unfortunately at the time of writing, the update is not available on WSUS, so it must be manually downloaded and deployed.  I decided to deploy the patch using ConfigMgr to the affected machines.  Of course, you could use SCUP to deploy the patch using the Software Updates model, but I decided to use the standard Package and Program model instead, with some Powerhelp ;).  Here’s a step-by-step guide for deploying the patch.  I’m using ConfigMgr 2012 R2 CU3.

Create a Collection

First, we need to identify all the machines that may be affected by this issue.  According to the KB, it only affects machines that have the KB3008923 installed, Internet Explorer 9, and the following operating systems: Windows 7 SP1, Windows 2008 SP2 and Windows 2008 R2.

Let’s use a little Powershell to create the collection and add the query rule that will filter for affected machines.  I’m using a limiting collection called ‘All SCCM 2012 Clients’, and I’m using Software Inventory to determine the IE version installed and the Win32_QuickFixEngineering WMI class to determine installed patches, so make sure that’s all enabled in your client settings (Software Inventory, Hardware Inventory classes).


# Create the collection
New-CMDeviceCollection -LimitingCollectionName "All SCCM 2012 Clients" -Name "KB3025945" `
-Comment "Manual deployment of patch KB3025945 to all Win 7 SP1, W2K8 R2 SP1 and W2K8 SP2 machines with KB3008923 and IE9 installed" `
-RefreshType Periodic

# Add the query rule
Add-CMDeviceCollectionQueryMembershipRule -CollectionName "KB3025945" -QueryExpression `
"select SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_G_System_OPERATING_SYSTEM.Version
from SMS_R_System
inner join SMS_G_System_QUICK_FIX_ENGINEERING on SMS_G_System_QUICK_FIX_ENGINEERING.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_SoftwareFile.FilePath like ""%\\Program Files\\Internet Explorer\\""
and SMS_G_System_SoftwareFile.FileName like ""iexplore.exe""
and SMS_G_System_SoftwareFile.FileVersion like ""9.%"" and
SMS_G_System_QUICK_FIX_ENGINEERING.HotFixID = ""KB3008923"" and
SMS_G_System_OPERATING_SYSTEM.Caption in (
""Microsoft Windows 7 Enterprise"",
""Microsoft Windows 7 Enterprise K"",
""Microsoft Windows 7 Enterprise N"",
""Microsoft Windows 7 Professional"",
""Microsoft Windows 7 Ultimate"",
""Microsoft Windows Server 2008 R2 Enterprise"",
""Microsoft Windows Server 2008 R2 Standard"",
""Microsoft® Windows Server® 2008 Enterprise"",
""Microsoft® Windows Server® 2008 Standard""
)
and SMS_G_System_OPERATING_SYSTEM.Version in (""6.0.6002"",""6.1.7601"")" `
-RuleName "KB3008923 / IE9"

Download the Patches

Great, now we have the collection, let’s download the patches from the Microsoft Update Catalog and save them to our SCCM package source.  There are 5 patches available for the different OS’s, so I’m gonna download them all.

Capture

Create the Packages, Programs and Deployments with Powershell

Lets make life easy again and use the native ConfigMgr Powershell cmdlets to do the work for us.

First off, I’m setting the Package and Program property variables as these will be common to all the packages.  Be sure to add your distribution point group name.  Further down in the script, from line 36, we need to set the package source location ($source) for each of the packages we will create for the different OS’s.  If you are happy with the Package name ($Pkg) you can leave it as is, and the command-lines are populated for you.  Since the installers are msu files, we will use WUSA.exe to install them silently with the /quiet and /norestart switches.  We will deploy the packages to the collection we created, simply named ‘KB3025945’.

I’m choosing to create a Required deployment that will begin as soon as possible, and I’m suppressing any notifications as I prefer to deploy silently in most cases, but you can of course change these settings in the script.

Run the script, and it will create the packages, programs, deployments, and distribute the package content.


<#
----------------
Deploy KB3025945
----------------

This script creates packages and programs for KB3025945, distributes the packages and deploys them.

Set the variables below as required
#>
## Set Package / Program Properties

# Required amount of disk space
$DiskSpace = "100"
# Units for Disk space requirement, eg KB, MB or GB
$DiskUnit = "MB"
# Maximum allowed run time
$Duration = "16"
# Program can run, eg "WhetherOrNotUserIsLoggedOn", "OnlyWhenNoUserIsLoggedOn", "OnlyWhenUserIsLoggedOn"
$RunType = "WhetherOrNotUserIsLoggedOn"
# Run mode, eg "RunWithAdministrativeRights" or "RunWithUserRights"
$RunMode = "RunWithAdministrativeRights"
# Allow user to interact?
$UserInteraction = $False
# Allow program to be installed from task sequence?
$EnableTS = $True
# Distribution Point Group Name
$DPG = "All Distribution Points"
# Set OS versions as array
$OSes = "Win7","Win7x64","W2K8","W2K8R2","W2K8x64"
## Set error action
$ErrorActionPreference = "Stop"
foreach ($OS in $OSes)
{

# Set Package source location and file names
if ($OS -eq "Win7")
{
$Pkg = "KB3025945 - Windows 7 SP1 x86"
$Source = "\\sccmserver-01\SoftwareUpdates\Out-of-Band Patches\KB3025945\Update for Windows 7 (KB3025945)"
$CommandLine = "wusa.exe X86-all-ie9-windows6.1-kb3025945-x86_ba77aa242aa4a0991c8879f9a04aaedd4c4b67c0.msu /quiet /norestart"
}
if ($OS -eq "Win7x64")
{
$Pkg = "KB3025945 - Windows 7 SP1 x64"
$Source = "\\sccmserver-01\SoftwareUpdates\Out-of-Band Patches\KB3025945\Update for Windows 7 for x64-based Systems (KB3025945)"
$CommandLine = "wusa.exe AMD64-all-ie9-windows6.1-kb3025945-x64_6566d74c6f14e6d4b120d2b07d91711f2e4c7dc9.msu /quiet /norestart"
}
if ($OS -eq "W2K8")
{
$Pkg = "KB3025945 - Windows Server 2008 x86 SP2"
$Source = "\\sccmserver-01\SoftwareUpdates\Out-of-Band Patches\KB3025945\Update for Windows Server 2008 (KB3025945)"
$CommandLine = "wusa.exe X86-all-ie9-windows6.0-kb3025945-x86_4a6f65fe4b418deb3e9626ba86234c3db718f85f.msu /quiet /norestart"
}
if ($OS -eq "W2K8R2")
{
$Pkg = "KB3025945 - Windows Server 2008 R2 SP1"
$Source = "\\sccmserver-01\SoftwareUpdates\Out-of-Band Patches\KB3025945\Update for Windows Server 2008 R2 x64 Edition (KB3025945)"
$CommandLine = "wusa.exe AMD64-all-ie9-windows6.1-kb3025945-x64_6566d74c6f14e6d4b120d2b07d91711f2e4c7dc9.msu /quiet /norestart"
}
if ($OS -eq "W2K8x64")
{
$Pkg = "KB3025945 - Windows Server 2008 x64 SP2"
$Source = "\\sccmserver-01\SoftwareUpdates\Out-of-Band Patches\KB3025945\Update for Windows Server 2008 x64 Edition (KB3025945)"
$CommandLine = "wusa.exe AMD64-all-ie9-windows6.0-kb3025945-x64_5cb8756529a63243805301e4e26f375bb6819fc1.msu /quiet /norestart"
}

## Create the Package ##

write-host "Creating Package: $Pkg" -foregroundcolor Green
try
{
$Package = New-CMPackage -Name $Pkg -Path $Source
}
catch { write-host "Could not create new package.`n$($_.Exception.Message)" -ForegroundColor Red; break }

## Create a Program

write-host 'Adding Program'
try
{
$Program = New-CMProgram `
-PackageName $Pkg `
-StandardProgramName $Pkg `
-CommandLine $CommandLine `
-DiskSpaceRequirement $DiskSpace `
-DiskSpaceUnit $DiskUnit `
-Duration $Duration `
-ProgramRunType $RunType `
-RunMode $RunMode `
-UserInteraction $UserInteraction
}
catch { write-host "Could not add program.`n$($_.Exception.Message)" -ForegroundColor Red; break }

## Update Program

write-host 'Updating Program'
try
{
$ProgramUpdate = Set-CMProgram `
-Name $Pkg `
-ProgramName $Pkg `
-StandardProgram `
-EnableTaskSequence $EnableTS
}
catch { write-host "Could not update program.`n$($_.Exception.Message)" -ForegroundColor Red; break }

## Distribute Content to DPs ##

write-host 'Distributing Content to DPs'
try
{
Start-CMContentDistribution -PackageName $Pkg -DistributionPointGroupName $DPG
}
catch { write-host "Could not distribute package.`n$($_.Exception.Message)" -ForegroundColor Red; break }


## Deploy the package

write-host "Deploying the package...please wait"
try
{
Start-CMPackageDeployment -CollectionName "KB3025945" `
-PackageName $pkg `
-ProgramName $pkg `
-StandardProgram `
-DeployPurpose Required `
-FastNetworkOption DownloadContentFromDistributionPointAndRunLocally `
-SlowNetworkOption DownloadContentFromDistributionPointAndLocally `
-SoftwareInstallation $true `
-SystemRestart $false `
-ScheduleEvent AsSoonAsPossible `
-RerunBehavior RerunIfFailedPreviousAttempt
}
catch { write-host "Could not deploy the package.`n$($_.Exception.Message)" -ForegroundColor Red; break }


}
write-host 'Done!' -ForegroundColor Yellow

Set the OS Requirement on the Programs

Great, now I have everything done…well, nearly everything…

Capture2

Capture4

Capture5

A limitation of the native Powershell cmdlets is that we can’t set the OS requirement on the Programs, so we need to do that in the console.  In the Properties of each program, set the relevant OS:

Capture3

That’s it!  Installation logging will appear in the WindowsUpdate.log on the client.  Since we did not enforce a reboot the clients will install the patch, but it will remain in the ‘reboot pending’ state until a reboot has been performed.  You can monitor your deployment using the standard methods in the ConfigMgr console or the built-in reports.

An Easy Way to Monitor Your ConfigMgr OS Deployments

There are several ways to monitor your OS deployments, since the monitoring service feature was introduced in MDT 2012 and above.  But if you are using ConfigMgr, or MDT-Integrated ConfigMgr, then it’s very easy to monitor your OSDs using a connection to the ConfigMgr database.  I made a post about this before, using a Powershell script to get a ‘snapshot’ of OSD status from the database.  In this post, I will use Microsoft Excel which allows me to set a refresh period on the SQL query, meaning I can get almost real-time information about my deployments as they happen, in step-by-step detail, even if my deployments are running on another geographical site.  This can be especially useful if a deployment fails (in a controlled way) as the error information is usually available in the database, and therefore visible in my Excel document.

Capture4

Create the Excel Document

First, we need to create a new Excel workbook and create a data connection to the ConfigMgr SQL database.  This is described in more detail in my post: Creating dynamic reports for configuration manager with microsoft excel

SQL Query

When you create the data connection, paste the following SQL query


Select Name0 as 'Computer Name'
,Name as 'Task Sequence'
,ExecutionTime
,Step
,ActionName
,GroupName
,tes.LastStatusMsgName
,ExitCode
,ActionOutput

from vSMS_TaskSequenceExecutionStatus tes
inner join v_R_System sys on tes.ResourceID = sys.ResourceID
inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
where tsp.Name in ('Windows OS Deployment x64', 'Windows OS Deployment x86')
and DATEDIFF(hour,ExecutionTime,GETDATE()) < 8
ORDER BY Step Desc

In line 14 of the query, I have added the names of my OSD task sequences, you will need to add your own.  You could, of course, create more than one data connection in the same Excel workbook, using a new worksheet for each OSD task sequence.

In line 15, I am returning data from the database from the last 8 hours.  You can change this to your preferred setting.

Set the Refresh Period

When creating the data connection, you have the option to refresh the data periodically.  Set this to refresh every minute.  This will simply run the SQL query every minute, keeping the workbook up-to-date.

Capture

Using the OSD Monitoring Workbook

Filtering

The workbook will display the computer name, task sequence name, step number and execution time, the name of the action and the group it belongs to, the last status message for that action, the exit code, and the action output, which is basically a snippet from the smsts.log log file for that action.

You can use Excel’s filters to filter the information you want to see.  For example, if you want to monitor OSD for a specific machine, simply filter for the machine name in the ‘Computer Name‘ column.

Capture2

Note that the computer name will only appear if the machine is already known to ConfigMgr, ie it is being rebuilt.  If it is bare metal, it will display ‘Unknown’ in the computer name.

You can also use the ‘ExecutionTime‘ column to filter for OSDs during a specific time period.  For example, here I filter for all OSD actions after 10:00:

Capture3

Finding errors

Because the exit code is reported for each step, you can simply filter that column to discover any steps that failed by selecting the non-zero exit codes.  Then you can check the ‘ActionOutput‘ column for a snippet from the smsts.log to find more about why it failed.

For example, one of my OSDs failed to apply the OS image:

PC003 Windows OS Deployment x64 16/01/2015 08:28 89 Apply x64 Operating System Image (Partition 3) OS Image x64 The task sequence execution engine failed executing an action -2147024751

I’ve seen that before, but let’s check the ActionOutput column for more details:


... ,721)
ApplyImage(), HRESULT=80070091 (e:\nts_sccm_release\sms\client\osdeployment\applyos\installimage.cpp,1830)
Apply(), HRESULT=80070091 (e:\nts_sccm_release\sms\client\osdeployment\applyos\installimage.cpp,2019)
installer.install(), HRESULT=80070091 (e:\nts_sccm_release\sms\client\osdeployment\applyos\installimage.cpp,2094)
Closing image file \\sccmsrv-01.testlab.com\SMSPKGC$\ABC00116\W7-X64-001.wim
ReleaseSource() for \\sccmsrv-01-testlab\SMSPKGC$\ABC00116\.
reference count 1 for the source \\sccmsrv-01.diasemi.com\SMSPKGC$\ABC00116\ before releasing
Released the resolved source \\sccmsrv-01.diasemi.com\SMSPKGC$\ABC00116InstallImage( g_InstallPackageID, g_ImageIndex, targetVolume, ImageType_OS, g_ConfigPackageID, g_ConfigFileName, bOEMMedia, g_RunFromNet ), HRESULT=80070091 (e:\nts_sccm_release\sms\client\osdeployment\applyos\applyos.cpp,509)
Installation of image 1 in package ABC00116 failed to complete..
The directory is not empty. (Error: 80070091; Source: Windows)

As I suspected, it could not wipe the partition of all the files, so I need to do it manually with diskpart.

A handy solution for easy monitoring and troubleshooting of your ConfigMgr OSDs 🙂

Monitoring the Project Server Job Queue with PowerShell

!UPDATE! 20-Jan-2015.  Updated the SQL query to also query for jobs that do not have a completion date, as these can also indicate a problem with the queue.

Recently we had an issue with our Project Server where there were some jobs stuck in the queue that were preventing other jobs, such as Project checkins, from being processed.  Since I was on vacation at the time, I was not aware of the issue until several days after the problem occurred.  Even when I’m in the office, I don’t make a habit of checking the Project Server queue as most of the time it is working fine.  But on the odd occasion it doesn’t, it can be a pain!  There is no built-in method of monitoring the queue, which seems to me to be a major oversight, but nevertheless, it was a good excuse to brush up on some scripting 😉  I decided to write a Powershell script that checks the Project Server queue jobs and sends me an email when there are a certain number of ‘unsuccessful’ jobs.  By ‘unsuccessful’ I mean any job that is not in the ‘success’ state, such as failed jobs, or those that are blocked due to another failed job.  The script can be run as a scheduled task using any schedule you specify, to provide continuous ‘monitoring’ of the queue.  This way you can be notified of any potential problems with the queue without having to make manual checks in PWA.

The recommended way to programmatically check the queue is to use the PSI Web Services, but I decided instead to query the Project Server database directly instead, since I already have some Powershell code which I use for querying SQL server.  The script was created for use with Project Server 2010, but may work with other versions too.

Download the Script

You can download the script from the Technet gallery here.

What Does the Script Do?

First, we open a connection to the draft Project Server database, and run a query against a view called dbo.MSP_QUEUE_PROJECT_GROUP_FULL_VIEW.  This SQL view contains most of the information we need from the queue, although we need to use the query to ‘translate’ some of the fields into something more meaningful, including the queue state and message type codes.  We then check the total number of unsuccessful jobs in the queue in the time period we have specified.  If that number is greater than the minimum, we create and send an html-formatted email with the results of the query.

Note that we are only checking for Project jobs in the queue, we are not checking for Timesheet jobs.

Configuring the Script

To use the script in your own environment, you will need to configure a few parameters at the top of the script, including your SQL database server and instance, the database name (use the draft database), your smtp server name, email recipient address, email from address.  You can also change the location of the temp file created for preparing the email text if you wish.  This file gets deleted at the end of the script.


# Database info
$dataSource = “mysqlserver\myinstance”
$database = “PS_2010_PWA_DRAFT_DB”

# Mail settings
$smtpserver = "mysmtpserver"
$MailSubject = "Project Server Queue Unsuccessful Jobs"
$MailRecipients = "joe.bloggs@mycompany.com"
$FromAddress = "ProjectServer@mycompany.com"

# Location of temp file for email message body (will be removed after)
$msgfile = 'c:\temp\mailmessage.txt'

Security

To run the script, you will need to do so with an account that has at least db_datareader access to the draft PS database.  If you wish to use your own account for that, or the account that will be running the scheduled task, then the script will work as-is, but if you want to use a specific SQL account, then you will need to comment line 110, uncomment line 112, and enter the SQL credentials in the in the connection string (uid and pwd).


# ConnectionString for integrated authentication
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
# ConnectionString for SQL authentication
#$connectionString = "Server=$dataSource;Database=$database;uid=ProjServer_Read;pwd=Pa$$w0rd;Integrated Security=false"

Schedule the Script

Create a new scheduled task and enter the action as ‘Start a program’:

CaptureBe sure to use double quotes when specifying the file name of the script, eg

-file “C:\Scripts\Powershell\Monitor-ProjectServerQueue.ps1”

Set the schedule of your choice (I run it every hour).

Parameters

The script will accept two non-mandatory parameters, and these are documented in the script’s help file:

-TimeInHours

The number of hours past in which to check the queue jobs.  The default is the last 24 hours.

-MinJobCount

The minimum number of unsuccessful jobs.  If the number of unsuccessful jobs reaches this number or higher, an email will be sent.  The default is 5.

The Result

This is an example email telling me there are 3 unsuccessful jobs in the queue:

Capture2