DW query - alerts based on group

Dashboard Server
, ,
1

Hi,

I need a little help to constuct a query against the datawarehouse. I want to have a list with all alerts raised by an instances in specific groups.

Here’s what i have so far, getting alerts for a managed entity, which I thought should be somewhat the same for the groups

SELECT * FROM OperationsManagerDW.[Alert].[Alert_FCA18B0FCA7648CA96EA32E797BB59E5] AL JOIN OperationsManagerDW.[dbo].[ManagedEntity] ME ON ME.ManagedEntityRowId = AL.ManagedEntityRowId JOIN OperationsManagerDW.[dbo].[Relationship] RE ON ME.ManagedEntityRowId = RE.TargetManagedEntityRowId JOIN OperationsManagerDW.[dbo].[ManagedEntity] TME ON TME.TopLevelHostManagedEntityRowId = RE.SourceManagedEntityRowId WHERE TME.DisplayName LIKE '%scomweb-001'
 

Any suggestions, we are building reports in PowerBi for this :slight_smile:

2

Is there a specific reason to do this as a direct SQL query? This is a fairly simple task in powershell. I have a function I can blog about for recursively detailing instances contained by a group / subgroup set… I’d then loop through those instances and build up a tree (Host, instance, alert) for display/CSV export.

 

(Sorry about the repeated posts… Captcha wasn’t cooperating… but apparently it was working, just a feedback thing).

1 Like
3

I think the query you posted might be doing something strange by mixing relationships and top-level hosts.

Querying for this sort of information in the DW is harder than it seems, but here are a few queries that might help.

Note that best practice is to use the DW views rather than tables (for example, I think the alert table name will change over time and with management group).

Also note that the below queries only return alerts for objects hosted on servers (disks, OS etc), not alerts on the servers themselves (which are not common unless you have custom MPs). Including alerts for the servers is possible but complicates the queries further.

-- Alerts for objects hosted on a server
SELECT *
FROM OperationsManagerDW.Alert.vAlert AL
JOIN OperationsManagerDW.dbo.vManagedEntity ME ON ME.ManagedEntityRowId = AL.ManagedEntityRowId
JOIN OperationsManagerDW.dbo.vManagedEntity TME ON TME.ManagedEntityRowId = ME.TopLevelHostManagedEntityRowId
WHERE TME.DisplayName LIKE '%my_server%'
-- Alerts for a group (directly contained objects only)
SELECT *
FROM OperationsManagerDW.Alert.vAlert AL
JOIN OperationsManagerDW.dbo.vManagedEntity ME ON ME.ManagedEntityRowId = AL.ManagedEntityRowId
JOIN OperationsManagerDW.dbo.vManagedEntity TME ON TME.ManagedEntityRowId = ME.TopLevelHostManagedEntityRowId
JOIN OperationsManagerDW.dbo.vRelationship RE ON TME.ManagedEntityRowId = RE.TargetManagedEntityRowId
JOIN OperationsManagerDW.dbo.vManagedEntity GRP ON GRP.ManagedEntityRowId = RE.SourceManagedEntityRowId
WHERE GRP.DisplayName LIKE 'All Windows Computers'
-- Alerts for sub-groups (one level of sub group only)
SELECT *
FROM OperationsManagerDW.Alert.vAlert AL
JOIN OperationsManagerDW.dbo.vManagedEntity ME ON ME.ManagedEntityRowId = AL.ManagedEntityRowId
JOIN OperationsManagerDW.dbo.vManagedEntity TME ON TME.ManagedEntityRowId = ME.TopLevelHostManagedEntityRowId
JOIN OperationsManagerDW.dbo.vRelationship RE ON TME.ManagedEntityRowId = RE.TargetManagedEntityRowId
JOIN OperationsManagerDW.dbo.vManagedEntity SUB_GRP ON SUB_GRP.ManagedEntityRowId = RE.SourceManagedEntityRowId
JOIN OperationsManagerDW.dbo.vRelationship RE_GRP ON SUB_GRP.ManagedEntityRowId = RE_GRP.TargetManagedEntityRowId
JOIN OperationsManagerDW.dbo.vManagedEntity GRP ON GRP.ManagedEntityRowId = RE_GRP.SourceManagedEntityRowId
WHERE GRP.DisplayName LIKE 'top level group'

Hope this helps.

2 Likes
4

Here’s a function for recursively getting group members from SCOM using powershell. I use Backticks, be aware of them if you’re troubleshooting anything for your environment… they’re easy to overlook.

https://github.com/cole-mcdonald/Get-SCOMGroupMembers/blob/master/Get-SCOMGroupMembers.ps1

2 Likes
5

I wish I was good with SQL queries :frowning: However, Kevin Holman has a bunch of useful queries - https://blogs.technet.microsoft.com/kevinholman/2016/11/11/scom-sql-queries/

6

Hmmmm… the issue appears to be that the group i am trying to query for alerts only have subgroups. I will have to figure out a way to get all subgroups or something

7

Thank you very much! I managed to query all subgroups and create a custom view for that to use in PowerBi, but these will come in handy and answers the initial question.

8

Hi @me1,
I have a similar problem. Would you mind posting your solution to the community?
Thanks

9

@jh1 if you’re okay using the oltp, here’s what i use just replace your rgv.SourceObjectDisplayName with the group you want.

SELECT CASE
        WHEN Severity = 1
          THEN 'Warning'
        WHEN Severity = 2
          THEN 'Critical'
      END AS 'Alert Level' 
    , av.AlertStringName AS 'Alert Name'
    , CONCAT (
        av.MonitoringObjectPath
        , ' '
        , av.MonitoringObjectName
        ) AS 'Alerting Object'
    , av.TimeRaised AS 'Time Raised'
    , av.Id AS 'Alert Context'
FROM Alertview AS av WITH (NOLOCK)
INNER JOIN dbo.RelationshipGenericView rgv WITH (NOLOCK)
    ON av.TopLevelHostEntityId = rgv.TargetObjectId
WHERE TimeRaised IS NOT NULL
    AND av.ResolutionState = 0
    AND rgv.SourceObjectDisplayName = 'MSSQL on Windows: SQL Server Computers'
    AND av.MonitoringObjectFullName LIKE 'Microsoft.Windows.Computer%'
ORDER BY 'Alert Level', av.TimeRaised DESC;

EDIT: oh and skip/change the av.MonitoringObjectFullName where statement if you need to.