SQL Server Real-time Monitoring using WMI classes: Part -3

I hope you have been through both Part 1 and Part 2 of this series .In Part 1 we discussed about using WMI based real-time alerts using SQL Server Agent and shared the example of capturing blocking through it .In Part 2, I shared with you how you can create several SQL Server related alerts on your own and did WMI Deadlock alert for you.

In this part we will see how to capture OS related alerts.I will share an example of High CPU alert with you .Using the same example you can create different other alerts as required in your organization.

Creating OS related Alerts in SQL Server will need different approach and it’s not as straightforward as we have seen so far. Now, before we move forward, there is one thing to note here.

If you try to capture the columns from the OS WMI class in to the database tables it’s going to fail .The error you will see in the Job history is Shown below (column name would be different  as per the alert )

1_SQL_Server_Realtime_Monitoring_using_WMI_classes_Part_3

Do not take this error as an issue with your WMI and then start Binging / Googling ending up running WMIDIAG.This is because error ID 0x80041002 means object not found and in our case this is not the correct error .So looks this functionality of catching up columns of OS WMI class in SQL Server tables might still not have been incorporated within SQL Server Agent as of now (SOMEONE PLEASE CORRECT ME J ) .But the good thing is that we can capture the alert and send a mail to the DBA group .

Let’s begin with creating an alert for high CPU.

Explanation of the script :

  • Here we are creating the High CPU alert and capturing the event whenever the CPU exceeds 80%.The alert checks for the high CPU condition every 5 minutes.
  • We need to choose the CIMV2 namespace, the class would be _InstanceModificationEvent  and the Targetinstance would be Win32_Processor.
  • There are a few Keywords that we have used .These are :
    • ISA which means is equal to and is used for a string. It is used to refer to the instance (or rather say targetInstance) of the event class.
    • WITHIN is used for specifying polling gap and it’s in seconds .We have used 300 which means that every 5 minutes the alert will check if the high CPU condition is met or not .
  • You will also notice that in this script (the initial part) I am creating an extended stored procedure to write the information to the HTML file .I have not created this SP but just incorporated it in to this script .If you try and bing with the keyword “ sp_OACreate ‘scripting.filesystemobject’ ” , you will find some good examples that you can modify and use as per your requirements (that’s what I did ).
  • For background colors you need to study HTML color codes .Again BING on “background-color:#FF0000” and you will get good hits.
  • Finally I am using database mail to send the HTML report (believe me it looks beautiful).
  • You will also need to enable XP_CMDSHELL and Ole Automation Procedures That is It ..

Now there is only one thing left and that is to create centralized alerts .This would be more helpful than creating individual alerts .We will discuss this in the next blog (might be the last in this series ) …

Hope you have liked this entry …Your comments are most welcome .If there is any specific alert that you are looking for , please post it in the comments section and I will get back to you .

Cheers and happy learning!!

 

Regards

Abhay Chaudhary

Like us on FaceBook Follow us on Twitter

Follow me on FaceBook | Join the fastest growing SQL Server group on FaceBook