Get the Deployment Status of ConfigMgr Packages and Task Sequences with PowerShell

In my last post, we looked at how to report on the deployment status of ConfigMgr Applications using PowerShell.  Of course, you can get this information from the built-in ConfigMgr SSRS reports too, but our aim here is to report this data quickly by using PowerShell to query the ConfigMgr database directly.  It enables you to quickly check the deployment status at any time, or to put the data into a csv allowing us to create a custom Excel report, for example.

In this post, we will do the same, but using a different SQL query which will report on both Packages and Task Sequences.

This query will give us the targeted collection names, the targeted computer and user names, the acceptance status and times, and the delivery status and times.  In the case of a failed installation, it will report a failure code in the LastExecutionResult column.

As with the previous script, you can filter the results either in PowerShell’s Gridview, or Excel, to identify specific deployments, computers, timescales, delivery states etc.

Capture

To run the script you will need db_datareader access to your ConfigMgr SQL database, or you can enter the credentials of an SQL account that does in the script.

Enter the following variables:

  • $Name – The name of the ConfigMgr Package or Task Sequence
  • $CSV – Enter Yes to return the data into a csv file
  • $Grid – Enter Yes to return the data into PowerShell’s Gridview
  • $datasource – The name of the SQL Server and Instance hosting your ConfigMgr database
  • $database – Your ConfigMgr database name

<#

This script gets the deployment status of a ConfigMgr 2012 Package or Task Sequence

#>

$Name = "My ConfigMgr Package" # Enter Package or Task Sequence Name
$CSV = "No" # Output to CSV, Yes or No
$Grid = "Yes" # Out-Gridview, Yes or No
# Get Start Time
$startDTM = (Get-Date)

# Database info
$dataSource = “mysqlserver\INST_SCCM”
$database = “CM_ABC”

# Open a connection
cls
Write-host "Opening a connection to '$database' on '$dataSource'"
#Using windows authentication, or..
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
# Using SQL authentication
#$connectionString = "Server=$dataSource;Database=$database;uid=ConfigMgrDB_Read;pwd=Pa$$w0rd;Integrated Security=false"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Getting Package / TS deployment status
Write-host "Running query..."

$query = "
select PackageName as 'Package / Task Sequence',ai.AdvertisementID as 'DeploymentID',ai.CollectionName, Name0 as 'Computer Name', User_Name0 as 'User Name', LastAcceptanceMessageIDName, LastAcceptanceStateName, LastAcceptanceStatusTime, LastStatusMessageIDName, LastStateName, LastStatusTime, LastExecutionResult
from v_ClientAdvertisementStatus cas
inner join v_R_System sys on sys.ResourceID=cas.ResourceID
inner join v_AdvertisementInfo ai on ai.AdvertisementID=cas.AdvertisementID
where PackageName = '$Name' and LastStatusTime is not null ORDER BY LastStatusTime Desc
"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)
$Count = $table.Rows.Count

if ($CSV -eq "Yes")
{
$Date = Get-Date -Format HH-mm--dd-MMM-yy
$Path = "C:\Script_Files\SQLQuery-$Date.csv"
$table | Export-Csv -Path $Path
Invoke-Item -Path $Path
}
If ($Grid -eq "Yes")
{
$table | Out-GridView -Title "Deployment Status of '$Name' ($count machines)"
}
# Close the connection
$connection.Close()

# Get End Time
$endDTM = (Get-Date)

# Echo Time elapsed
"Elapsed Time: $(($endDTM-$startDTM).totalseconds) seconds"

How to Get the Deployment Status of ConfigMgr Applications with PowerShell

PowerShell and SQL server.  It’s a combination I’m liking more every day 🙂  In this post, I give you a PowerShell script that will query your ConfigMgr SQL server and return the deployment status of a ConfigMgr Application.

It will return data for each deployment of the application, giving you the names of the deployment types and the targeted collections, as well as the status of each computer that has received the deployment.  The SQL query will interpret the AppEnforcementState value to it’s friendly name to make it easier to determine the deployment status of each device.  Finally, we sort by the LastComplianceMessageTime, showing the most recently completed deployments at the top.

Results can be returned into PowerShell’s Gridview, or CSV format, where you can filter the results for a specific deployment type, targeted collection, deployment status, computer, or deployment compliance message time.

Capture

What about package and task sequence deployments, you say?  Watch this space 😉

To run the script you will need db_datareader access to your ConfigMgr SQL database, or you can enter the credentials of an SQL account that does in the script.

Enter the following variables:

  • $ApplicationName – The name of the ConfigMgr Application
  • $CSV – Enter Yes to return the data into a csv file
  • $Grid – Enter Yes to return the data into PowerShell’s Gridview
  • $datasource – The name of the SQL Server and Instance hosting your ConfigMgr database
  • $database – Your ConfigMgr database name

<#

This script gets the Deployment status of a ConfigMgr 2012 Application

