Integration Points

This is a script destined for a scheduled task/cron that reads the DB, gets a count to alert via email if data is missing. (aka “Miner’s Canary”/ heartbeat)

Credits due:
http://weblogs.asp.net/soever/archive/2006/12/31/powershell-pitfalls-reading-text-from-file-using-get-content.aspx

http://poshcode.org/1791

### Create tempfile
$filename = [System.IO.Path]::GetTempFileName()

### Read DB to query Long table for SCADA data
Invoke-Sqlcmd2 -ServerInstance "DBserver\InstanceName" -Database "DBNAME" -Query "Select '[' + RTRIM(COUNT(*)) +']'  from TargetTable Where LogDate = getdate();" | Format-List | Out-File $filename;

### read tempfile as string
$a = [string]::join([environment]::newline, (get-content $filename))

### SMTP call you can put in an IP or FQDN for the SMTP server
if ($a.Contains("[0]"))
{
$smtp = New-Object System.Net.Mail.SMTPClient -ArgumentList smtpservername
$smtp.Send('from@mail.com', 'toaddress@mail.com', 'subject line', "Message Body")
}

### Delete temp file
Remove-Item $filename

### Simple utility to connect to SQL without frills
function Invoke-Sqlcmd2
{
param(
[string]$ServerInstance,
[string]$Database,
[string]$Query,
[Int32]$QueryTimeout=30
)

$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $ServerInstance,$Database
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables[0]

}