Powershell Scan Event log

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.
LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nashiookaCommented:
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 DeveloperAuthor Commented:
I did run it locally on the server I was looking logs at.
Gaurav SinghHead - Managed ServicesCommented:
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Learn More!
footechCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leo TorresSQL DeveloperAuthor 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
footechCommented:
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.