#>

$ApplicationName = "Cisco Webex Meeting Center" # Enter the Application Name
$CSV = "No" # Output to CSV, Yes or No
$Grid = "Yes" # Out-Gridview, Yes or No

# Database info

$dataSource = “mysqlserver\INST_SCCM”
$database = “CM_ABC”

# Get Start Time
$startDTM = (Get-Date)

# Open a connection
cls
Write-host "Opening a connection to '$database' on '$dataSource'"
#Using windows authentication, or..
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
# Using SQL authentication
#$connectionString = "Server=$dataSource;Database=$database;uid=ConfigMgrDB_Read;pwd=Pa$$w0rd;Integrated Security=false"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Getting Application Deployment Data
Write-host "Running query..."

$query = "
select distinct
aa.ApplicationName,
ae.AssignmentID,
aa.CollectionName as 'Target Collection',
ae.descript as 'Deployment Type Name',
s1.netbios_name0 as 'Computer Name',
ci2.LastComplianceMessageTime,
ae.AppEnforcementState,
case when ae.AppEnforcementState = 1000 then 'Success'
when ae.AppEnforcementState = 1001 then 'Already Compliant'
when ae.AppEnforcementState = 1002 then 'Simulate Success'
when ae.AppEnforcementState = 2000 then 'In Progress'
when ae.AppEnforcementState = 2001 then 'Waiting for Content'
when ae.AppEnforcementState = 2002 then 'Installing'
when ae.AppEnforcementState = 2003 then 'Restart to Continue'
when ae.AppEnforcementState = 2004 then 'Waiting for maintenance window'
when ae.AppEnforcementState = 2005 then 'Waiting for schedule'
when ae.AppEnforcementState = 2006 then 'Downloading dependent content'
when ae.AppEnforcementState = 2007 then 'Installing dependent content'
when ae.AppEnforcementState = 2008 then 'Restart to complete'
when ae.AppEnforcementState = 2009 then 'Content downloaded'
when ae.AppEnforcementState = 2010 then 'Waiting for update'
when ae.AppEnforcementState = 2011 then 'Waiting for user session reconnect'
when ae.AppEnforcementState = 2012 then 'Waiting for user logoff'
when ae.AppEnforcementState = 2013 then 'Waiting for user logon'
when ae.AppEnforcementState = 2014 then 'Waiting to install'
when ae.AppEnforcementState = 2015 then 'Waiting retry'
when ae.AppEnforcementState = 2016 then 'Waiting for presentation mode'
when ae.AppEnforcementState = 2017 then 'Waiting for Orchestration'
when ae.AppEnforcementState = 2018 then 'Waiting for network'
when ae.AppEnforcementState = 2019 then 'Pending App-V Virtual Environment'
when ae.AppEnforcementState = 2020 then 'Updating App-V Virtual Environment'
when ae.AppEnforcementState = 3000 then 'Requirements not met'
when ae.AppEnforcementState = 3001 then 'Host platform not applicable'
when ae.AppEnforcementState = 4000 then 'Unknown'
when ae.AppEnforcementState = 5000 then 'Deployment failed'
when ae.AppEnforcementState = 5001 then 'Evaluation failed'
when ae.AppEnforcementState = 5002 then 'Deployment failed'
when ae.AppEnforcementState = 5003 then 'Failed to locate content'
when ae.AppEnforcementState = 5004 then 'Dependency installation failed'
when ae.AppEnforcementState = 5005 then 'Failed to download dependent content'
when ae.AppEnforcementState = 5006 then 'Conflicts with another application deployment'
when ae.AppEnforcementState = 5007 then 'Waiting retry'
when ae.AppEnforcementState = 5008 then 'Failed to uninstall superseded deployment type'
when ae.AppEnforcementState = 5009 then 'Failed to download superseded deployment type'
when ae.AppEnforcementState = 5010 then 'Failed to updating App-V Virtual Environment'
End as 'State Message'
from v_R_System_Valid s1
join vAppDTDeploymentResultsPerClient ae on ae.ResourceID=s1.ResourceID
join v_CICurrentComplianceStatus ci2 on ci2.CI_ID=ae.CI_ID AND
ci2.ResourceID=s1.ResourceID
join v_ApplicationAssignment aa on ae.AssignmentID = aa.AssignmentID
where ae.AppEnforcementState is not null and aa.ApplicationName='$ApplicationName'
order by LastComplianceMessageTime Desc
"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)
$Count = $table.Rows.Count

if ($CSV -eq "Yes")
{
$Date = Get-Date -Format HH-mm--dd-MMM-yy
$Path = "C:\Script_Files\SQLQuery-$Date.csv"
$table | Export-Csv -Path $Path
Invoke-Item -Path $Path
}
If ($Grid -eq "Yes")
{
$table | Out-GridView -Title "Deployment Status of Application '$ApplicationName' ($count machines)"
}

# Close the connection
$connection.Close()

# Get End Time
$endDTM = (Get-Date)

# Echo Time elapsed
"Elapsed Time: $(($endDTM-$startDTM).totalseconds) seconds"

Monitoring Disk Space and Content Directories on ConfigMgr Distribution Points

Recently I noticed that the available disk space on my ConfigMgr distribution points was getting low.  Since they are mostly VMs using SAN storage it’s fairly easy to increase the disk space, but it got me thinking about how I could monitor the DPs for available free space, as well as the sizes of the key directories that ConfigMgr is using to store large amounts of data, including the SCCMContentLib, the legacy package share (SMSPKGx$), and in my case, the WSUSUpdates folder, as I also use our DPs as WSUS servers for patching during OSD.  These directories will certainly increase in size over time, as new content is distributed and new patches are downloaded.

There are, of course, many ways to monitor the overall free disk space, including WMI, SNMP, various 3rd party software, your corporate monitoring tool and even ConfigMgr itself.  But monitoring the ConfigMgr/WSUS directory sizes can be a little trickier.  Rather than purchase some new software to do it, I wanted to build a ‘free’ solution using tools already available to me in the Enterprise.  I also wanted to have all this data for all our distributions points displayed graphically in a report.

Since Microsoft Excel is my favourite reporting tool, I decided to build a workbook that has a data connection to a SQL database where I will store the disk and directory information from the DPs.  Then I run a PowerShell script on each of the DPs as a scheduled task that will get the information I need and insert it into the SQL database.

The result is a nice graphical report that displays all the data I want and keeps itself up-to-date automatically.  You can download a sample report here.

BigGraph

AvailableSpace

SCCMContentLib

Here is a step-by-step guide to create such a report.

Create a SQL Database and Table

First, we will create an SQL database where we will store this data.  You could, of course, use an existing database as we only need a single table to store this data, but just for good organisation I will create a new one for this purpose. Here is a sql script that will create the database, create a table, and create records for each of my distribution points with blank data values for now.  Edit the script with the hostnames of your distribution points, and run the script in your SQL instance.


CREATE DATABASE SCCM_Server_Data
GO
USE SCCM_Server_Data;
CREATE TABLE DiskSpace (
ID INT NOT NULL,
Server VARCHAR (20),
WSUS_Updates DECIMAL(6,2),
SMSPKGx DECIMAL(6,2),
SCCMContentLib DECIMAL(6,2),
DiskCapacity DECIMAL(6,2),
AvailableSpace DECIMAL(6,2),
PRIMARY KEY (ID)
);

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (1, 'SCCMDP-01', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (2, 'SCCMDP-02', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (3, 'SCCMDP-03', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (4, 'SCCMDP-04', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (5, 'SCCMDP-05', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (6, 'SCCMDP-06', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (7, 'SCCMDP-07', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (8, 'SCCMDP-08', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (9, 'SCCMDP-09', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (10, 'SCCMDP-10', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (11, 'SCCMDP-11', 0, 0, 0, 0, 0 );

SQL Permissions

I recommend to create an SQL login to the database you created that has only ‘db_datareader’ permission.  This way you can include the credentials required to get data from the database in the report, and not have to give SQL database permissions to everyone you want to share the report with.  If your SQL Instance is not using Mixed-mode authentication, you can easily change this.  I created a SQL login called ‘ConfigMgrDB_Read’ for this purpose (I use the same login to access my ConfigMgr database for reporting purposes).

Note: The SQL credentials will be stored in clear text in the Excel data connection file, but since they are read-only permission, it shouldn’t be a security concern.

Create a PowerShell Script and Scheduled Task on each DP

Next, we need to create a PowerShell script that will get the directory sizes of the following locations:

  • SCCMContentLib (SCCM Content Library)
  • SMSPKGx$ (SCCM Legacy Package Share)
  • WSUS_Updates (WSUS Update files location)

We will also get the disk capacity and amount of free space remaining.

We will put this data into a simple formatted text table that can be sent in an email.  We will then create a connection to the SQL database where we will store this data, and update the records with the new data values.

I recommend to run this script as a scheduled task on each DP, rather than running it against multiple DPs from one location, especially if your DPs are across a WAN, as the Get-ChildItem commands run much quicker locally than remotely.

The account running this script must have permission to input data to the SQL database.

You will need to change some things in the script for your environment:

  • Line 4: Enter the hostname of the server you are running the script on and for
  • Lines 7-11: Enter the email details
  • Lines 16,19,22: Enter the locations of the directories you want to get the size for
  • Line 25: Change the drive letter for your DP data drive
  • Lines 47,48: Enter the SQL Instance and database name

## Variables

# Enter ComputerName
$ComputerName = "SCCMDP-01"

# Enter email details
$SendEmail = "Yes"
$smtpServer = "mysmtpserver@mydomain.com"
$smtpFrom = "$ComputerName@mydomain.com"
$smtpTo = "MyRecipients@mydomain.com"
$messageSubject = "Drive Space Report for $ComputerName"

## Get and email the data

# Get drive and directory data
$colItems = (Get-ChildItem G:\WSUS_Updates -recurse | Measure-Object -property length -sum)
$WSUS_Updates = "{0:N2}" -f ($colItems.sum / 1GB)

$colItems = (Get-ChildItem G:\SMSPKGG$ -recurse | Measure-Object -property length -sum)
$SMSPKGG = "{0:N2}" -f ($colItems.sum / 1GB)

$colItems = (Get-ChildItem G:\SCCMContentLib -recurse | Measure-Object -property length -sum)
$SCCMContentLib = "{0:N2}" -f ($colItems.sum / 1GB)

$data = Get-WmiObject -Class Win32_Volume -ComputerName $ComputerName | Where-Object {$_.DriveLetter -eq 'G:'} | Select Capacity,FreeSpace,SystemName,DriveLetter

$capacity = "{0:N2}" -f ($data.capacity / 1GB)
$freespace = "{0:N2}" -f ($data.freespace / 1GB)
$systemname = $data.SystemName
$driveletter = $data.DriveLetter

# Add data to a simple text 'table'
$table = "All values in GB`nServer:`t`t`t$systemname`nWSUS_Updates:`t$WSUS_Updates`nSMSPKGx:`t`t$SMSPKGG`nSCCMContentLib:`t$SCCMContentLib`nDiskCapacity:`t`t$capacity`nAvailableSpace:`t`t$freespace"

# Send data as an email
if ($SendEmail -eq "Yes")
{
$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.Body = $table
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
}
# SQL Stuff

# Database info
$dataSource = “MySQLServer\INST_SCCM”
$database = “SCCM_Server_Data”

# Open a connection
cls
Write-host "Opening a connection to '$database' on '$dataSource'"
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Set and run the SQL Command
$update = "UPDATE DiskSpace SET WSUS_Updates = '$WSUS_Updates', SMSPKGx = '$SMSPKGG', SCCMContentLib = '$SCCMContentLib', DiskCapacity = '$capacity', AvailableSpace = '$freespace' WHERE SERVER = '$ComputerName'"
$command = $connection.CreateCommand()
$command.CommandText = $update
$command.ExecuteNonQuery()

# Close the connection
$connection.Close()

Note: if you don’t want to receive the email from each DP, simply put ‘No’ in the $SendEmail variable, although it’s a useful way to verify that the Scheduled Task has run.

Now create a scheduled task on the DP with the schedule of your choice and point to the location of your Powershell script:

  • Action: Start a program
  • Program/script: Powershell
  • Add arguments: -Command “C:\LocalScripts\DP-DiskSpaceScript.ps1” -ExecutionPolicy Bypass

You can manually start the task to verify that it works, you should receive an email like below, and data should be entered into the SQL table in the record for that server.  In this case, I now have lots of free space.  Nice 🙂

Capture

Create an Excel Workbook

Create the Data Connection

Open a new blank workbook in Microsoft Excel (I’m using Office 2013), right-click the tab for Sheet1 and rename it Data.

On the Data ribbon, in the Get External Data section, click From Other Sources and choose From SQL Server.

In the Data Connection Wizard, enter the name of the Instance that contains your database, and enter the SQL account credentials.  Click Next.

DataConWizard

Select the SCCM_Server_Data database from the drop-down list, then simply click next.  The database only contains one table, and we will pull the entire table into the workbook, so no need for a specific SQL query.

DataCon2

Rename the File Name and Friendly Name of the data connection file if you wish, then click Finish.

DataCon3

In the Import Data window, make sure to choose Table and the cell range =$A$1 in the existing worksheet, then click Ok.

DataCon4

You will now see the data from the database in the worksheet:

DataTab

Finally, go to the Data ribbon again and click Connections.  Make sure your data connection is selected, the click Properties.  Check the option to Refresh data when opening the file.  This will make sure that you always get the current data from the database every time the file is opened.

DataCon5

On the Definition tab, check the box Save password, click Yes at the prompt to save without encryption, then click OK to save and close the Connection Properties.

DataCon6

Close the Workbook Connections window.

Create a Summary Chart

Now that we have the data, let’s create some nice charts to display it.

First, select all the populated cells in the Data sheet, except for the ‘ID’ column, then right-click the Data tab and choose Insert…  Choose Chart and click Ok.

You should see a chart automatically added for you.  Let’s make it look a bit nicer.  (These customizations are just my own suggestions, of course.  Do what you like :-))

SummaryChart1

Rename the Chart tab to Disk Space Summary.

Click on the Chart, then choose Change Chart Type from the Chart ribbon.  In the Column charts, select 3D Column and click Ok.

Double-click the chart title and change it to SCCM DP Disk Space Summary

Cool, looking better to my eye.

SummaryChart2

Create Additional Charts

Now let’s create some additional charts so we can compare the data on their own scales.

Go back to the Data sheet and select all the populated cells in the Server column, and all the populated cells in the WSUSUpdates column, including the headers.

WSUSUPdates

Right-click on the Data tab and choose Insert…  Choose Chart and click Ok.  Change the chart type again, this time choosing 3D Clustered Column.

Rename the chart tab to WSUSUpdates.

Job done.

WSUSUPdates2

Now do the same for each of the remaining data columns in the Data sheet, selecting the populated cells in the Server column, and the next data column, and adding charts.

There you have it.  Now you can keep your eye on those SCCM DPs, know the sizes of the key content directories, observe how they are growing over time, and make sure there is plenty of free disk space available!

Note: you could also create an SSRS report, if you prefer, with the SQL data and add the report to your existing ConfigMgr reports folder.

!Tip

If you want the most up-to-date data in your report, then you can manually trigger all the scheduled tasks to run immediately using PowerShell.  If you have PowerShell remoting available, you can run the following script which will trigger the Task on all your distribution points.

You need to enter your DP names in the $server variable, and it assumes that your scheduled tasks all have the same name on each DP.


<#

This script manually triggers the 'DriveSpaceReport' Scheduled Task on each of the DPs listed in the $servers variable

#>

$servers = "sccmsrvdp-01", `
"sccmsrvdp-02, `
"sccmsrvdp-03", `
"sccmsrvdp-04", `
"sccmsrvdp-05", `
"sccmsrvdp-06", `
"sccmsrvdp-07", `
"sccmsrvdp-08", `
"sccmsrvdp-09", `
"sccmsrvdp-10", `
"sccmsrvdp-11", `
"sccmsrvdp-12"

cls
write-host "######################################################"
write-host "Trigger the DriveSpaceReport Scheduled Task on all DPs"
write-host "######################################################"
write-host ""

# Trigger the Scheduled tasks

write-host "Get task status and trigger the task for each server" -ForegroundColor Green
write-host ""

foreach ($Server in $servers)
{
write-host "Processing $server" -ForegroundColor Yellow
Write-Host "Getting Scheduled Task Details for 'DriveSpaceReport'" -ForegroundColor Cyan
schtasks /query /S $server /TN DriveSpaceReport /FO List
Write-Host ""
Write-Host "Run the task..." -ForegroundColor Cyan
schtasks /run /S $server /TN DriveSpaceReport /I
Write-Host ""
}

# Report task status

Write-Host ""
write-host "Get updated task status for each server" -ForegroundColor Green

foreach ($Server in $servers)
{
Write-Host ""
$Task = schtasks /query /S $server /TN DriveSpaceReport /V /FO CSV | ConvertFrom-Csv
$Name = $Task.TaskName
$Status = $Task.Status
$LastRunTime = $Task.'Last Run Time'
$Result = $Task.'Last Result'
Write-host $server -ForegroundColor Cyan
Write-host "TaskName: $Name"
Write-host "Status: $Status"
write-host "Last Run Time: $LastRunTime"
Write-host "Last Result: $Result"
}

Querying ConfigMgr – WMI vs SQL

There is more than one way to get data from ConfigMgr.  Some data you can get directly from the ConfigMgr Console.  But if you want more detailed and customised information you usually need to go a bit deeper, for example by using the ConfigMgr PowerShell cmdlets, querying WMI with PowerShell or some other tool, or accessing the ConfigMgr database directly.

But the method you choose can make a big difference in how quickly you can return results. For example, I wanted to find out what collections a particular device is a member of. Unfortunately, this is not possible natively with the ConfigMgr Console, unless you have a custom extension.  So I turned to WMI.  I customised a little PowerShell script written by David O’Brien so it could run on my local machine and query the WMI on the ConfigMgr site server:


<#

This script gets the collection membership of a device using a remote WMI Query
Enter your site server name, and run on your local machine.

#>

# Get Start Time
$startDTM = (Get-Date)

$SiteServer = "mysiteserver"

write-host "Get Collection Membership for ConfigMgr Device"
$ComputerName = Read-host "Enter the computername to check"
$s = New-PSSession -ComputerName $SiteServer
Invoke-Command -Session $s -Argu $ComputerName -ScriptBlock `
{
param ($ComputerName)
Import-Module 'C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1'

$SiteCode = (Get-CMSite | Select SiteCode).SiteCode
$drive = $SiteCode + ':'
cd $drive

$ResID = (Get-CMDevice -Name $ComputerName).ResourceID

$ColRemote = @()
$Collections = (Get-WmiObject -Class sms_fullcollectionmembership -Namespace root\sms\site_$SiteCode -Filter "ResourceID = '$($ResID)'").CollectionID
foreach ($Collection in $Collections)
{
$Col = Get-CMDeviceCollection -CollectionId $Collection | select Name, CollectionID
$ColRemote += $Col
}
}

$ColLocal = Invoke-Command -Session $s -ScriptBlock { $ColRemote }
Remove-PSSession $s
$Count = $ColLocal.Count
$ColLocal = $ColLocal | Select Name,CollectionID | Sort Name
$ColLocal | Out-GridView -Title "Collection Membership for $ComputerName ($count Collections)"

# Get End Time
$endDTM = (Get-Date)

# Echo Time elapsed
"Elapsed Time: $(($endDTM-$startDTM).totalseconds) seconds"

But this is very slow to return results:

time1

How about if I run the script on the site server itself?  Well first I’ll need to change the script a bit:


<#

This script gets the collection membership of a device using a local WMI Query
Run on the site server itself
#>

# Get Start Time
$startDTM = (Get-Date)

write-host "Get Collection Membership for ConfigMgr Device"

$ComputerName = Read-host "Enter the computername to check"

$SiteCode = (Get-CMSite | Select SiteCode).SiteCode

$ResID = (Get-CMDevice -Name $ComputerName).ResourceID
$ColLocal = @()
$Collections = (Get-WmiObject -Class sms_fullcollectionmembership -Namespace root\sms\site_$SiteCode -Filter "ResourceID = '$($ResID)'").CollectionID
foreach ($Collection in $Collections)
{
$Col = Get-CMDeviceCollection -CollectionId $Collection | select Name, CollectionID
$ColLocal += $Col
}

$Count = $ColLocal.Count
$ColLocal = $ColLocal | Select Name,CollectionID | Sort Name
$ColLocal | Out-GridView -Title "Collection Membership for $ComputerName ($count Collections)"

# Get End Time
$endDTM = (Get-Date)

# Echo Time elapsed
"Elapsed Time: $(($endDTM-$startDTM).totalseconds) seconds"

That definitely quicker, but still a bit slow:

time2What about accessing the ConfigMgr database?  Well if I run the following SQL query in the SSMS I get almost instant results:


Select col.CollectionName
,col.SiteID as 'Collection ID'
from vCollectionMembers cmb
inner join vCollections col on cmb.CollectionID = col.CollectionID
where cmb.Name = 'PC001' ORDER BY CollectionName

But rather than log into the SQL server, I’d prefer to run a PowerShell script to get the data.  So let’s use this script to query the ConfigMgr database, and get the same results as the previous scripts:


<#

This script gets the collection membership of a device using a remote SQL Query
Enter the SQL server name and instance, and the database name
Choose Windows or SQL authentication (enter the SQL credentials in the script)
#>

# Get Start Time
$startDTM = (Get-Date)

write-host "Get Collection Membership for ConfigMgr Device"
$ComputerName = read-host "Enter computername to check"

# Database info
$dataSource = “mysqlserver\INST_SCCM”
$database = “CM_ABC”

# Open a connection
cls
Write-host "Opening a connection to '$database' on '$dataSource'"
# Using windows authentication, or..
#$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
# Using SQL authentication
$connectionString = "Server=$dataSource;Database=$database;uid=ConfigMgrDB_Read;pwd=PaSSw0rd;Integrated Security=false"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Getting Software Updates Compliance Data
Write-host "Running query..."

$query = "Select col.CollectionName `
,col.SiteID as 'Collection ID' `
from vCollectionMembers cmb `
inner join vCollections col on cmb.CollectionID = col.CollectionID `
where cmb.Name = '$ComputerName' ORDER BY CollectionName"

$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)
$Count = $table.Rows.Count
$table | Out-GridView -Title "Collection Membership on $ComputerName ($count Collections)"

# Close the connection
$connection.Close()

# Get End Time
$endDTM = (Get-Date)

# Echo Time elapsed
"Elapsed Time: $(($endDTM-$startDTM).totalseconds) seconds"

Wow, that’s much quicker!

time3

Given that my ConfigMgr site server and database server are both in the same remote location, that’s pretty impressive!  So getting data from the SQL database directly is the quickest way to query ConfigMgr, which is good to know for scripting purposes at least.

 

Monitor OS Deployments with PowerShell

If you have access to your ConfigMgr database, you have access to a wealth of information, including the detailed steps of your OS deployments.

Below is a PowerShell script that will query the ConfigMgr database and return the detailed steps of your deployments.  You can specify a particular machine that is being or has been deployed, the length of time in which to return data, and whether to output the data into PowerShell’s Gridview, CSV, or both.  You can also run this for any task sequence, not just OS deployments.

For currently running deployments, the script will get you a ‘snapshot’ of the current deployment progress.  To update it, simply run the script again.  In a coming blog post, I will show you how to monitor deployments in near real-time using the ConfigMgr database.

The TS steps are returned with the most recently completed step at the top, so you can easily see where the deployment is at present.  The ActionOutput column contains a snippet of the deployment logs for that step, giving more detailed information about what was carried out. To look for any TS steps that failed, simply filter the ExitCode column for any value other than 0.

grid1

csv

To run the script, enter the following parameters:

  • $TS – The name of your OS Deployment (or other) task sequence
  • $ComputerName – Enter the computername for a computer already known to ConfigMgr (eg for rebuilds), enter ‘Unknown’ for bare-metal deployments, or leave blank to get all deployments
  • $SearchTimeInHours – The length of time in hours in which to return data, eg ’24’ gets all data up to 24 hours ago
  • $CSV – Enter Yes to return the data into a csv file
  • $Grid – Enter Yes to return the data into PowerShell’s Gridview
  • $datasource – The name of the SQL Server and Instance hosting your ConfigMgr database
  • $database – Your ConfigMgr database name

You must have at least ‘db_datareader’ access to the ConfigMgr database to run the script.


<#

This script will get the detailed steps carried out by a Task Sequence from the ConfigMgr database

#>
$TS = "Windows OS Deployment x64" # Enter the name of the Task Sequence you wish to monitor
$ComputerName = "unknown" # Leave blank to query for all deployments, enter 'Unknown' for a bare-metal deployment, or enter the computer name for a known device
$SearchTimeInHours = 24 # Enter the length of time in hours to return past data, eg '24' means all data until 24 hours ago
$CSV = "No" # Yes or No
$Grid = "Yes" # Yes or No

# Database info
$dataSource = “mysqlserver\INST_SCCM”
$database = “CM_ABC”

# Open a connection
cls
Write-host "Opening a connection to '$database' on '$dataSource'"
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Run SQL query
Write-host "Running query..."

If ($ComputerName)
{
$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 = '$TS' `
and sys.Name0 = '$ComputerName') `
and DATEDIFF(hour,ExecutionTime,GETDATE()) < '$SearchTimeInHours' `
ORDER BY Step Desc"
}
Else
{
$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 = '$TS' `
and DATEDIFF(hour,ExecutionTime,GETDATE()) < '$SearchTimeInHours' `
ORDER BY Step Desc"
}

$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

# Output data
$table = new-object “System.Data.DataTable”
$table.Load($result)

if ($CSV -eq "Yes")
{
$Date = Get-Date -Format HH-mm--dd-MMM-yy
$Path = "C:\Script_Files\$TS-$Date.csv"
$table | Export-Csv -Path $Path
Invoke-Item -Path $Path
}
If ($Grid -eq "Yes")
{
$table | Out-GridView -Title "$TS on $ComputerName"
}

# Close the connection
$connection.Close()

 

Changing the Collection Update Type for Multiple Collections in ConfigMgr

While waiting for a task sequence to run on a newly deployed computer, I found that the collection I had deployed the task sequence to had not updated even though I added the computer to the collection.  After some investigation, I discovered I had exceeded the recommended threshold for the number of collections that can be enabled for incremental updates, and this was obviously causing some problems with the collection membership evaluation.  David O’Brien has a great post describing this issue in more detail here.

I identified a number of collections that were enabled for incremental updates that didn’t really need to be, they were just enabled for that by default when the collections were created.  These collections were all nested under different subfolders created to organise my different geographical sites, under a single parent folder in the Device Collections node.

So I wrote a PowerShell script that will identify all the collections contained underneath the parent folder, including all subfolders up to 3 levels deep, and change the collection refresh type to ‘Full Scheduled Update only’ instead of using the incremental updates.

To use the script, simply enter your Site Code, the name of the parent folder, and which refresh type you want to set on all those collections.


<#

This script changes the Refresh Type for all collections found nested underneath a specific collection folder, up to 3 levels deep

#>

$SiteCode = "ABC"
$ParentFolderName = "Infrastructure"
$NewTypeCode = "2"
 # For NewTypeCode, enter:
 # 1, for No Scheduled Update, ie manual updates only
 # 2, for Full Scheduled Update Only, using default weeekly schedule if not previously enabled
 # 4, for Incremental Update Only
 # 6, for Incremental and Full Scheduled Update

# Empty the arrays in case script is run more than once in same session
$Collections = $null
$Collections1 = $null
$Collections2 = $null
$Collections3 = $null

if ($NewTypeCode -eq "1")
{$NewType = "No Scheduled Update"}
if ($NewTypeCode -eq "2")
{$NewType = "Full Scheduled Update Only"}
if ($NewTypeCode -eq "4")
{$NewType = "Incremental Update Only"}
if ($NewTypeCode -eq "6")
{$NewType = "Incremental and Full Scheduled Update"}
cls

# Get Parent FolderID
Write-Host "Getting Folder ID of Folder '$ParentFolderName'"
$ParentFolderID = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_ObjectContainerNode where Name='$ParentFolderName'" | Select ContainerNodeID
$ParentFolderID = $ParentFolderID.ContainerNodeID

# Get Parent Folder Collections
Write-Host "Getting Collections from '$ParentFolderName'"
$CollectionsInParentFolder = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_Collection where CollectionID is
in(select InstanceKey from SMS_ObjectContainerItem where ObjectType='5000'
and ContainerNodeID='$ParentFolderID') and CollectionType='2'"
$Collections = $CollectionsInParentFolder.Name

#Get Child Folder Names Level 1
$CF1 = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_ObjectContainerNode where ParentContainerNodeID='$ParentFolderID'" | Select Name,ContainerNodeID
$CF1FolderID = $CF1.ContainerNodeID
$CF1FolderName = $CF1.Name
if ($CF1FolderName -ne $null)
{
write-host " Found subfolders: "$CF1FolderName -ForegroundColor Yellow
}

# Get Child Folder 1 Collections
foreach ($CF in $CF1)
{
$CF1FolderID = $CF.ContainerNodeID
$CF1FolderName = $CF.Name
Write-Host " Getting Collections from '$CF1FolderName'" -ForegroundColor Yellow
$CollectionsInCF1 = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_Collection where CollectionID is
in(select InstanceKey from SMS_ObjectContainerItem where ObjectType='5000'
and ContainerNodeID='$CF1FolderID') and CollectionType='2'"
$CollectionsCF1 = $CollectionsInCF1.Name
$Collections1 += $CollectionsCF1

#Get Child Folder Names Level 2
 $CF2 = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
 -Query "select * from SMS_ObjectContainerNode where ParentContainerNodeID='$CF1FolderID'" | Select Name,ContainerNodeID
 $CF2FolderID = $CF2.ContainerNodeID
 $CF2FolderName = $CF2.Name
 if ($CF2FolderName -ne $null)
 {
 write-host " Found subfolders: "$CF2FolderName -ForegroundColor Yellow
 }

 # Get Child Folder 2 Collections
 foreach ($CF in $CF2)
 {
 $CF2FolderID = $CF.ContainerNodeID
 $CF2FolderName = $CF.Name
 Write-Host " Getting Collections from '$CF2FolderName'" -ForegroundColor Yellow
 $CollectionsInCF2 = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
 -Query "select * from SMS_Collection where CollectionID is
 in(select InstanceKey from SMS_ObjectContainerItem where ObjectType='5000'
 and ContainerNodeID='$CF2FolderID') and CollectionType='2'"
 $CollectionsCF2 = $CollectionsInCF2.Name
 $Collections2 += $CollectionsCF2

 #Get Child Folder Names Level 3
 $CF3 = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
 -Query "select * from SMS_ObjectContainerNode where ParentContainerNodeID='$CF2FolderID'" | Select Name,ContainerNodeID
 $CF3FolderID = $CF3.ContainerNodeID
 $CF3FolderName = $CF3.Name
 if ($CF3FolderName -ne $null)
 {
 write-host " Found subfolders: "$CF3FolderName -ForegroundColor Green
 }

 # Get Child Folder 3 Collections
 foreach ($CF in $CF3)
 {
 $CF3FolderID = $CF.ContainerNodeID
 $CF3FolderName = $CF.Name
 Write-Host " Getting Collections from '$CF3FolderName'" -ForegroundColor Green
 $CollectionsInCF3 = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
 -Query "select * from SMS_Collection where CollectionID is
 in(select InstanceKey from SMS_ObjectContainerItem where ObjectType='5000'
 and ContainerNodeID='$CF3FolderID') and CollectionType='2'"
 $CollectionsCF3 = $CollectionsInCF3.Name
 $Collections3 += $CollectionsCF3
 }
 }
}
$Collections += $Collections1 += $Collections2 += $Collections3
$Collections = $Collections | Sort
$Count = $Collections.Count
write-host "Found $Count collections"

Write-host "Changing the Refresh Type for all collections!" -ForegroundColor Magenta
foreach ($Collection in $Collections){
$Coll = Get-WmiObject -Class SMS_Collection -Namespace root\sms\site_$SiteCode -Filter "Name ='$($Collection)'"
$Coll.Name
if ($Coll.RefreshType -eq 1)
{$Type = "No Scheduled Update Set"}
elseif ($Coll.RefreshType -eq 2)
{$Type = "Full Scheduled Update Only"}
elseif ($Coll.RefreshType -eq 4)
{$Type = "Incremental Update Only"}
elseif ($Coll.RefreshType -eq 6)
{$Type = "Incremental and Full Scheduled Update"}
else {$Type = "Unknown"}
write-host " Currently: $Type" -ForegroundColor DarkCyan
Write-host " Changing to: $NewType" -ForegroundColor Cyan

$Coll = [wmi]$Coll.__PATH
$Coll.RefreshType = $NewTypeCode
$Coll.put() | Out-Null
}