none
Report: SQL Help: All alerts > 3 days old, Still in NEW (0) State

    Question

  • This is what I have so far for my report:

    SELECT   Alert.vAlert.AlertName AS [Alert Name], ManagedEntity.DisplayName AS System, Alert.vAlert.RaisedDateTime AS Raised, Alert.vAlert.Priority, Alert.vAlert.Severity,
               Alert.vAlertResolutionState.TimeFromRaisedSeconds, Alert.vAlertResolutionState.ResolutionState AS [Resolution State], Alert.vAlert.DWLastModifiedDateTime
    FROM     ManagedEntity CROSS JOIN
               Alert.vAlertResolutionState CROSS JOIN
               Alert.vAlert
    WHERE   (Alert.vAlertResolutionState.TimeFromRaisedSeconds > 259200) AND (Alert.vAlertResolutionState.ResolutionState = 0)

     

    The problem is that it has 50k+ rows and looking at SCOM, I only have 1 alert that fits this criteria.  Looking at the SQL output, it is showing alerts that have been clearly removed/closed already.  Any thoughts?

    Friday, May 28, 2010 12:59 AM

Answers

  • Hi

    The joins themselves are fine but I've just done some investigation and interestingly, the ResolutionState of an alert is not updated when an alert is closed - instead a new entry is created for the alert with a resolution state of 255 and a TimeInStateSeconds of -1 is set. You'll see if you run the following query against the DW that you get "pairs" of alerts .. one with alert resolutionstate of 255 an one with 0. 

    select * from Alert.vAlertResolutionState order by AlertGuid

    Basically, we'll need to look for another way to pull this data as we can't rely on the resolution state pulled via this view. I'm onsite for the rest of this week so won't have much time to look at it so hopefully someone else might be able to throw a query this way.

    Cheers

    Graham


    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/
    Tuesday, June 1, 2010 8:59 AM
    Moderator
  • Yep - if you run this against the DW you'll see what I mean:

    select * from Alert.vAlertResolutionState order by AlertGuid

    Here are an example pairing - the alert ID is the same, the resolution state is the second field (0 in the first line, 255 in the second) with TimeInState and TimeFromRaised being the next 2 fields. TimeInState is -1 for the resolved alert.  

    C4DC5CB5-7CFC-4E4F-8ABC-65076E357CF8 0      54  0   2010-01-10 18:22:34.363 System 2010-01-10 18:25:37.003
    C4DC5CB5-7CFC-4E4F-8ABC-65076E357CF8 255   -1 54  2010-01-10 18:23:28.640 WESTSIDE\Administrator 2010-01-10 18:25:41.087

    This isn't something that I had noticed before and I'll try and get some info during the week to explain this in more detail.

    Cheers

    Graham


    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/
    Tuesday, June 1, 2010 3:03 PM
    Moderator

All replies

  • Hi

    Not sure why you are using Cross Joins as these do a cartesian product of all the tables involved in the query - hence you are getting so many results returned. Try the following :

    use OperationsManagerDW
    go


    SELECT   Alert.vAlert.AlertName, dbo.vManagedEntity.DisplayName, Alert.vAlert.RaisedDateTime, Alert.vAlert.Priority, Alert.vAlert.Severity,
               Alert.vAlertResolutionState.TimeFromRaisedSeconds, Alert.vAlertResolutionState.ResolutionState AS [Resolution State], Alert.vAlert.DWLastModifiedDateTime

    FROM     Alert.vAlert Inner Join dbo.vManagedEntity
     On vAlert.ManagedEntityRowId = dbo.vManagedEntity.ManagedEntityRowId

    Inner Join Alert.vAlertResolutionState on Alert.vAlert.AlertGuid = Alert.vAlertResolutionState.AlertGuid
     
    WHERE   (Alert.vAlertResolutionState.TimeFromRaisedSeconds > 259200) AND (Alert.vAlertResolutionState.ResolutionState = 0)

    Let us know how you get on.

    Cheers

    Graham


    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/
    • Proposed as answer by Nicholas LiModerator Monday, May 31, 2010 8:53 AM
    • Marked as answer by dr-boolean2 Monday, May 31, 2010 11:52 PM
    • Unmarked as answer by dr-boolean2 Tuesday, June 1, 2010 2:35 AM
    • Unproposed as answer by dr-boolean2 Tuesday, June 1, 2010 2:41 AM
    Friday, May 28, 2010 6:59 AM
    Moderator
  • The correct data is not being pulled.  Looking at what is in the DW (also in the Monitoring space) the correct data is not being shown.  Assuming it is the joins.  Are you sure on those joins?
    Monday, May 31, 2010 11:52 PM
  • Hi

    The joins themselves are fine but I've just done some investigation and interestingly, the ResolutionState of an alert is not updated when an alert is closed - instead a new entry is created for the alert with a resolution state of 255 and a TimeInStateSeconds of -1 is set. You'll see if you run the following query against the DW that you get "pairs" of alerts .. one with alert resolutionstate of 255 an one with 0. 

    select * from Alert.vAlertResolutionState order by AlertGuid

    Basically, we'll need to look for another way to pull this data as we can't rely on the resolution state pulled via this view. I'm onsite for the rest of this week so won't have much time to look at it so hopefully someone else might be able to throw a query this way.

    Cheers

    Graham


    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/
    Tuesday, June 1, 2010 8:59 AM
    Moderator
  • Wow, okay, that is crazy.  Does the new record have the same ID for the alert?
    Tuesday, June 1, 2010 1:13 PM
  • Yep - if you run this against the DW you'll see what I mean:

    select * from Alert.vAlertResolutionState order by AlertGuid

    Here are an example pairing - the alert ID is the same, the resolution state is the second field (0 in the first line, 255 in the second) with TimeInState and TimeFromRaised being the next 2 fields. TimeInState is -1 for the resolved alert.  

    C4DC5CB5-7CFC-4E4F-8ABC-65076E357CF8 0      54  0   2010-01-10 18:22:34.363 System 2010-01-10 18:25:37.003
    C4DC5CB5-7CFC-4E4F-8ABC-65076E357CF8 255   -1 54  2010-01-10 18:23:28.640 WESTSIDE\Administrator 2010-01-10 18:25:41.087

    This isn't something that I had noticed before and I'll try and get some info during the week to explain this in more detail.

    Cheers

    Graham


    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/
    Tuesday, June 1, 2010 3:03 PM
    Moderator
  • I am seeing this in the OpsDB as well with the 'a' and 'ah' tables.  Working with Michael Pearson in this thread (http://social.technet.microsoft.com/Forums/en-US/operationsmanagergeneral/thread/b27915e6-6490-4e94-a2e0-36d85203ec50).  It came-up for a different reason as well.  A published schema document would really help for this...
    Monday, June 7, 2010 12:23 PM
  • Hi, I seen your post and have been trying been trying to get a similar report/SQL for our organisation (only I'm wanting alerts > 1day old still at resolution state NEW).  I thought this would have been pretty straight forward but like you the more I researched the more tricky it seemed able to get!

    Anyhow was just wondering if you got/developed a working script which I could maybe tweek for my criteria.

    Any help much appreciated!!

    Thursday, July 8, 2010 1:39 PM
  • We just extended the date of our DB groom to 60 (max. allowed) and that enabled us to just utilize the previous DB script vs. developing a join for the DB/DW.
    Friday, July 9, 2010 6:13 PM
  • Dr,

    Sorry, still a bit of a novice - did you just set the 'Resolved Alerts' to 60 (from 7) in database grooming settings on RMS?  Also is the SQL you use this:

    SELECT   Alert.vAlert.AlertName AS [Alert Name], ManagedEntity.DisplayName AS System, Alert.vAlert.RaisedDateTime AS Raised, Alert.vAlert.Priority, Alert.vAlert.Severity,
               Alert.vAlertResolutionState.TimeFromRaisedSeconds, Alert.vAlertResolutionState.ResolutionState AS [Resolution State], Alert.vAlert.DWLastModifiedDateTime
    FROM     ManagedEntity CROSS JOIN
               Alert.vAlertResolutionState CROSS JOIN
               Alert.vAlert
    WHERE   (Alert.vAlertResolutionState.TimeFromRaisedSeconds > 259200) AND (Alert.vAlertResolutionState.ResolutionState = 0)

    If not could you paste in the correct one?...

    Many thanks

    Thursday, July 15, 2010 8:59 AM