Powershell script to run a SQL query and if there are results, send an email with csv attachment & post slack message. If no results, send email saying no results.

SQLResultstoEmailSlack.ps1

#Import the PSSlack module
Import-Module PSSlack

#Set user credentials
$User = "$env:UserDomain\$env:UserName"
$PasswordFile = ".\Password.txt"
$KeyFile = ".\AES.key"
$key = Get-Content $KeyFile
$MyCredential = New-Object -TypeName System.Management.Automation.PSCredential `
 -ArgumentList $User, (Get-Content $PasswordFile | ConvertTo-SecureString -Key $key)

#Set Slack auth 
$token = ''

#SQL Vars
$SqlServer = "someserver"
$Database = "somedatabase"
$SqlQuery = 'exec usp_somequery'
$Results = Invoke-Sqlcmd -Query $SqlQuery -ServerInstance $SqlServer -Database $Database

#File Path
$ExportFile = ".\Results - $(get-date -f yyyy-MM-dd).csv"

#Remove the export file if exists
if (Test-Path -Path $ExportFile -PathType Leaf) {
    Remove-Item $ExportFile -Force
}

#Email Vars
$SMTPServer = "smtp.company.com" 
$EmailTo = "you@company.com"
$EmailFrom = "server@company.com"
$Subject = "Some SQL Things" 
$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom,$EmailTo,$Subject,$Body)
$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587) 
$SMTPClient.EnableSsl = $true 
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential($MyCredential.UserName, $MyCredential.Password); 

#If not null then send messages
if ($results -ne $null) {
    #Export SQL Results to CSV
    $results | Export-CSV -Path $ExportFile -NoTypeInformation
    #Attach CSV to Email
    $attachment = New-Object System.Net.Mail.Attachment($ExportFile)
    $SMTPMessage.Attachments.Add($attachment)
    #Create Slack Message
    New-SlackMessageAttachment -Color $([System.Drawing.Color]::red) `
                           -Title $Subject `
                           -Text $ExportFile `
                           -Pretext 'Everything is broken' `
                           -Fallback 'Your client is bad' |
    New-SlackMessage -Channel 'channel name without #' `
                     -Username 'Bob' `
                     -IconUrl 'http://i.imgur.com/WiUdDbu.jpg' |
    Send-SlackMessage -Token $Token
   
     }
else{
      $Body = "SQL Result was blank" 
     }

#Send email
$SMTPClient.Send($SMTPMessage)