gold-star These were initially published by Daniele Grandini and have been a great tool for finding noisy discoveries.  Only small modifications to time window has been made.

/*Top discovery rules in the last 24 hours*/
USE OperationsManagerDW
SELECT ManagedEntityTypeSystemName, DiscoverySystemName, COUNT(*) AS 'Changes'
FROM  (SELECT DISTINCT
                              MP.ManagementPackSystemName, MET.ManagedEntityTypeSystemName, METP.PropertySystemName, D.DiscoverySystemName,
                              D.DiscoveryDefaultName, MET1.ManagedEntityTypeSystemName AS 'TargetTypeSystemName',
                              MET1.ManagedEntityTypeDefaultName AS 'TargetTypeDefaultName', ME.Path, ME.Name, C.OldValue, C.NewValue, C.ChangeDateTime
               FROM   vManagedEntityPropertyChange AS C INNER JOIN
                              vManagedEntity AS ME ON ME.ManagedEntityRowId = C.ManagedEntityRowId INNER JOIN
                              vManagedEntityTypeProperty AS METP ON METP.PropertyGuid = C.PropertyGuid INNER JOIN
                              vManagedEntityType AS MET ON MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId INNER JOIN
                              vManagementPack AS MP ON MP.ManagementPackRowId = MET.ManagementPackRowId INNER JOIN
                              vManagementPackVersion AS MPV ON MPV.ManagementPackRowId = MP.ManagementPackRowId LEFT OUTER JOIN
                              vDiscoveryManagementPackVersion AS DMP ON DMP.ManagementPackVersionRowId = MPV.ManagementPackVersionRowId AND
                              CAST(DMP.DefinitionXml.query('data(/Discovery/DiscoveryTypes/DiscoveryClass/@TypeID)') AS nvarchar(MAX))
                              LIKE '%' + MET.ManagedEntityTypeSystemName + '%' LEFT OUTER JOIN
                              vManagedEntityType AS MET1 ON MET1.ManagedEntityTypeRowId = DMP.TargetManagedEntityTypeRowId LEFT OUTER JOIN
                              vDiscovery AS D ON D.DiscoveryRowId = DMP.DiscoveryRowId
               WHERE (C.ChangeDateTime > DATEADD(hh, - 24, getutcdate()))) AS [#T]
GROUP BY ManagedEntityTypeSystemName, DiscoverySystemName
ORDER BY 'Changes' DESC

 

/*Discovered objects in the last 24 hours*/
USE OperationsManagerDW
SELECT DISTINCT
               MP.ManagementPackSystemName, MET.ManagedEntityTypeSystemName, D.DiscoverySystemName, D.DiscoveryDefaultName,
               MET1.ManagedEntityTypeSystemName AS 'TargetTypeSystemName', MET1.ManagedEntityTypeDefaultName AS 'TargetTypeDefaultName', ME.Path, ME.Name,
               ME.DWCreatedDateTime
FROM  vManagedEntity AS ME INNER JOIN
               vManagedEntityType AS MET ON MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId INNER JOIN
               vManagementPack AS MP ON MP.ManagementPackRowId = MET.ManagementPackRowId INNER JOIN
               vManagementPackVersion AS MPV ON MPV.ManagementPackRowId = MP.ManagementPackRowId LEFT OUTER JOIN
               vDiscoveryManagementPackVersion AS DMP ON DMP.ManagementPackVersionRowId = MPV.ManagementPackVersionRowId AND
               CAST(DMP.DefinitionXml.query('data(/Discovery/DiscoveryTypes/DiscoveryClass/@TypeID)') AS nvarchar(MAX))
               LIKE '%' + MET.ManagedEntityTypeSystemName + '%' LEFT OUTER JOIN
               vManagedEntityType AS MET1 ON MET1.ManagedEntityTypeRowId = DMP.TargetManagedEntityTypeRowId LEFT OUTER JOIN
               vDiscovery AS D ON D.DiscoveryRowId = DMP.DiscoveryRowId
WHERE (ME.DWCreatedDateTime > DATEADD(hh, - 24, getutcdate()))

 

Back to SQL queries main menu