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"