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. Yes..it is a command line, like all the checklist

   1: USE [msdb]

   2: GO

   3:  

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

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

   6: GO

   7:  

   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

  20:  

  21:  

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.

#PowerShellLifeStyle