Automating a Solution Monitoring the Number of Files in a Path with SQL WMI Alert and WQL

In my last job a did a complete automated solution to gathering the data from several servers  to a daily checklist (jobs failed, backups,..etc..using PowerShell in asynchronous mode). This solution is using PowerShell scripts of course, SQL Server Jobs and a  SQL WMI Alert.

The full process I will cover in my new exciting written project about Practical day-to-day SOLUTIONS in a Real World DBA using PowerShell and SQL Server, with 3 great friends, but generally I had some checklist that only applied in some servers, in some days. Then I had to create a flexible and automated solution, in a central repository server, using a few  tables to make the relationship between servers, checklist (enabled or not) and day of week. Each checklist created had its own CSV file in a path and store the values in its tables.

As any of the checklist jobs had schedule ,yes they were ran at the same time (using PowerShell) by other job called MatrixStart,, and when all of them were finished I wanted a email to all dba´s with the csv joined in a xlsx file, I created a SQL Agent WMI alert to check the csv files in the path. When the count of the files = 10, the check list process has finished. Then this SQL WMI alert run a JOB that do the join in the csv (using a PowerShell command line too)

The trick is how to use WQL to do this. Luckily I found in this post from my good friend and PowerShell Jedi Ravikanth Chaganti (@ravikanth) the WQL to monitoring the File Creation. But in this post he show how to monitoring a file creation in a path. I want to monitor the number of the files created. So, I found in his AWESOME eBook WMI query language (WQL) via PowerShell  the solution. It is just use the “group within” clause on the WQL.

The WQL is :

   1: @wmi_query=N'Select * from __InstanceCreationEvent WITHIN 5 WHERE TargetInstance ISA ''CIM_DataFile'' AND TargetInstance.Drive=''C:'' AND TargetInstance.Path=''\\DailyCheckList\\'' group within 10'

This WQL monitoring all files created in the c:\dailychecklist folder and the “group within 10” clause say that only when 10 files are created.This is mean that my checklist process  were finished.

Now it is just to create the SQL WMI Alert to run the Job to join all csv in a xlsx file and send by email to the DBA´s. is a command line, like all the checklist Sorriso

   1: USE [msdb]

   2: GO


   4: /****** Object:  Alert [TestWMIFileCount]    

   5: EXEC msdb.dbo.sp_delete_alert @name=N'MatrixWMIFileCount'

   6: GO


   8: /****** Object:  Alert [TestWMIFileCount]    

   9: EXEC msdb.dbo.sp_add_alert @name=N'MatrixWMIFileCount', 

  10:         @message_id=0, 

  11:         @severity=0, 

  12:         @enabled=1, 

  13:         @delay_between_responses=0, 

  14:         @include_event_description_in=0, 

  15:         @category_name=N'[Uncategorized]', 

  16:         @wmi_namespace=N'\\.\root\CIMV2', 

  17:         @wmi_query=N'Select * from __InstanceCreationEvent WITHIN 5 WHERE TargetInstance ISA ''CIM_DataFile'' AND TargetInstance.Drive=''C:'' AND TargetInstance.Path=''\\dailychecklist\\'' group within 10', 

  18:         @job_id=N'f9c98f09-afda-4b97-a18b-301ac9cb6c8d'

  19: GO



Then,  the question. Why should  DBA  know PowerShell ?

It is all about SOLUTIONS. In here I am integrating PowerShell, SQL Jobs and SQL WMI alert . I was wondering how I could  do the SOLUTION without PowerShell.



About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s