email alert for certain errors in SQL logs

• kevin.j.sexton

  SSC Veteran

  Points: 277

  More actions

  April 29, 2019 at 5:20 pm

  #3549938

  I need to be notified if there is a specific string in my logs on different servers. (only 1 string per server). Here is what I have, but it will only search for 1 string across multiple servers. I want to search for xyz on server1, abc on server2...and so on.

  My powershell...

  $recipients = "kevin@gmail.com"

  $ServerList = @('server123') # add as many servers as needed

  $attachment = "c:\downloads\errors.csv"

  $loginerrors = @()

  $loginerrors = ForEach($TargetInstance in $ServerList)

  {

  Get-DbaErrorLog -sqlinstance $TargetInstance -LogNumber 0 -text "login failed for user 'navyteam'" -after '2019-04-29' |

  select-object ComputerName,LogDate,Text |

  export-csv -path $attachment -Append

  }

  if ($loginerrors.Count -gt 0)

  { $subject = "failed logins"

  $sendMailMessageSplat = @{

  Attachments = $attachment

  Subject = $subject

  SmtpServer = 'myMailServer'

  From = "kevin@gmail.com"

  Body = "see attachment"

  To = $recipients

  }

  Send-MailMessage @sendMailMessageSplat

  }

  remove-item "c:\downloads\errors.csv"