Huntland Services Ltd Tel: +44 (0)1392-490518 Fax: +44 (0)1392-428003 Enquiries@huntland.co.uk	Setting the Alert Resolution State by Script
	Back	Download The Scripts
[NB Updated 06/08/02 to include additional modification to 'Alert.TimeResolved'.] Being able to trawl through all the outstanding alerts on the System and set them to some resolution state can be very useful for MOM administrators.  For instance, sometimes it's convenient to set all the alerts to resolved, or allocate an owner to many alerts, or change the state of all the alerts for a particular computer.  Most of these actions are possible via the MOM Admin Console but here are a couple of scripts that might just make life a little easier. UpdateAlertResolutionBySource.sql This script will want the name of a particular source (e.g. 'DNS' or 'W3Svc').  These values can be seen in the Source column when viewing the 'All Open Alerts' panel in the MOM Admin Console.   It will then pass through the Alert table modifying the ResolutionState field wherever it finds the alert was raised from your nominated source.  The example below would change the Alert state of all DNS sourced alerts to 'New', mark each record with the data and time of when it was modified, who made the modification and who the owner is.  It also updates the Alert History table with the original entry so you can keep track of how the alert has progressed. Set @Source = 'Dns'                                                                             -- the alert source as it appears in the MOM admin console Set @Comments = 'Performed by Automated Script'                             -- any useful comments Set @CurrentOwner = 'You'                                                                 -- the name of the person who is expected to resolve this alert Set @LastModifiedBy = 'Me'                                                               -- name of person performing the update Set @NewState = 0                                                                            -- The Alert Resolution State (* see table below for permissible values) UpdateAlertResolutionByComputer.sql Another angle on the same problem is to change the state for all alerts that belong to a particular computer.  This allows you to say set all the alerts to resolved for a particular box because you've dealt with all the problems and want to start afresh.  This example does this for a machine called 'Server1' Set @ComputerName = 'Server1'                                                       -- the computer name as it appears in the MOM admin console Set @Comments = 'Performed by Automated Script'                           -- any useful comments Set @LastModifiedBy = 'Me'                                                               -- name of person performing the update Set @NewState = 255                                                                        -- The Alert Resolution State (* see table below for permissible values) Resolution States MOM uses an integer between 0 and 255 to designate the resolution state of an alert.  Administrators can create their own, such as 'Pending', and designate a number from within this range.  However some numbers are reserved and in use by the system for the standard resolution states.  They are as follows:     0 -- New   85 -- Acknowledged 170 -- Level1 - Assigned to Help Desk or Local Support 180 -- Level2 - Assigned to Subject Matter Expert 190 -- Level3 - Requires Scheduled Maintenance 200 -- Level4 - Assigned to External Group or Vendor 255 -- Resolved Obviously it makes sense when creating new Resolution States to number them in sequence as to how they are escalated. Please accept these Transact SQL scripts as is.  In all cases it makes sense to test them out in your own environment and always back up the data first.  Any feed back will be gratefully received at Debug@huntland.co.uk