[OpsMgr] T-SQL to get all alerts related to Windows Computer and all hosted objects

Posted: September 22, 2011 in SCOM, SCOM 2007

just in case you need to grab all active SCOM alerts related only to single windows computer and all hosted objects(e.g. IIS server, SQL server, etc) directly from OperationsManager DB.
Use this script

DECLARE @LanguageCode1 VARCHAR(3) 
DECLARE @ManagedEntityId NVARCHAR(MAX) 
DECLARE @LastModified DATETIME 
DECLARE @ResolutionState0 NVARCHAR(MAX) 

SET @LanguageCode1='ENU' 
--GRAB ID FROM SECOND SCRIPT
SET @ManagedEntityId=N'74be7cbd-2c72-8d5e-2f48-49583ae4dd30' 
SET @LastModified=Getutcdate() - 7 
SET @ResolutionState0=N'0' 

SELECT * 
FROM   dbo.Fn_alertviewchanges(@LanguageCode1, NULL, @LastModified, 
       @LastModified, 
              @LastModified) AS alertview 
       LEFT OUTER JOIN dbo.mt_computer 
         ON alertview.toplevelhostentityid = mt_computer.basemanagedentityid 
WHERE  (( alertview.[ResolutionState] = @ResolutionState0 )) 
       AND ((( alertview.[MonitoringObjectId] IN (SELECT f.[BaseManagedEntityId] 
                                                  FROM 
                       dbo.Fn_containedbaseentitiesfromlist(@ManagedEntityId, 0) 
                       AS f) 
             )))

where BaseManagedEntitiId is one of the lines from output of this script:

SELECT * FROM [OperationsManager].[dbo].[BaseManagedEntity]
Advertisements

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