locked
SQL Query to show active alerts RRS feed

  • Question

  • Hi, i want to build a ASP page that shows a gridview with the current alerts in scom so we can see them clearly.

    What SQL query can get me the current alerts that are showing on Monitoring - Active Alerts, where severity is Critical?

    I use System Center 2012 SP1

    I checked this post

    http://social.technet.microsoft.com/Forums/systemcenter/en-US/898c6f62-9daf-493b-bfd9-193d1f42fc18/sql-query-to-view-current-active-alerts?forum=operationsmanagergeneral

    And this page:

    http://blogs.technet.com/b/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx

    But i can't see the correct query.

    I don't want to use Powershell as i want to display it properly in a formatted gridview.

    Thanks

    Tuesday, June 24, 2014 1:50 PM

Answers

  • This should give you active critical alerts which are not closed:

    use OperationsManager
    select * FROM dbo.AlertView
    where Severity like 2 AND ResolutionState like 0


    www.coretech.dk - blog.coretech.dk

    • Marked as answer by slowscripter Tuesday, June 24, 2014 2:51 PM
    Tuesday, June 24, 2014 2:29 PM
  • Hi,

    SELECT * FROM [OperationsManager].[dbo].[AlertView] where Severity = 2 and ResolutionState <> 255 and LanguageCode='ENU'

    Tuesday, June 24, 2014 2:33 PM

All replies

  • This should give you active critical alerts which are not closed:

    use OperationsManager
    select * FROM dbo.AlertView
    where Severity like 2 AND ResolutionState like 0


    www.coretech.dk - blog.coretech.dk

    • Marked as answer by slowscripter Tuesday, June 24, 2014 2:51 PM
    Tuesday, June 24, 2014 2:29 PM
  • Hi,

    SELECT * FROM [OperationsManager].[dbo].[AlertView] where Severity = 2 and ResolutionState <> 255 and LanguageCode='ENU'

    Tuesday, June 24, 2014 2:33 PM
  • Thanks!
    Tuesday, June 24, 2014 2:52 PM
  • Hi,

    SELECT * FROM [OperationsManager].[dbo].[AlertView] where Severity = 2 and ResolutionState <> 255 and LanguageCode='ENU'

    I want to report on all severity levels, but I'd like to see Warning instead of 1, and Critical instead of 2 and so on. Am I missing a field somewhere?

    Jeffrey S. Patton Jeffrey S. Patton Systems Specialist, Enterprise Systems University of Kansas 1001 Sunnyside Ave. Lawrence, KS. 66045 (785) 864-0242 | http://patton-tech.com

    Thursday, December 4, 2014 9:41 PM
  • Found my answer here

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e4a99a19-be1c-4110-b791-dedfa664aaf5/sql-query-if-then-else?forum=databasedesign

    using a case!


    Jeffrey S. Patton Jeffrey S. Patton Systems Specialist, Enterprise Systems University of Kansas 1001 Sunnyside Ave. Lawrence, KS. 66045 (785) 864-0242 | http://patton-tech.com

    Thursday, December 4, 2014 9:56 PM