wugSpace: The WhatsUp Gold Community

Script Library

This is a public Media Gallery public RSS

Action

Query Database and Send results via e-mail...

Action posted 11/13/09 by Zachary Loeber

977 Views

Name:

Query Database and Send results via e-mail (optionally update the records as well)

Description:

This is a good compliment to the sql query active monitor. It will open a connection to a remote database first. If there are results it will then compose and e-mail and shoot it out to whom ever you specify in the variables at the top of the code.

Optionally you can update the table values returned as well. I use this to help automate some internal processes but there are many uses for such functionality in the hands of a creative scripter.

Language:

VB Script

Code:

Context.LogMessage "Checking ActionTypeName=" & Context.GetProperty("ActionTypeName")
Context.NotifyProgress "Checking ActionTypeName=" + Context.GetProperty("ActionTypeName")

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
strEmailTo = "emailuser@testsite.local;anotheruser@testsite.local"
strEmailFrom = "whatsupgold@testsite.local"
strEmailSubject = "Alert - SQL query results included" 
strEmailServer = "myemailserver@testsite.local"

strSQL = "SELECT field1, field2, field3 " & _
 "FROM [databasename].[dbo].[databasetable] WHERE " & _
 "((field1 < 2) and (field2 = "& Chr(39) & "Test" & Chr(39) & "))"

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider=SQLOLEDB;Data Source=mydatabaseserver;" & _
        "Initial Catalog=mydatabase;" & _
             "User ID=mydatabaseuser;Password=mydatabaseuserpassword;"

objRecordSet.Open strSQL, _
        objConnection, adOpenStatic, adLockOptimistic

If Not objRecordSet.EOF Then
    objRecordSet.MoveFirst
    If Not objRecordSet.EOF Then
        strMessage = strMessage & "Hello," & vbCrLf & vbCrLf
        strMessage = strMessage & "The following sql results returned" & vbCrLf & vbCrLf
        
        Do While Not objRecordSet.EOF
            ' Create Our Message for republication
            strMessage = strMessage & vbTab & "field1: " & vbTab & vbTab & objRecordSet("field1") & vbCrLf
            strMessage = strMessage & vbTab & "field2: " & vbTab & vbTab & objRecordSet("field2") & vbCrLf
            strMessage = strMessage & vbTab & "field3: " & vbTab & vbTab & objRecordSet("field3") & vbCrLf & vbCrLf
            ' Optionally update a value
            ' objRecordSet("field2")= "Not a Test"
            objRecordSet.MoveNext
        Loop
        ' Only uncomment this if you are actually making changes to the records.
        'objRecordset.Update
        strMessage = strMessage & vbCrLf
        strMessage = strMessage & "Thank You," & vbCrLf
        strMessage = strMessage & "Whatsup Monitoring" & vbCrLf    
        
        Set objEmail = CreateObject("CDO.Message")
        objEmail.From = strEmailFrom
        objEmail.To = strEmailTo
        objEmail.Subject = strEmailSubject
        objEmail.Textbody = strMessage
        objEmail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        objEmail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
                strEmailServer
        objEmail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        objEmail.Configuration.Fields.Update
        objEmail.Send
        Set objEmail = Nothing
    Else
        ' There are no results so don't send anything
    End If
End If

objRecordSet.Close
objConnection.Close

'Set the result code of the check (0=Success, 1=Error)
Context.SetResult 0, "No error"

Disclaimer:

All scripts found on wugSpace are provided as-is with no warranty or guarantee. These scripts are not supported by Ipswitch Technical Support. If you have a problem with this script, please ask any questions in the comments.

Be the first to rate this
|
Sign in to rate this