Solutions..It´s All About Automated Solutions. PowerShell and SQL WMI Alert integrating itself.


A friend , that uses some of my solutions, was with a problem. He has a central repository server ( he uses to some monitoring too) and in this server has a folder called FTPDownload. In this folder has several files downloaded automatically by FTP.

In a specific file (CSV File) that is downloaded one time per day, he needs a solution to identify only this file and store in a other SQL Server. He has a LOT of “robot-programs” that do some operations like that to other files. This programs stay all the time reading the folder to check if some new file is in there.

What he asked to me is if we can do something and does not need to create another “robot” or change the code from the existents.

YES WE CAN !!!!

First lets create the WQL to Monitor the specific file in a specific folder : for us the folder is c:\FTPdownload and the file is FileImport.CSV

@wmi_query=N'Select * from __InstanceCreationEvent WITHIN 300 WHERE TargetInstance ISA ''CIM_DataFile'' AND  TargetInstance.Drive=''C:''and TargetInstance.path=''\\ftpdownload\\'' and TargetInstance.Name = ''c:\\ftpdownload\\FileImport.csv''', 

 

Then Lets create the Job called IMPORTCSV  with a PowerShell code called importCSV.ps1 on c:\FTPScripts

* Download The Out-DataTable  and Write-DataTable

try {

    $DataImport = Import-Csv -Path "c:\FTPDownLoad\FileImport.csv" -ErrorAction Stop

    $DataTable = Out-DataTable -InputObject $DataImport 

    Write-DataTable -ServerInstance YOURSERVER -Database YOURDATABASE -TableName YOURTABLE -Data $DataTable

    $Msg = "FileImport.csv successfully imported"

    Rename-Item -Path  "c:\FTPDownLoad\FileImport.csv" -NewName  "c:\FTPDownLoad\FileImport_$(Get-date -format 'yyyyMMdd').csv"

    Write-Output $Msg

} catch {

      $ex = $_.Exception

      Write-Error "$ex.Message"

    throw "Failure"

}

 

image

 

Now it is time to create the SQL WMI Alert to monitor the creation of this file,based on our WQL :

image

 

Now lets response the alert to the JOB IMPORTCSV :

 

image

 

Alert Code :

USE [msdb]

GO

 

/****** Object:  Alert [CheckCSVFile]    Script Date: 8/9/2012 8:47:15 PM ******/

EXEC msdb.dbo.sp_delete_alert @name=N'CheckCSVFile'

GO

 

/****** Object:  Alert [CheckCSVFile]    Script Date: 8/9/2012 8:47:15 PM ******/

EXEC msdb.dbo.sp_add_alert @name=N'CheckCSVFile', 

        @message_id=0, 

        @severity=0, 

        @enabled=1, 

        @delay_between_responses=0, 

        @include_event_description_in=0, 

        @category_name=N'[Uncategorized]', 

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

        @wmi_query=N'Select * from __InstanceCreationEvent WITHIN 300 WHERE TargetInstance ISA ''CIM_DataFile'' AND  TargetInstance.Drive=''C:''and TargetInstance.path=''\\ftpdownload\\'' and TargetInstance.Name = ''c:\\ftpdownload\\FileImport.csv''', 

        @job_id=N'990ef94a-a96d-41f2-809d-323c5e60d375'

GO

 

 

 

And done Alegre. Every time that a file called Fileimport.csv is created on folder c:\FTPDownload,5 minutes after (Clause Within on WQL 300 seconds)  the alert is fired and run the JOB.  Why 300 ? Just to have time to the file are completed saved in the folder.

TIP  :  if you have some problem with the file and generate a error, even you using Try-Catch the job will finish with no errors,because the exit code is 0 .The error will be show on the job history ,but I need that the job finishes with error.

 

image

 

How to solve this ? Just add throw “Failure”on the catch block and the Exit code change to 1 :

} catch {

      $ex = $_.Exception

      Write-Error "$ex.Message"

    throw "Failure"

}

and the Job will finish  with error :

image

 

Now  you can send an email using PowerShell or by the SQL job informing the error or successful . it is up to you.

After all, it’s all about automated solutions Alegre

 

#PowerShellLifeStyle

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:

WordPress.com Logo

You are commenting using your WordPress.com 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