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.
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"
