VBScript: Forward SQL Server Alerts/Windows Events to (Cingular) SMS

Someone recently asked why I don’t use SQL Mail on my SQL Servers (2000 and below). The answer is: I don’t want to install Outlook on my SQL Servers. Sure, there are ways around it but I prefer just using “net send” to alert the Operator (moi) then forwarding those messages on to my inbox and phone.

Although my employer offered me a Blackberry, I never really cared for them. The battery life is just too short and I don’t like talking into something the size of my hand. So.. I use a regular phone that doesn’t receive e-mails easily. It does, however, love to receive texts. Cingular even gave me an email address to go with my phone and this is what I use to Alert me if any of my jobs (Backups, Optimizations, etc) fail.

First, I created a DNS CNAME record to my workstation called DBAWorkstation. Then I created an operator named DBA Workstation. I like to keep things generic in the event that I leave or my workstation name changes. In the event that happens, there’s just one change at the DNS level. So next, I set the jobs to alert this operator via net send if any jobs fail. Finally, I set up a script that runs all the time on my workstation which has IIS’s SMTP installed locally:
‘****************************************************************************
‘ This script created by Chrissy LeMaire ([email protected])
‘ Website: http://netnerds.net/

‘ NO WARRANTIES, etc.

‘ This script monitors Windows Events and forwards any Application Popups
‘ to an email address
‘*****************************************************************************

‘ Push Event Viewer Alert
Set objWMIService = GetObject(“winmgmts:{(security)}!root/cimv2”)
Set eventSink = wscript.CreateObject(“WbemScripting.SWbemSink”, “EVSINK_”)
strWQL = “Select * from __InstanceCreationEvent where TargetInstance isa ‘Win32_NTLogEvent’ and TargetInstance.SourceName = ‘Application Popup’ and TargetInstance.EventCode = 26”
objWMIService.ExecNotificationQueryAsync eventSink,strWQL

‘ Keep it going forever
While (True)
Wscript.Sleep(1000)
Wend

Sub EVSINK_OnObjectReady(objObject, objAsyncContext)
‘Here’s where I do a whole lot of cleaning. Edit to your liking.
strCleanedMsg = replace(objObject.TargetInstance.Message,”Application popup: Messenger Service : “,””)
strCleanedMsg = replace(strCleanedMsg,”Message from “,””)
strCleanedMsg = replace(strCleanedMsg,” to ” & objObject.TargetInstance.ComputerName,””)
strCleanedMsg = replace(strCleanedMsg,” for DB Maintenance Plan ‘DB Maintenance for All DBs'”,””)
strCleanedMsg = replace(strCleanedMsg,”JOB RUN:”,””)
strCleanedMsg = replace(strCleanedMsg,” was run on “,” failed around “)
strCleanedMsg = Left(strCleanedMsg,InStr(strCleanedMsg,”DURATI”)-2)
If Len(strCleanedMsg) > 159 Then strCleanedMsg = Left(strCleanedMsg,159) ‘ SMS allows max 160 characters. I picked 159 just in case.

Set objCDO = CreateObject(“CDO.Message”)
objCDO.From = “SQL Alert
objCDO.To = “[email protected]” ‘ Phone number goes there. Cingular does the SMS magic
objCDO.CC = “[email protected]
objCDO.TextBody = strCleanedMsg
objCDO.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 1 ‘cdoSendUsingPort (1 = local, 2= remote, 3 = Exchange)
objCDO.Configuration.Fields.Update
objCDO.Send
set objCDO = Nothing
End Sub

Run that script as a service and you’re set with SMS/E-mail notifications in SQL Server 2000 and below. SQL Server 2005 can do the same thing..but e-mail is much more accessible within the service itself.

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SQL Server, VBScript