wugSpace: The WhatsUp Gold Community

Script Library

This is a public Media Gallery publicRSS

Action

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

Action posted 11/13/09 by Zachary Loeber

1606 Views, 4 Comments

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

Comments

posted Apr 27 by Slopium Granitae
I'm getting declare variable errors with each variable. Can you include more?
Reply to this Comment
Attach File
You may upload a file up to 20MB

Your file will be uploaded when you submit the form below
Cancel
- Be the first to rate this
  |
  Sign in to rate this
posted Apr 27 by Zachary Loeber
Are you certain you are defining it as an action script and not an active monitor?
Reply to this Comment
Attach File
You may upload a file up to 20MB

Your file will be uploaded when you submit the form below
Cancel
- Be the first to rate this
  |
  Sign in to rate this
posted Apr 27 by Slopium Granitae
Ah, I was doing something silly. I'm still new to all this, and I don't know much about VB.

Now it is only timing out, I don't think I have setup the objConnection.Open correctly. Or maybe this won't work with the Stock MSDE 2000 database that came with my version of What's Up(2006).

Thank you for responding, especially so quickly.
Reply to this Comment
Attach File
You may upload a file up to 20MB

Your file will be uploaded when you submit the form below
Cancel
- Be the first to rate this
  |
  Sign in to rate this
posted Apr 27 by Zachary Loeber
It is nice to see someone get some use out bits of my hackery :)

If the mydatabaseserver has been replaced correctly it should just work but I've not tested w/ a default instance of sql 2000 yet so you may have to google out the proper connection string.
Reply to this Comment
Attach File
You may upload a file up to 20MB

Your file will be uploaded when you submit the form below
Cancel
- Be the first to rate this
  |
  Sign in to rate this