Skip to content

OpsMgr 2012 – Useful SQL Queries

April 15, 2014

A post with some Operations Manager 2012 SQL queries i use from time to time:

Get manually installed agents:

select bme.DisplayName from MT_HealthService mths
INNER JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mths.BaseManagedEntityId
where IsManuallyInstalled = 1

Make all agents remotely manageable again:

UPDATE MT_HealthService
SET IsManuallyInstalled=0
WHERE IsManuallyInstalled=1

Get Management packs waiting for synchronisation with the Data Warehouse:

SELECT
ManagementPackId, MPFriendlyName,MPName, mp.MPVersionDependentId, MPLastModified, MPKeyToken, ContentReadable
FROM ManagementPack mp
WHERE MPVersionDependentId

NOT IN
(SELECT mpv.ManagementPackVersionDependentGuid
FROM OperationsMAnagerDW.dbo.ManagementPackVersion mpv
JOIN OperationsMAnagerDW.dbo.ManagementGroupManagementPackVersion mgmpv
ON (mpv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
WHERE (mgmpv.LatestVersionInd > 0))

AND NOT EXISTS
(SELECT * FROM ManagementPackReferences mpr
JOIN ManagementPack mpv
ON (mpr.ManagementPackIdSource = mpv.ManagementPackId)
WHERE (mpr.ManagementPackIdReffedBy = mp.ManagementPackId)
AND (mpv.MPVersionDependentId NOT IN
(SELECT mpv.ManagementPackVersionDependentGuid
FROM OperationsMAnagerDW.dbo.ManagementPackVersion mpv
JOIN OperationsMAnagerDW.dbo.ManagementGroupManagementPackVersion mgmpv
ON (mpv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
WHERE (mgmpv.LatestVersionInd > 0))))
Advertisements

From → System Center

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: