I’ve been spending a lot of my free time working to take out all of the stops on my upcoming presentation for the Microsoft Management Summit (OpsMgr Evolution). This blog article will discuss how to create a custom SQL Synthetic Transaction which writes to a table on a schedule basis and will be discussed in that session. Prior to details this process, attached is a short (9 seconds) pre-session video for OpsMgr Evolution!

Recently I started documenting the addition of custom SQL synthetic transactions for OpsMgr. Out of the box we can use an OLEDB synthetic transaction which will validate the ability to execute a query on a database. This can be extremely useful, but for one my clients they needed to go beyond this to perform scheduled writes to a table to validate not only read but write functionality to the database. So let’s get to it!

Start by creating a new Unit monitor.

 

Choose the Scripting, Generic, Timed Script Two State Monitor and choose your own management pack to store it in.

 

Define the name of the Rule [Database R/W Test (SCOM)] and a description for the monitor. The target (in this case) is SQL DB Engine and we are putting this under the Availability Parent Monitor. Please note that the monitor is NOT enabled otherwise it would attempt to be activated on every SQL DB engine. In a follow-up to this blog article I will detail the steps required to create a custom synthetic transaction which is targeted to a unique entity so that we can directly add the entity direction to a distributed application.

 

Next define a schedule for your unit monitor – in our case we are writing to the database every 5 minutes.

 

Next add the script which actually is going the work. Our script is “SCOMCheckDBReportToSCOM.vbs” – content below (so it can be directly cut and pasted into the window).

Option Explicit
‘——————————————————————————————–
Dim Version: Version = “1.1.08”
‘ Filename:        ScomCheckDBReportToSCOM.vbs
‘ Created:        11/23/2010
‘ Related Files:
‘ Author        Larry Brown
‘ Email            _Larry_brown@live.com  <- notice both underscores
‘ Purpose:        Should be ran against a DB. Checks for the SCOM.SCOMDBCheck table created by the DBA Group.

‘ Usage:        cscript.exe ScomCheckDBReportToSCOM.vbs /SQLServerName:ServerName /Database:DatabaseName (/SQLInstance:Instance) (/Sev:SEV1-SEV3)(/Debug:True:False)


‘ Revision History:




‘——————————————————————————————–
‘ Variable Declarations
‘——————————————————————————————–

Const HKEY_CLASSES_ROOT = &H80000000
const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003
Const ForAppending = 8
Const ForReading = 1
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const ErrorEvent = 1
Const WarningEvent = 2
Const InfoEvent = 0

Dim bDebug: bDebug = False

If bDebug = False Then
On Error Resume Next
End If

Dim SystemDrive, ProgramFiles, WinDir

