Home > SQL Server, SSIS, Vs2010 > WMI Event Watcher Task tips

WMI Event Watcher Task tips

2013/08/02

I had a problem with a “inherited” (aka: no one knows something , i must discover what is running and what it does…) IIS 7.5 server with an SQL 2008 instance running Reporting services, SSIS jobs (that i should transfer to another server) where sometimes the processor was at 100% for minutes.

Obviously the users are complaining about the lack of service : is a mission critical server; so a first step was to know how many times the processor was over 80% along the work day.

On the same server was needed to copy files in a directory , where a “watchdog” should read and process when a files was uploaded.

Giving the fact that during the night on this server are running some heavy jobs and the directory watchdog is not needed in the day hours, it was requested that the job surveilling the processor was running only during the day and the file watchdog only the night, in order to avoid unuseful workloads.

I don’t know server management software (as Nagios for example) and similar so i thinked an quick&dirty solution for the processor running at 100% .

The WMI Event Watcher seems perfect for the job , but the SSIS world is very complex and i have discovered some things about the WMI Event Watcher.

The first step is to create from the SQL Server Data Tools a new project , it creates automatically a new .dtsx package , here in the Control Flow we can drap&drop from the SSIS toolbox an WMI Event Watcher:


Double clicking the new object we define the properties, where for the first thing we define a new WMI connection:


The WqlQuerySource is the earth of the WMI procedure, a Wql query is a SQL-like language; for example the query

SELECT * from __InstanceModificationEvent WITHIN 20 WHERE TargetInstance ISA 'Win32_Processor' and TargetInstance.LoadPercentage > 80

gives to us a notification when the CPU use is over 80%

In order to help us to the construction of these queries is better to use some software for help.

The first is the WMI Code Creator from Microsoft.

This software is like a wizard with 4 main tabs where to test the queries, that can be constructed browsing the very huge amount of namespaces and WMI classes .

Naturally is requested an understanding of the WMI structure, this page can be of help.

In our case we use the third tab , “Receive an event”:


Using our example for the CPU use , we use the wizard for the code dinamic construction:


Selecting items in the dropdowns and inserting values (as in figure) we generate the code, in this case our final generated code is :

strComputer = "." 
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2") 
Set objEvents = objWMIService.ExecNotificationQuery _
("SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE " & _
                    "PreviousInstance ISA 'Win32_Processor'" & _
                    " AND TargetInstance.LoadPercentage > 80")
Wscript.Echo "Waiting for events ..."
Do While(True)
    Set objReceivedEvent = objEvents.NextEvent
    'report an event
    Wscript.Echo "__InstanceModificationEvent event has occurred."
Loop

Clicking on the “Execute Code” button is started an command line :


Is the condition is reached (we drop a file in that directory), is launched an MessageBox with text “__InstanceModificationEvent event has occurred.”

This is a simple case , with the case of the directory watchdog the things goes complicated.

The Event Class in this case is __InstanceOperationEvent, the Target Instance CIM_DirectoryContainsFile, for which we should insert the directory name with the syntax “Win32_Directory.Name='<foldername>'”.

The problem is that the wizard is creating string delimited from ” , and we have the slash char \ (directory separator in ms-dos paths) that must be escaped, in SSIS we escape a single \ with \\\\ (4 characters).

The problem is complex because we must insert string with a mix of delimiters (‘ and “”) , for example my first attempt was:

