Alot of people wanted this so I thought I'd just send it to the mail-list: OK, so here's how I managed to do this trigger for emailing me if sensor status changes. I haven't been "down in the weeds" of SQL in a while so the script might not be as "sexy" as someone with more SQL programming experience might do. Your environment may be different than mine but this should work fairly universally. Also, just as a little disclaimer, if you aren't familiar with the RealSecureDB schema or how to use SQL then you probably shouldn't attempt this. ISS may not like this customization as well, although I feel it is a substantially minor edit and does not affect the schema, or the product, as a whole. It could be deleted in a flash without any issues. If anyone at ISS thinks this should void my support then please let me know and I'll delete it. :-)
Step 1: Make sure you have a method for your SQL Server to email you. The most common method is to load a MAPI client and run SQLMail. The other method is to create a stored procedure that will use SMTP to send the email. I chose to use the SMTP method because I didn't have to load anything extra. As a result, my trigger is built for using sp_send_cdontsmail, the SMTP method, instead of xp_sendmail, the SQLMail method. A reference for setting this up is: http://support.microsoft.com/default.aspx?scid=kb;en-us;312839 Step 2: Create a table in the RealSecureDB to use as a reference to the status description. This table will only be a lookup table. If you do a SELECT * of the dbo.Component table you'll see that the Status column has only numbers in it. Apparently, somewhere in the java code of the SiteProtector Console is the translation of the numbers to the actual status such as Active, Idle, Not Responding, etc. A couple people at ISS were able to send me the translation so hopefully these are correct: ---------------------------------------------- 0 Unused 1 Not_Responding 2 Available 3 Scanning 4 Upgrading 5 Unknown 6 Initializing 7 Active 8 Stopped 9 Paused 10 Updating 11 Starting 12 Stopping 13 Pausing 14 Resuming 15 Applying_Policy 16 Applying_Response_File 17 Applying_Property_File 18 Launching_XPU_Install 19 Launching_XPU_Uninstall 20 Revoking_Master_Status 21 Error 22 Unknown_Product_Type 23 Licensing_Failure 24 Disabled 25 Connecting 26 Closed 27 Online 28 Offline 29 Idle ------------------------------------------- I created a table called dbo.ComponentStatus with only two columns like above. The first column, of course, being the primary key although this table has no connections/references to any other table in RealSecureDB. Step 3: The final step is to create the trigger. Now, this could potentially add processing overhead on slow/small SQL servers but I have yet to see any performance hit from this. Here is the trigger I created using the Query Analyzer: -- ==================================================== -- Trigger for emailing Component status changes -- Author: Eric S. Lewis, Technical Security Officer -- U.S. Department of Veterans Affairs -- Austin Automation Center -- ==================================================== SET NOCOUNT ON GO CREATE TRIGGER trig_EricLewis -- You can name it whatever you want here ON dbo.Component FOR UPDATE AS IF UPDATE(Status) BEGIN DECLARE @sensornm1 nvarchar(100) DECLARE @statusdsc1 char(40) DECLARE @delstatusdsc1 char(40) DECLARE @sensortotal1 varchar(140) SET @sensornm1 = (SELECT SensorName FROM Inserted) SET @statusdsc1 = (SELECT StatusDesc FROM Inserted,dbo.ComponentStatus WHERE Status=StatusValue) SET @delstatusdsc1 = (SELECT StatusDesc FROM Deleted,dbo.ComponentStatus WHERE Status=StatusValue) SET @sensortotal1 = @sensornm1+' '[EMAIL PROTECTED] IF @statusdsc1 != @delstatusdsc1 exec sp_send_cdontsmail '[EMAIL PROTECTED]','[EMAIL PROTECTED]',@sensortotal1,'Pleas e check sensors!' END GO -------------------------------------------------------------------- This will email you only if the status has changed since the previous refresh of the table. Apparently the table does an INSERT or UPDATE everytime there is a heartbeat to the sensor because the first trigger I wrote didn't have the check/comparison of the Deleted table and was emailing me like crazy. Now it should only email when the newly Updated status is different than the previous status. Also, remember that if you take down the core or an event collector then you may get a whole bunch of emails depending on the number of sensors you have. I have this sent to a distribution list that goes to all of my technical team's pagers/emails. I put the Sensor's name and the status all in the subject line so it is easier to see on a pager or phone but you can put whatever you want in the subject or body. If anyone needs further assistance with this you can email me. Eric S. Lewis, CCNA, MCSE, NSA IAM, CCSA, CISSP, CEH, ISS-CS Technical Security Officer (Technical Team Lead) VA Automation Center _______________________________________________ ISSForum mailing list [EMAIL PROTECTED] TO UNSUBSCRIBE OR CHANGE YOUR SUBSCRIPTION, go to https://atla-mm1.iss.net/mailman/listinfo