Dim objLocator: Set objLocator = CreateObject(“WbemScripting.SWbemLocator”)
Dim objComputer: Set objComputer = GetObject(“WinNT://” & “.” & “”)
Dim objWshShell: Set objWshShell = WScript.CreateObject(“WScript.Shell”)
Dim objFSO: Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Dim objNetwork: Set objNetwork = Wscript.CreateObject(“WScript.Network”)
Dim objWMI: Set objWMI = GetObject(“winmgmts:” & “{impersonationLevel=impersonate}!\\.\root\cimv2”)
Dim WshEnv: Set WshEnv = objWshShell.Environment(“PROCESS”)
Dim oReg: Set oReg=GetObject(“winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv”)
Dim objADSysInfo: Set objADSysInfo = CreateObject(“ADSystemInfo”)
Dim oArgs: Set oArgs = WScript.Arguments
Dim colNamedArguments: Set colNamedArguments = WScript.Arguments.Named

Dim strMessage, strDate, strScriptName

Dim strEndEmailAddies, arrCollectionContentsArray(), content
Dim strSite, strServer, strUserName, strLogPath
Dim strAllUsersDesktopPath, strAllUsersProgramsPath, strUserProfilesMainFolder, strCurrentUserDesktop, strCurrentUserStartMenu, strScriptFileDirectory
Dim strUser

SetScriptItems

Dim strSQLInst, strSEV, strDB, strSQLServerName

If oArgs.Count < 2 Then
ExitDueToParameters(“Not enough parameters provided”)
WScript.Quit 1
Else
If colNamedArguments.Item(“SQLServerName”) <> “” Then
strSQLServerName = colNamedArguments.Item(“SQLServerName”)
Else
ExitDueToParameters(“Doesn’t include the SQLServerName as a parameter”)
WScript.Quit 1
End If
strSQLInst = colNamedArguments.Item(“SQLInstance”)
If colNamedArguments.Item(“Sev”) <> “” Then
strSEV = colNamedArguments.Item(“Sev”)
If bDebug = True Then
strMessage = “Severity level in command Line.. Level set at ” & strSEV
AddtoScomLog InfoEvent,500,strMessage
End If
Else
strSEV = “SEV3”
If bDebug = True Then
strMessage = “Severity level set at default ” & strSEV
AddtoScomLog InfoEvent,500,strMessage
End If
End If
If colNamedArguments.Item(“Database”) <> “” Then
strDB = colNamedArguments.Item(“Database”)
Else
ExitDueToParameters(“Doesn’t include the Database as a parameter”)
Script.Quit 1
End If
If colNamedArguments.Item(“Debug”) <> “” Then
bDebug = colNamedArguments.Item(“Debug”)
bDebug = CBool(bDebug)
If Err.number <> 0 Then
ExitDueToParameters(“Can’t convert Debug statement to boolean. Parameter Recieved was ” & colNamedArguments.Item(“Debug”))
Script.Quit 1
else
If bDebug = True Then
strMessage = “Successully converted Debug to a Boolean. Data is ” & bDebug
AddtoScomLog InfoEvent,500,strMessage
End If

        End if
End If
End If

If bDebug = True Then
‘On Error GoTo 0
strMessage = “Starting Script ” & strScriptName
AddtoScomLog InfoEvent,500,strMessage
End If

If bDebug = True Then
Dim x
Dim i: i = oArgs.Count
strMessage = “Command Line parameters: ”
For x = 0 To i -1
strMessage = strMessage & Chr(10) & “Parameter #” & x & ” : ” &  oArgs.Item(x)
Next
AddtoScomLog InfoEvent,500,strMessage
End If

Dim strTable: strTable = “SCOM.SCOMDBCheck”

If strSQLInst <> “” Then
strSQLServerName = strSQLServerName & “\” & strSQLInst
End If

Dim intRowCountThreshold: intRowCountThreshold = 2500

DIm intRowCount: intRowCount = GetRowCount(strSQLServerName,strDB)

If IsNumeric(intRowCount) = False Then

strMessage = “DB Access Error, Can’t Read from SQLServerName: ” & strSQLServerName & ” DB: ” & strDB
ReportInfoToScom “BAD”,strMessage,strDB
AddtoScomLog ErrorEvent,501,strMessage
WScript.Quit

End If

If intRowCount > intRowCountThreshold Then
If bDebug = True Then
strMessage = “Row Count is : ” & intRowCount & ” and is greater than ” & intRowCountThreshold
AddtoScomLog InfoEvent,500,strMessage
End If
CleanUpDB strSQLServerName,strDB,intRowCountThreshold – 500
If bDebug = True Then
strMessage = “New row count after deletion is ” & GetRowCount(strSQLServerName,strDB)
AddtoScomLog InfoEvent,500,strMessage
End If
Else
If bDebug = True Then
strMessage = “Nothing to do, row count is only at ” & intRowCount
AddtoScomLog InfoEvent,500,strMessage
End If
End If

If bDebug = True Then
strMessage = “Count is ” & GetRowCount(strSQLServerName,strDB)
AddtoScomLog InfoEvent,500,strMessage
End If

Dim strTargetDate: strTargetDate = Now()

WriteDateToDB strSQLServerName,strDB,cdate(strTargetDate)

Dim strCurrentDBinfo: strCurrentDBinfo = GetInformationFromDB(strSQLServerName,strDB,strTargetDate)

If strCurrentDBinfo = strTargetDate Then
strMessage = “DB Access Verified SQLServerName: ” & strSQLServerName & ” DB: ” & strDB
ReportInfoToScom “GOOD”,strMessage,strDB
Else
strMessage = “DB Access Access Error, Can’t write to SQLServerName: ” & strSQLServerName & ” DB: ” & strDB
ReportInfoToScom “BAD”,strMessage,strDB
AddtoScomLog ErrorEvent,501,strMessage
End If

If bDebug = True Then
strMessage = “Exiting Script.”
AddtoScomLog InfoEvent,500,strMessage
End If

 

Function GetInformationFromDB(strSQLServerName,strDB,strCriteria)

On Error Resume Next
Dim  objConn, objRS, strSQLQuery

strSQLQuery =    “SELECT CheckDate ” & _
“FROM ” & strTable & _
” where CheckDate = ” & chr(39) & strCriteria & chr(39)

Set objConn = CreateObject(“ADODB.Connection”)
Set objRS   = CreateObject(“ADODB.Recordset”)

    objConn.Open _
“Provider=SQLOLEDB;” & _
“Data Source=” & strSQLServerName & “;” & _
“Trusted_Connection=Yes;” & _
“Initial Catalog=” & strDB &  “;”
objRS.Open strSQLQuery, objConn, adOpenStatic, adLockOptimistic

if objRS.EOF Then
GetInformationFromDB = “Unknown”
Else
objRS.MoveFirst
Do Until objRS.EOF
GetInformationFromDB = objRS(“CheckDate”)
objRS.MoveNext
Loop
end If
objRS.Close
set objConn = Nothing
set objRS = Nothing

End Function
‘***********************************************************************************************

Sub WriteDateToDB(strSQLServerName,strDB,strDate)

On Error Resume Next
Dim  objConn, objRS, strSQLQuery

strSQLQuery = “USE ” & strDB & Chr(10) & _
“insert INTO” & Chr(10) & _
strTable & Chr(10) & _
“select ” & Chr(39) & strDate & Chr(39)

    Set objConn = CreateObject(“ADODB.Connection”)
Set objRS   = CreateObject(“ADODB.Recordset”)

    objConn.Open _
“Provider=SQLOLEDB;” & _
“Data Source=” & strSQLServerName & “;” & _
“Trusted_Connection=Yes;” & _
“Initial Catalog=” & strDB &  “;”

objRS.Open strSQLQuery, objConn, adOpenStatic, adLockOptimistic

End Sub
‘***********************************************************************************************

Function CleanUpDB(strSQLServerName,strDB,intCount)

‘On Error Resume Next
Dim  objConn, objRS, strSQLQuery

strSQLQuery =    “USE ” & strDB & Chr(10) & _
“DECLARE @X int ” & “USE ” & strDB & Chr(10) & _
“SELECT @X=COUNT(*) FROM ” & strTable & Chr(10) & _
“SET @X=@X-” & intCount & Chr(10) & _
“DELETE ” & strTable & ” WHERE IsAliveID IN ” & Chr(10) & _
“(SELECT TOP(@x) IsAliveID FROM ” & strTable & ” ORDER BY Checkdate ASC)”

    Set objConn = CreateObject(“ADODB.Connection”)
Set objRS   = CreateObject(“ADODB.Recordset”)

    objConn.Open _
“Provider=SQLOLEDB;” & _
“Data Source=” & strSQLServerName & “;” & _
“Trusted_Connection=Yes;” & _
“Initial Catalog=” & strDB &  “;”

objRS.Open strSQLQuery, objConn, adOpenStatic, adLockOptimistic

Set objConn = Nothing
Set objRS = Nothing

End Function
‘***********************************************************************************************

Function GetRowCount(strSQLServerName,strDB)

On Error Resume Next
Dim  objConn, objRS, strSQLQuery

strSQLQuery =    “SELECT count(*) as count ” & _
“FROM ” & strTable

    Set objConn = CreateObject(“ADODB.Connection”)
Set objRS   = CreateObject(“ADODB.Recordset”)

    objConn.Open _
“Provider=SQLOLEDB;” & _
“Data Source=” & strSQLServerName & “;” & _
“Trusted_Connection=Yes;” & _
“Initial Catalog=” & strDB &  “;”
objRS.Open strSQLQuery, objConn, adOpenStatic, adLockOptimistic

if objRS.EOF Then
GetRowCount = “Unknown”
Else
objRS.MoveFirst
Do Until objRS.EOF
GetRowCount = objRS(“count”)
objRS.MoveNext
Loop
end If
objRS.Close
set objConn = Nothing
set objRS = Nothing

End Function
‘***********************************************************************************************

Sub SetScriptItems

    ‘ Attempts to set the variables and objects that might be needed by the script (not all variables may be used by the script).
SystemDrive = objWshShell.ExpandEnvironmentStrings(“%SystemDrive%”)
ProgramFiles = objWshShell.ExpandEnvironmentStrings(“%ProgramFiles%”)
WinDir = objWshShell.ExpandEnvironmentStrings(“%windir%”)

‘ Attempts to set the needed directory paths.
strScriptFileDirectory = objFSO.GetParentFolderName(wscript.ScriptFullName)
strScriptName = WScript.ScriptName

‘ Attempts to obtain the Desktop and Star Menu paths for all users.
strAllUsersProgramsPath = objWshShell.RegRead(“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders\Common Programs”)
strAllUsersDesktopPath = objWshShell.RegRead(“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders\Common Desktop”)

‘ Attempts to configure Windows XP paths.
strUserProfilesMainFolder = Mid(strAllUsersDesktopPath,1,InStr(strAllUsersDesktopPath, “\All Users”))
strCurrentUserDesktop = strUserProfilesMainFolder & objNetwork.UserName & “\Desktop”
strCurrentUserStartMenu = strUserProfilesMainFolder & objNetwork.UserName & “\Start Menu”

If strUserProfilesMainFolder = “” Then
‘ Attempts to configure Windows Vista paths.
strUserProfilesMainFolder = Mid(strAllUsersDesktopPath,1,InStr(strAllUsersDesktopPath, “\Public”))
strCurrentUserDesktop = strUserProfilesMainFolder & objNetwork.UserName & “\Desktop”
strCurrentUserStartMenu = strUserProfilesMainFolder & objNetwork.UserName & “\AppData\Roaming\Microsoft\Windows\Start Menu”
End If

End Sub
‘***********************************************************************************************

Function ReportInfoToScom(strState,strInfo,strDatabase)

ReportInfoToScom = False
On Error Resume Next

‘DataTypes
‘    Alert Data = 0
‘    Event Data = 1
‘    Performance Data = 2
‘    State Data = 3

strInfo = strInfo & chr(13) & Chr(10) & “ScriptName : ” & strScriptName & ” Version: ” & Version

Dim DataType: DataType = 3
Dim oAPI: Set oAPI = CreateObject(“MOM.ScriptAPI”)
Dim oBag: Set oBag = oAPI.CreateTypedPropertyBag(DataType)

    Call oBag.AddValue(“State”,strState)
Call oBag.AddValue(“Info”,strInfo)
Call oBag.AddValue(“ComputerName”,strSQLServerName)
Call oBag.AddValue(“Database”,strDatabase)
Call oBag.AddValue(“Instance”,strSQLInst)
Call oBag.AddValue(“SEV”,strSEV)
Call oAPI.Return(oBag)
If Err.Number = 0 Then
ReportInfoToScom = True
If bDebug = True Then
strMessage = “Success submitting info to Scom. Returned Success.”
AddtoScomLog InfoEvent,500,strMessage
End If

       Else
AddtoScomLog ErrorEvent,501,”Error submitting info to Scom. Error returned is ” & Err.Number & ” Description: ” & Err.Description
End If

oAPI = Null
oBag = Null

End Function
‘***********************************************************************************************

Function AddtoScomLog(strSeverity,intNumber,strDescription)

‘Error = ErrorEvent = 1
‘Warning = WarningEvent2
‘informational = InfoEvent 0

‘DataTypes
‘    Alert Data = 0
‘    Event Data = 1
‘    Performance Data = 2
‘    State Data = 3

AddtoScomLog = False
On Error Resume Next

If instr(strDescription,Version) > 0 Then
Else
strDescription = strDescription & chr(13) & Chr(10) & “ScriptName : ” & strScriptName & ” Version: ” & Version
End If
Dim DataType: DataType = 3
Dim oAPI: Set oAPI = CreateObject(“MOM.ScriptAPI”)
Dim oBag: Set oBag = oAPI.CreateTypedPropertyBag(DataType)

Call oAPI.LogScriptEvent(strScriptName, intNumber,strSeverity,strDescription)
If Err.Number = 0 Then
AddtoScomLog = True
End If

oAPI = Null
oBag = Null

End Function
‘***********************************************************************************************

Function ExitDueToParameters(strInfo)

    Dim x
Dim i: i = oArgs.Count
strMessage = “Command Line parameters: ”
For x = 0 To i -1
strMessage = strMessage & Chr(10) & “Parameter #” & x & ” : ” &  oArgs.Item(x)
Next
AddtoScomLog InfoEvent,501,strMessage

    strMessage = “Script aborted. Parameters incorrect. should be scriptname /SQLServerName:SQLSERVER /SQLInstance:Instance (/Database:DatabaseName) (/Debug:True:False)”

strMessage = strMessage & chr(10) & strInfo

AddtoScomLog ErrorEvent,501,strMessage


WScript.Quit 1

End Function

05

Define the Unhealthy expression next which in this case we are using “Property[@Name=’State’] Contains BAD.

06

Define the Unhealthy expression next which in this case we are using “Property[@Name=’State’] Contains GOOD.

07

Change the Unhealthy condition from Warning (default value) to Critical.

08

Finally configure the alert to match your requirements with a unique alert name and alert description as shown below.

09

To provide a database to store the SCOMDBCheck table in, we created a SCOMDBTest database and ran the following script to create the required tables:

if exists(select 1 from sys.schemas where name = ‘SCOM’)

begin

   print ‘Schema Already exists’

end

else

begin

   exec(‘create schema [SCOM] Authorization dbo’)

end

 

if exists(select 1 from sys.tables where name = ‘SCOMDBCheck’)

begin

   select ‘SCOMDBCheck table already exists’

end

else

begin

   create table SCOM.SCOMDBCheck

   (

       IsAliveID int identity(1,1) Primary Key NOT NULL, CheckDate datetime

    )

end

11

After the monitor has been created, we now create an override for the specific object that we want to activate SQL monitoring on.

10

Override information for this monitor is shown below: [any parameters shown in () are optional]

‘ Usage:                                cscript.exe ScomCheckDBReportToSCOM.vbs /SQLServerName:ServerName /Database:DatabaseName (/SQLInstance:Instance) (/Sev:SEV1-SEV3)(/Debug:True:False)

To determine the health of this monitor in the monitoring pane, choose the Actions pane and change the target type to SQL DB Engine as shown below.

13

Prior to the override creation, we see the following in the Health Explorer for the SQL DB Engines in the environment.

14

Once the override has been created, we can use Health Explorer to see the health of the custom monitor as shown below.

15

After successful execution of the custom script we can see when the table was last written to as shown in the next two screenshots.

16

17

There is also debug information written to the Operations Manager log indicating when the script is executed and details around it’s execution (shown in the next two screenshots).

18 - debug to opsmgr log

19 - debug continued

The end result of this is a custom SQL synthetic transaction which writes to a SQL database on a scheduled basis and tracks when it was written to. This can be extremely useful when the ability to successfully write to a SQL database is critical for an environment.

Summary: Creating custom SQL synthetic transactions is not that hard and is documented above as an example to work from!

Thank you to Larry Brown for all of his hard work putting this together!