(“SELECT * FROM __InstanceOperationEvent WITHIN 60 WHERE TargetInstance.GroupComponent = “Win32_Directory.Name=’g:\\\\WMIFileWatcher'””)

But this causes an error launching the code (lacking of a parentesis); note that after we must insert the working code in the WqlQuerySource property of the WMI Event Watcher .

In order to be not driven mad we could correct the generated code (is not readonly) as

Dim strComputer
Dim objWMIService 
Dim varSrc 
Dim objEvents 
' better do declare the variables , even if in VBScript are all  of Variant type
strComputer = "."  ' the local pc name , otherwise a NETBIOS name
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2") 
' below we specify the directory to be watched
varSrc = "g:\WMIFileWatcher"
varSrc = Replace(varSrc, "\", "\\\\")
Set objEvents = objWMIService.ExecNotificationQuery _
    ("SELECT * FROM __InstanceOperationEvent WITHIN 10 WHERE " _
        & "Targetinstance ISA 'CIM_DirectoryContainsFile' and " _
            & "TargetInstance.GroupComponent= " _
                & "'Win32_Directory.Name=""" & varSrc & """'")
Wscript.Echo "Waiting for events ..."
Do While(True)
    Set objReceivedEvent = objEvents.NextEvent
    Wscript.Echo "A file operation has occurred in the directory."
Loop

This works , so we can follow the same approach for our SSIS package.

Note for WITHIN : is the number of seconds between each WMI polling of a directory, a low value causes an high CPU load, an high value could lead to problems if the file in the meantime is changed or deleted: it is necessary to find an adequate value for the specific case.

But the problem with the WMI Event Watcher is related to the event : when it fires our WMI Event Watcher “dies” and the dtsx is terminated.

A first approach could be to insert the Watcher inside a loop:


The loop espression could be


And our dtsx is running forever.

Now we can construct the Wql query using first Script Task linked via a Precedence Constraint to the WMI Event Watcher:


In the Script Task the code is (Vb2010):

Public Sub Main()
    Dim strPathWatch As String
    strPathWatch = "g:\WMIFileWatcher"
    strPathWatch = Replace(strPathWatch, "\", "\\\\")
    Dim strCode As String = "SELECT * FROM __InstanceOperationEvent WITHIN 10 WHERE " _
    & "Targetinstance ISA 'CIM_DirectoryContainsFile' and " _
        & "TargetInstance.GroupComponent= " _
            & "'Win32_Directory.Name=""" & strPathWatch & """'"
    Dts.Variables("User::varScriptSrc").Value = strCode
    Dts.TaskResult = ScriptResults.Success
End Sub

And using the variable varScriptSrc as source of the WMI Event Watcher:


The variable must be , obvious, specified as a read write variable:


But our problem is that we need to stop the procedure at a certain hour, so we change the EvalEspression in

GETDATE() < DATEADD(“Mi”,30,DATEADD(“Hh”,18,(DT_DBDATE)GETDATE()))

What is this , that seems TSQL but is not TSQL? Is an example of the Wql syntax , a SQL dialect that supports the WMI infrastructure.

If this case the condition is true when the system date is over 18:30

How to construct these queries ? In MSDN there is an reference , but we need something to test the Wql queries , we can’t debug the EvalExpression…

So is very useful a software as the Expression Tester Tool , that can be downloaded from Codeplex.

Here we can create our Wql with the treeview wizard or write directly a Wql string and try what happens :


Now that we have a tested Wql query we can use it in our Loop:


But there is a problem : until does not happens that a file is copied in our guarded directory, the dtsx is running forever despite the EvalExpression, which is tested only at the first launch !

We can be resolve the problem using the Timeout property :


Note that is specified an timeout (20 seconds) that must be >= of the WITHIN time in the Wql query (in our sample 10 seconds).

What happens at this point? If a file is dropped at the end of the polling (WITHIN 10) interval the dtsx flows outside the WMI Event Watcher , we can do something with the event via a Script Task, for example, after the WMI Event Watcher:


otherwise is nothing happens (no file dropped in that directory) after 20 seconds the code goes in every case to the Script Task as when a file is dropped .

Here there is the problem : apparently with this approach there is nothing that we can test in order to understand if the Script Task is running because was happened an Timeout or a file was dropped in the surveilled directory.

And beware of the Expressions, you can create something as:


Using some test variable as the varResult of the example , the sample expression is compiled without errors but is absolutely wrong this thing (should be provided an warning…)! the expression serves to calculate the value of the property instead of a fixed value chosen drom the dropdown : in this case the AfterTimeout dropdown


 
 

is no more working (is not possible to change the value) because its value is calculated from the Expression; its result should be one of the enumerated values so the sample expression is a no sense (even if it is correct, from the syntax point of view).

In practice the correct approach is to use the Event Handlers, not a Script Task or other after the WMI Event Watcher:


where we can correctly manage if the WMI Watcher is exited because a timeout or because (with the WMIEventWatcherEventOccurred event) a file was dropped.

In any case (timeout or file dropped) our loop condition is tested at least every 20 seconds (the defined timeout) so the Loop condition is evaluate ; at least 20 seconds after 18:30 our final package:


is terminated; the same approach can be taken for the CPU surveillance.

For SSIS expressions some useful references here and from Microsoft, which has a dedicated blog.

Advertisements
Categories: SQL Server, SSIS, Vs2010
%d bloggers like this: