Unlocking Locked Objects in the SCCM Console using Powershell

Have you ever come across a lovely message like this from the SCCM 2012 console?  In this case I wanted to open the properties of a package but somehow SCCM still has a handle on it that it won’t release.  This could be because someone else is currently modifying it, but in this case I only just created it so I know that’s not true.

console

The solution is to delete the record for the locked object in the SQL database.  You can do this from the SQL server, but hey, why not do it with PowerShell for a quicker fix?

This script will query the ConfgMgr SQL server database for locked objects, list them in a Grid, ask you which object you want to unlock, then delete the record from the database.

Note the script assumes you are using an account that has the necessary permissions in the SQL database.

First, we get the list of locked objects and put them into a grid view so we can filter and find the object we need in case there are more than one.

grid

If it’s something that you think was locked recently, you can sort by the ‘AssignmentTime’ column and find the latest one.  Or if you think it was done by a specific user account, you can sort by ‘AssignedUser’.

The first column contains the ID for that record in the database.  The script asks you to input this record ID, then it will delete the record:

ps

Don’t forget to add your SQL server and database name to the beginning of the script.


<#

This script unlocks an object that is locked in the SCCM console.

#>

# 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()

# Get the list of locked objects, output to Grid
Write-host "Getting the list of locked objects"
$query = "select * from SEDO_LockState where LockStateID <> 0"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)
$table | Out-GridView

# Prompt for the record ID to unlock
$ID = Read-Host ">>Enter the ID number of the record you want to unlock"

# Get the LockID of the object
Write-host "Getting the LockID of this object"
$query2 = "select LockID from SEDO_LockState where ID = '$($ID)'"
$command = $connection.CreateCommand()
$command.CommandText = $query2
$result = $command.ExecuteReader()

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

# Delete the object
Write-Host "Deleting the object record"
$query3 = "DELETE from SEDO_LockState where LockID = '$($table)'"
$command = $connection.CreateCommand()
$command.CommandText = $query3
$result = $command.ExecuteReader()

# Close the connection
$connection.Close()

More info here: http://myitforum.com/myitforumwp/2013/02/22/unlocking-configmgr-2012-objects/

SCCM Package Distribution Email Notification

In my last post, we looked at a way to monitor ConfigMgr Package distributions with PowerShell.  In this post, we’ll add an email notification to it.  Sometimes when you are distributing a large package to multiple distribution points, it’s handy to receive a notification that the distribution has been completed so you don’t have to keep checking the distribution status.

In this script, we do much the same as in the previous post, except we watch for a change in the distribution status in WMI to see when the distribution has completed.  Then we send an email.

Simply add your email details, sitecode, and the polling frequency into the script, then run it. Choose which active distribution you want to monitor, then you’ll get an email when the distribution to all the relevant distribution points has completed.

As I mentioned in my previous post, ConfigMgr does some post-processing before the actual distribution is started, so if you try to monitor a distribution immediately after you distributed it, you might see no results.  One way to check when it’s started is to search for ‘Created package transfer job to send package … to distribution point’ in the distmgr.log on the site server.

dp1


<#

This script monitors an active distribution that you choose and sends an email once the distribution to all DPs has completed.
Note: Run on the SCCM Site Server, and complete the variables first.

#>

## Complete the variables ##

$ToEmail = "trevor.jones@mycompany.com"
$FromEmail = "sccmsiteserver@mycompany.com"
$smtpServer = "emailserver.mycompany.com"
$SiteCode = "ABC"
# Poll frequency in seconds
$Seconds = "120"

## Start of script

Import-Module 'C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1'
$Drive = $SiteCode + ':'
cd $Drive

$Djobs = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_DistributionStatus -Filter "Type='2'" | Select Assets,PackageID

if ($djobs -eq $Null)
{
Write-host "There are no active distributions!" -ForegroundColor Yellow
Write-host "If you just distributed something, try again in a couple of minutes as the server may be processing the request."
write-host "Press any key"
$HOST.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown") | OUT-NULL
$HOST.UI.RawUI.Flushinputbuffer()
exit
}

Write-host ""
Write-host "The following distributions are active:"
write-host ""

foreach ($Djob in $Djobs)
{
$ID = $djob.PackageID
$assets = $djob.Assets
# Get PackageName and Type

$PKG = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_Package -Filter "PackageID='$ID'" | Select Name
If ($PKG -ne $null)
{
$Name = $PKG.Name
$Type = "Standard Package"
}

$BIPKG = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_BootImagePackage -Filter "PackageID='$ID'" | Select Name
If ($BIPKG -ne $null)
{
$Name = $BIPKG.Name
$Type = "Boot Image Package"
}

$CNPKG = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_ContentPackage -Filter "PackageID='$ID'" | Select Name
If ($CNPKG -ne $null)
{
$Name = $CNPKG.Name
$Type = "Application Content Package"
}

$DRVPKG = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_DriverPackage -Filter "PackageID='$ID'" | Select Name
If ($DRVPKG -ne $null)
{
$Name = $DRVPKG.Name
$Type = "Driver Package"
}

$IMGPKG = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_ImagePackage -Filter "PackageID='$ID'" | Select Name
If ($IMGPKG -ne $null)
{
$Name = $IMGPKG.Name
$Type = "Image Package"
}

$SUPKG = Get-WmiObject -Namespace root\SMS\Site_D1G -Class SMS_SoftwareUpdatesPackage -Filter "PackageID='$ID'" | Select Name
If ($SUPKG -ne $null)
{
$Name = $SUPKG.Name
$Type = "Software Updates Package"
}

Write-host " Package ID: $ID"
write-host " Package Name: $name"
write-host " Package Type: $Type"
write-host " Number of DPs: $Assets"
write-host ""
}

$PKG2Monitor = Read-host "Enter the Package ID of the distrbution you want to monitor"

write-host " "
write-host "Monitoring the distribution status of package $PKG2Monitor on all DPs. An email will be sent when complete." -ForegroundColor Yellow
write-host "Do not close this PowerShell window!" -ForegroundColor Yellow

while ((Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_DistributionStatus | where {($_.Type -eq "2" -or $_.Type -eq "4") -and $_.PackageID -eq "$PKG2Monitor"}).Type -eq '2' `
-or (Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_DistributionStatus | where {($_.Type -eq "2" -or $_.Type -eq "4") -and $_.PackageID -eq "$PKG2Monitor"}).Type -eq '4')
{
Start-Sleep -Seconds $Seconds
}

if ($PKG)
{$Class = "SMS_Package"}
if ($TSPKG)
{$Class = "SMS_TaskSequencePackage"}
if ($BIPKG)
{$Class = "SMS_BootImagePackage"}
if ($CNPKG)
{$Class = "SMS_ContentPackage"}
if ($DRVPKG)
{$Class = "SMS_DriverPackage"}
if ($IMGPKG)
{$Class = "SMS_ImagePackage"}
if ($SUPKG)
{$Class = "SMS_SoftwareUpdatesPackage"}

$NName = Get-WmiObject -Namespace root\SMS\Site_D1G -Class $Class -Filter "PackageID='$PKG2Monitor'" | Select Name
$NName = $NName.Name

send-mailmessage -To $ToEmail -From $FromEmail -Subject "Package distribution completed for $NName ($PKG2Monitor)" -body "The package distribution for $NName ($PKG2Monitor) has completed on all DPs." -smtpServer $smtpServer

SCCM Package Distribution Monitor

If you’ve installed the ConfigMgr 2012 R2 Toolkit, you’ve probably used the handy “Distribution Point Job Queue Manager” to monitor your distributions.  But you can also monitor them with Powershell if you wish to.  Here’s a simple script that will do just that.  It will identify the package ID, name and type, the distribution points that are currently receiving the distribution, when the distribution started, the total size of the package, the remaining size to be distributed, and overall progress of each individual distribution.

Note that ConfigMgr does some post-processing before the actual distribution is started, so if you try to monitor a distribution immediately after you distributed it, you might see no results.  One way to check when it’s started is to search for ‘Created package transfer job to send package … to distribution point’ in the distmgr.log on the site server.  Thinking about it, that could probably be scripted too, but that’s for another day 🙂

dp


## Variables ##
#!!Enter the sitecode here!!
$SiteCode = "ABC"

## Start of script

Import-Module 'C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1'
$Drive = $SiteCode + ':'
cd $Drive

cls
$Djobs = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_DistributionJob | Select PkgID,NALPath,StartTime,RemainingSize,TotalSize

if ($djobs -eq $Null)
{
Write-host "There are no active distributions!" -ForegroundColor Yellow
Write-host "If you just distributed something, try again in a couple of minutes as the server may be processing the request."
write-host "Press any key"
$HOST.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown") | OUT-NULL
$HOST.UI.RawUI.Flushinputbuffer()
exit
}

foreach ($Djob in $Djobs)
{
$ID = $djob.PkgID
# Get PackageName and Type

$PKG = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_Package -Filter "PackageID='$ID'" | Select Name
If ($PKG -ne $null)
{
$Name = $PKG.Name
$Type = "Standard Package"
}

$BIPKG = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_BootImagePackage -Filter "PackageID='$ID'" | Select Name
If ($BIPKG -ne $null)
{
$Name = $BIPKG.Name
$Type = "Boot Image Package"
}

$CNPKG = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_ContentPackage -Filter "PackageID='$ID'" | Select Name
If ($CNPKG -ne $null)
{
$Name = $CNPKG.Name
$Type = "Application Content Package"
}

$DRVPKG = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_DriverPackage -Filter "PackageID='$ID'" | Select Name
If ($DRVPKG -ne $null)
{
$Name = $DRVPKG.Name
$Type = "Driver Package"
}

$IMGPKG = Get-WmiObject -Namespace root\SMS\Site_$SiteCode -Class SMS_ImagePackage -Filter "PackageID='$ID'" | Select Name
If ($IMGPKG -ne $null)
{
$Name = $IMGPKG.Name
$Type = "Image Package"
}

$PkgID = $Djob.PkgID
$NALPath = $Djob.NALPath
if ($Djob.StartTime -ne $null)
{
$StartTime = [System.Management.ManagementDateTimeConverter]::ToDateTime($Djob.StartTime).DateTime
}
$RemainingSize = $Djob.RemainingSize
$TotalSize = $Djob.TotalSize
if ($RemainingSize -ne $Null)
{
$percentComplete = "{0:N0}" -f (100-$RemainingSize/$TotalSize*100) + " %"
}
Else
{
$percentComplete = "Not calculated yet"
}
$RemainingSize = "{0:N0}" -f ($RemainingSize / 1MB) + " MB"
$TotalSize = "{0:N0}" -f ($TotalSize / 1MB) + " MB"

write-host ""
write-host "Package Name: $Name" -ForegroundColor Cyan
write-host "Packge Type: $Type"
Write-host "Package ID: $PkgID"
Write-host "Distribution Point: $NALPath"
Write-host "Start Time: $StartTime"
write-host "Remaining Size: $RemainingSize"
write-host "Total Size: $TotalSize"
Write-host "Percent Complete: $percentComplete"
}