Operations Manager 2012 – Useful SQL Queries


How many times did that alert occur?


 

With the default reports this isn't possible to get. Run this one against the Data Warehouse.

 

SELECT  TOP (10000)  adt.Owner,

 

adt.TicketId,

 

alt.AlertName,

 

alt.AlertDescription,

 

alt.Severity,

 

alt.Priority,

 

alt.Category,

 

alt.RaisedDateTime,

 

alt.RepeatCount,

 

vManagedEntity.DisplayName,

 

vManagedEntity.Name,

 

vManagedEntity.Path

 

FROM Alert.vAlertResolutionState  AS  ars  INNER  JOIN

 

Alert.vAlertDetail  AS  adt  ON  ars.AlertGuid=adt.AlertGuid  INNER  JOIN

 

Alert.vAlert  AS  alt  ON  ars.AlertGuid=alt.AlertGuid  INNER  JOIN

 

vManagedEntity ON  alt.ManagedEntityRowId=vManagedEntity.ManagedEntityRowId

 

WHERE

 

alt.AlertName='<enter alertname here>'


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

 

 

** Note: there is a reason why agents cannot be edited from within the console when they are manually installed. One of them is when changing the primairy management server. When this action is performed all the management servers living in the All Management Servers resourcepool wille become failover servers. If an agent is installed to use a gateway this will cause problems...


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

 


Reactie schrijven

Commentaren: 0