Powershell Scan Event log

Leo Torres
Leo Torres used Ask the Experts™
on
Hello,

CLS
$a = Get-Date
$b = $a.AddHours(-1) #$a.AddDays(-1)

$Server = "ServerName"

Get-EventLog -ComputerName $Server -LogName Application -After $b -Before $a -Source "MSSQLSERVER" | ?{$_.EventID -eq 14151 }

Open in new window


The code above returns 
   Index Time          EntryType   Source                 InstanceID Message                                                                                    
   ----- ----          ---------   ------                 ---------- -------                                                                                    
 2255302 Mar 22 10:39  Error       MSSQLSERVER            3221239623 The description for Event ID '-1073727673' in Source 'MSSQLSERVER' cannot be found.  The...
 2255299 Mar 22 10:38  Error       MSSQLSERVER            3221239623 The description for Event ID '-1073727673' in Source 'MSSQLSERVER' cannot be found.  The...
 2255298 Mar 22 10:38  Error       MSSQLSERVER            3221239623 The description for Event ID '-1073727673' in Source 'MSSQLSERVER' cannot be found.  The...
 2255297 Mar 22 10:38  Error       MSSQLSERVER            3221239623 The description for Event ID '-1073727673' in Source 'MSSQLSERVER' cannot be found.  The...
 2255296 Mar 22 10:37  Error       MSSQLSERVER            3221239623 The description for Event ID '-1073727673' in Source 'MSSQLSERVER' cannot be found.  The...
 2255295 Mar 22 10:37  Error       MSSQLSERVER            3221239623 The description for Event ID '-1073727673' in Source 'MSSQLSERVER' cannot be found.  The...
 2255294 Mar 22 10:37  Error       MSSQLSERVER            3221239623 The description for Event ID '-1073727673' in Source 'MSSQLSERVER' cannot be found.  The...

Open in new window



I need this script to go back 1 hour and check for error.  If it returns at least 1 error send me an email with the time and message in the body as a table. But I cant even get the message to display correctly.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
nashiooka

Commented:
Can you try and run the command directly on the server?  Just wondering if it's because the local registry doesn't have the event information, maybe it doesn't return the data with full fidelity.  If it does work on the server I'd wonder if it would work with the proper SQL Management Tools installed on the running station.
Leo TorresSQL Developer

Author

Commented:
I did run it locally on the server I was looking logs at.
Gaurav SinghSolutions Architect

Commented:
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Start Today!
Top Expert 2014
Commented:
Change line 7.
$events = @(Get-EventLog -ComputerName $Server -LogName Application -After $b -Before $a -Source "MSSQLSERVER" | ?{$_.EventID -eq 14151 })

If ( $events.count -gt 0 )
{
    $params = @{
        subject = "Testing"
        to = "test@company.com"
        from = "ps-test@company.com"
        smtpserver = "smtp.server.com"
    }
    Send-MailMessage @params -Body ($events | Select Time,Message | Format-Table -Wrap -AutoSize | Out-String)
}

Open in new window


A problem with the Message property is that it is a multi-line string.  If you want to do HTML you have to jump through some hoops to convert things back and forth.

Also, for performance reasons I will always suggest using Get-WinEvent with one of the -filter* parameters if possible over Get-EventLog.
Leo TorresSQL Developer

Author

Commented:
footech:

I like your code and seems like it works but it takes a while since I have to get all the errors in an hour then apply the Eventid filter

The one have seems to work a little better but i cant add the time filter or check for counts so see if its more than 1

cls

$End = Get-Date
$Start = $End.AddHours(-1) #$a.AddDays(-1)

$Server = "SomeServer"

Get-EventLog -LogName Application -ComputerName $Server -EntryType Error | where-object { $_.eventid -eq 14151} | Select TimeGenerated,Message,MachineName -First 2 | Export-Csv errors.csv

$SMTPSERVER = "Server"
$FROM = "Email1"
$TO = "Email2"
$SUBJECT = "Replication Errors"



$Errorinfo = Import-Csv errors.csv | ConvertTo-Html -Fragment
$mailBody = @"
$Errorinfo
"@

Send-MailMessage -From $FROM -To $TO -SmtpServer $SMTPSERVER -Subject $SUBJECT -Body $mailBody -BodyAsHtml: $true
Write-Host "Message Sent...!"

Open in new window


Any way we can add time filter to this and and count check to see if 1 exists send email
footech
Top Expert 2014

Commented:
Why can't you add the time filter or check for counts?  Nothing's different.  Just add the parameter for the time.
For the count, notice what I did:  I collected the output from Get-EventLog into an array and assigned it to a variable.

And I'm not sure why you're exporting to .CSV now.

Can you use Get-WinEvent?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial