system
April 26, 2019, 12:08pm
1
I need to disable different SQL Server logins on different servers and get an email if anyone tries to use them. Here is what I have an it works but it is not flexable enough. I need to be able to check for different login failures on different servers.
$attachment = "c:\downloads\errors.csv"
$loginerrors = @()
$errorCount = Get-DbaErrorLog -sqlinstance myServerName,my2ndServer -LogNumber 0 -text "login failed for user 'sa'" -after '2019-04-25'
$loginerrors = Get-DbaErrorLog -sqlinstance myServerName,my2ndServer -LogNumber 0 -text "login failed for user 'sa'" -after '2019-04-25' | select-object ComputerName,LogDate,Text | export-csv -path $attachment
if ($errorCount.Count -gt 0) {
$subject = "failed logins"
}
Send-MailMessage -From "joe@msn.com" -To $recipients -SmtpServer myMailServer.com -Body "see attachment" -Subject $subject -Attachments $attachment
Just off the top of my head, maybe try this…
$ServerList = @('myServerName','my2ndServer') # add as many servers as needed
$attachment = "c:\downloads\errors.csv"
$loginerrors = @()
$errorCount = ForEach($TargetInstance in $ServerList)
{Get-DbaErrorLog -sqlinstance $TargetInstance -LogNumber 0 -text "login failed for user 'sa'" -after '2019-04-25'}
$loginerrors = ForEach($TargetInstance in $ServerList)
{
Get-DbaErrorLog -sqlinstance $TargetInstance -LogNumber 0 -text "login failed for user 'sa'" -after '2019-04-25' |
select-object ComputerName,LogDate,Text |
export-csv -path $attachment -Append
}
if ($errorCount.Count -gt 0)
{ $subject = "failed logins" }
$sendMailMessageSplat = @{
Attachments = $attachment
Subject = $subject
SmtpServer = 'myMailServer.com'
From = "joe@msn.com"
Body = "see attachment"
To = $recipients
}
Send-MailMessage @sendMailMessageSplat
system
April 26, 2019, 5:26pm
3
I should have been a little more clear. I was to search the logs for:
login failed for joe
login failed for jim
login failed for mary
Get-DbaErrorLog only allows 1 string to search for. There may be 1 login which has been failing for years. I don’t want to get email alerts for that.