Straight to the code: https://github.com/cole-mcdonald/SCOM-GhostedObjects

!! Warning, backup your Operation Manager DB before aggressive changes like this script performs. It worked for me, I can't guarantee it'll work for you !!

I've been seeing quite a few manually installed agents disappearing from our agent managed entities list in SCOM, even though they still show up in the "Windows Computer" state view under monitoring. After beating my head against all of the normal troubleshooting, we ended up with a premiere ticket with Microsoft. Hindsight web searches show this to be a relatively common occurrence. Only one way to address this in my world... PowerShell.

My tasks:

Get list of "Microsoft.Windows.Computer" objects from SCOM Remove any returned from Get-SCOMAgent Remove any SCOM Management or Gateway servers Verify their status in SQL Mark as to be deleted in SQL by GUID Activate DB Grooming Stored Procedure. Blog about it, because it saved me a ton of time.

Getting a list of objects is done using the Get-SCOMClassInstance (MANTRA: an object is an instance of a class). Using a Where-Object clause, limit the return to only those whose fullname property starts with "Microsoft.Windows.Computer". Sort it to make it easier to read through the results.

$instances = Get-SCOMClassInstances | Where-Object fullname -like "Microsoft.Windows.Computer*" | Sort-Object displayname

Getting the agents is just as simple:

$agents = Get-SCOMAgent

Since the Management Servers don't show in the Agent Managed lists, but do discover as Windows Computers, we remove them from consideration.

$managementServers = Get-SCOMManagementServer

Last part of the discovery is to pop them into an Out-GridView -passthru so the user can select which to remove and review them. This can easily be commented out if you just want them to process without review. I use Out-GridView in most of my scripts for doing bulk SCOM remediations. It allows addressing individual machines or entire environments from a single script with no extra parameterization needed.

Since we'll be addressing the SQL DB, we need to have a way to do so. I've chosen to use the SQLPS module... but it doesn't live anywhere that SQL isn't installed. So I'm cheating it into the current session using Import-PSSession to grab it from the SQL Server. I'll need to know the server\instance shortly anyway, so I grab that from the registry entry since we're running from the Management Server. Splitting on the backslash returns a small array with the server and instance name separated (don't forget to escape your escape characters):

$OpsMgrDB = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup').DatabaseServername -split "\\"

From there, the SQL invocation is fairly straight forward. You need an instance, a DB, and a Query. $OpsMgrDB[0] holds the server and $OpsMgrDB[1] holds the instance name.

$r_verify = Invoke-Sqlcmd -ServerInstance "$($OpsMgrDB[0])\$($OpsMgrDB[1])" -Database "OperationsManager" -Query $q_verify

I preface all of my query variable names with q_ and my returns with r_. Here are the 3 queries:

$q_verify = "select * from MT_HealthService where isagent=1 and DisplayName = '$($ghost.displayName)';"

$q_delete = @"

-- Mark for removal

DECLARE @EntityId uniqueidentifier;

DECLARE @TimeGenerated datetime;

-- change "GUID" to the ID of the invalid entity

SET @EntityId = '$($ghost.id)';

SET @TimeGenerated = getutcdate();

-- Execute the transaction

BEGIN TRANSACTION

EXEC dbo.p_TypedManagedEntityDelete @EntityId, @TimeGenerated;

COMMIT TRANSACTION

"@

$q_groom = @"

-- Groom Once marked for removal

DECLARE @GroomingThresholdUTC datetime

DECLARE @BatchSize int

SET @GroomingThresholdUTC = getutcdate()

SET @Batchsize = 250

-- Execute the transaction

BEGIN TRANSACTION

EXEC dbo.p_DiscoveryDataPurgingByTypedManagedEntityInBatches @GroomingThresholdUTC, @BatchSize

EXEC dbo.p_DiscoveryDataPurgingByRelationshipInBatches @GroomingThresholdUTC, @BatchSize

EXEC dbo.p_DiscoveryDataPurgingByBaseManagedEntityInBatches @GroomingThresholdUTC, @BatchSize

COMMIT TRANSACTION

"@

Kevin Holman has a great post on the steps leading up to this admittedly aggressive step: https://blogs.technet.microsoft.com/kevinholman/2008/09/29/agent-pending-actions-can-get-out-of-synch-between-the-console-and-the-database/

------------------------------------------------------------------------

Did you find this article useful? Let me know at cole.mcdonald@beyondimpactllc.com

If you want to be kept informed, follow our RSS feed: http://blog.beyondimpactllc.com/blog/rss.xml

Learn more about PowerShell in Azure

Beyond Impact is a Cloud Hosting and Managed Services provider based in Minneapolis, Minnesota.

You can learn more about our Cloud Services at beyondimpactllc.com/azure-services/.