how can i set up windows event log notification through mail using SQL server

Question

how can i set up windows event log notification through mail using SQL server

best_yunus

SSC-Addicted

Points: 409
More actions
April 6, 2014 at 10:52 am

#285313

how can i set up windows event log notification through mail using SQL server.
Scenario:
i have to audit an specific eventid and execute a job whenever this event id occured..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

best_yunus (4/6/2014)
I have to audit an specific eventid and execute a job whenever this event id occured..

(1) If I need to constantly check and be notified immediatly I would set this up through an WMI alert with SQL Server Agent, which can be set for notification when it fires. You can also set which SQL Agent Job is run when the alert fires.

(2) If I only needed to check every hour or so I would do this with PowerShell and setup the job step to execute the commands in SQL Server Agent. I would perform the notification within the PowerShell script because it is simpler logic than trying to raise an error up to SQL Server Agent and have the notification be sent out on job failure.

If you needed to monitor multiple server's event logs from one central point, with option two you would need to ensure the SQL Server Agent service account had permissions to the servers in question.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

best_yunus SSC-Addicted Points: 409 More actions · Answer 2

can you share the solution step by step.

i need to audit event id 18264.

I want whenever this event id occur i should get a mail and run a job specific job

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 3

Step-by-step, I don't have that much time at the moment.

You can piece this together yourself with a little work. For Powershell search for the commandlet "Get-EventLog", there are parameters to pass to that in order to filter out the event logs for a period of time and for a specific eventID.

You would then just determine if the event was returned and if it was send an email. You can find sample scripts for PowerShell by searching for things like "sending email in PowerShell". There are a few different options, I suggest using "System.Net.Mail.SmtpClient".

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

best_yunus SSC-Addicted Points: 409 More actions · Answer 4

Thanks.

Seems like there is no option in SQL for this to do.

I will give a shot to this via Powershell.

🙁

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 5

There is no option in T-SQL no, but SQL Server can be used to accomplish this if you want.

I would simply use Scheduled Tasks to execute the PowerShell script, but you can use SQL Server Agent if you want more logging of when the script executes.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton