trešdiena, 2015. gada 3. jūnijs

MS SQL Express query changes of table and export to txt file and send by mail

Powershell script which makes sql query, compares result to previous result and in case of changes send result via mail. Link to ps1 file here. I have Microsoft SQL Express and a task to check one specific column and send message to security officer if column is changed. Because support are outsourced Database and server must be untouched as much as possible. SQL Express version does not have embedded mail sending and lot of other tools. If you want to do it via triggers, there are lot of .net functions that must be enabled and I am not sure is it really possible via sql express version.

Benefit- txt file is in date format so you can compare files later. Define variables- names of text files.
$ab='C:\temp\' + (Get-Date -format "MM-dd-yyyy-").tostring()+'go.txt'
$cd='C:\temp\go.txt'
Yesterday data are in file go.txt. Formatted table are sent out to txt file. If content of files differs, script send mail. Otherwise just exits.
$table | format-table $format | Out-File $ab
$go1 = Get-Content $ab
$go2 = Get-Content $cd
# compare contents of 2 files if differs, send mail
if (diff $go1 $go2)

 { 
Script uses embedded Powershell capability to send mails. If smtp server asks for credentials it will be quite more complex.
Send-MailMessage -From "Yourservername <server@valid.suffix>" -to "Admin <yourmail@yourdomain>","Admin2 <name2@youtdomain>" -Subject "Row changes noticed" -body "Detected changes in Table, see the attachment" -Attachments ($ab) -SmtpServer "smtp server fqdn"
Requirements. File go.txt must exist before executing script. I assume there are data from yesterday. Connection to SQL Express are possible only locally, or you have to install SQL Connection Manager and enable port 1433. Script exports sql query in txt file and then sends  it by mail. C:\temp are read only permissions for users and full control for Owners. SMTP recieving must be enabled on local mail server. Usually in intranet it is allowed from all hosts. Powershell execution must be allowed locally. You can schedule Powershell script via Task Scheduler to run daily.
If you dont know exactly, how to create scheduled tasks here are detailed explanation. http://blogs.technet.com/b/heyscriptingguy/archive/2012/08/11/weekend-scripter-use-the-windows-task-scheduler-to-run-a-windows-powershell-script.aspx Link to ps1 file here.

d

Nav komentāru:

Ierakstīt komentāru