asked on
<# connection string and SQL command to execute #>
[String] $now = Get-Date
[String] $to = 'dmegnin@careersourcebroward.com'
[String] $connectionString = 'Server=MSQLServ; Database=ITASync; Integrated Security=SSPI;'
[String] $query = @'
SELECT Provider, LoginDate, DATEDIFF(day, LoginDate, GetDate()) as Diff
FROM Logins
WHERE DATEDIFF(day,LoginDate, GetDate()) IN (37)
'@
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($result)
$format = @{Expression={$_.Provider};Label="Provider Name";width=30},@{Expression={$_.LoginDate};Label="Login Date"; width=25},@{Expression={$_.Diff};Label="Days"; width=8}
$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format
$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format | Out-File C:\Temp\ee\Providers.txt
Send-MailMessage -BodyAsHtml -Body "$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format" -SmtpServer mail.careersourcebroward.com -From "Contract Expiration Database <csbd@careersourcebroward.com>" -To $to -Subject "Provider: $($_.Provider) logged in $($_.Diff) days ago."
$connection.Close()