' This string will be appended to the beginning of first Event description parameter (Params/Param[1])
' Use <RegExExpression /> to narrow alerts to this MP only
Const MANAGEMENT_PACK_ID = "SQL2012PDW MP"
Const ManagementGroupName = "$Target/ManagementGroup/Name$"
Dim sApplianceID
sApplianceID = ""
Sub LogWarning(ByVal customMessage)
Dim logger
Set logger = new ScriptLogger
logger.LogWarning(customMessage)
End Sub
Sub HandleError(ByVal customMessage)
Dim logger
If Err.number <> 0 Then
Set logger = new ScriptLogger
logger.LogError(customMessage)
If Not USE_ERROR_LOG Then
Wscript.Quit 0
Else
Wscript.Quit 1
End If
End If
End Sub
Sub HandleErrorInMonitoring(ByVal customMessage, ByRef oBag, ByRef oAPI)
Dim logger
If Err.number <> 0 Then
Set logger = new ScriptLogger
logger.LogError(customMessage)
Call oAPI.Return(oBag)
Wscript.Quit 0
End If
End Sub
Sub HandleErrorInDiscovery(ByVal customMessage, ByRef oDiscovery, ByRef oAPI)
Dim logger
If Err.number <> 0 Then
Set logger = new ScriptLogger
logger.LogError(customMessage)
oDiscovery.IsSnapshot = false
HandleDebugMessage("Discovery: IsSnapshot = false")
oAPI.Return(oDiscoveryData)
Wscript.Quit 0
End If
End Sub
Sub HandleNonCriticalErrorInDiscovery(ByVal customMessage, ByRef oDiscovery)
Dim logger
Set logger = new ScriptLogger
logger.LogError(customMessage)
oDiscovery.IsSnapshot = false
HandleDebugMessage("Discovery: IsSnapshot = false")
End Sub
Sub HandleDebugMessage(ByVal debugMessage)
Dim logger
Set logger = new ScriptLogger
logger.LogDebug(debugMessage)
End Sub
Private Sub Class_Initialize()
sourceLogEvent = MANAGEMENT_PACK_ID
If (sApplianceID <> "") Then
sourceLogEvent = sourceLogEvent & ". Appliance: " & sApplianceID
End If
sourceLogEvent = sourceLogEvent & ". Script: " & WScript.ScriptName
Set oAPI = CreateObject("MOM.ScriptAPI")
End Sub
Private Function LogEvent(ByVal message, ByVal eventType, ByVal scriptEventID)
On Error Resume Next
Call oAPI.LogScriptEvent(sourceLogEvent, scriptEventID, eventType, message)
End Function
Public Function LogDebug(ByVal message)
if DEBUG_MODE Then
WScript.StdOut.WriteLine message
Call oAPI.LogScriptEvent(sourceLogEvent, DEBUG_SCRIPT_EVENT_ID, INFO_EVENT_TYPE, message)
End If
End Function
Public Function LogWarning(ByVal message)
if DEBUG_MODE Then
WScript.StdOut.WriteLine message
End If
LogEvent message, WARNING_EVENT_TYPE, SCRIPT_EVENT_ID
End Function
Public Function LogError(ByVal customMessage)
Dim message
If Err.number <> 0 Then
message = Replace(" Error Number: #P1# " & VbCrLf & " Description: #P2# ", "#P1#", CStr(Err.number and 65535) )
message = Replace(message, "#P2#", Err.Description )
message = customMessage & VbCrLf & message & VbCrLf
Else
message = customMessage & VbCrLf
End If
If DEBUG_MODE Then
WScript.StdOut.WriteLine message
End If
If USE_EVENT_LOG Then
LogEvent message, ERROR_EVENT_TYPE, SCRIPT_EVENT_ID
End If
If USE_ERROR_LOG Then
WScript.StdErr.WriteLine message
End If
End Function
End Class
Function IsStringEmpty(ByVal sValue)
If sValue = EMPTY Or Trim(sValue) = "" Then
IsStringEmpty = true
Else
IsStringEmpty = false
End If
End Function
Const DEFAULT_SCHEMA = "[sys]"
'This class is wrapper for ADODB typical operations (Connect and Run SQL query)
Class SQLQuery
PRIVATE adodbConnection
Private Sub Class_Initialize()
On Error Resume Next
Set adodbConnection = CreateObject("ADODB.Connection")
Call HandleError("Cannot create ADODB.Connection")
End Sub
Private Sub Class_Terminate()
On Error Resume Next
'adodbConnection.State = 0 - Closed
If adodbConnection.State > 0 Then
Call adodbConnection.Close()
End If
Call HandleError("Cannot close ADODB.Connection")
End Sub
'This method create and open connection to the server
'<connectionString> - DSN to be used for connection;
Public Sub Connect(ByVal connectionString, ByVal sUser, ByVal sPass, ByVal nTimeout)
On Error Resume Next
Dim providerString
providerString = "DSN=" & connectionString & ";UID=" & sUser & ";PWD=" & sPass
adodbConnection.ConnectionTimeout = nTimeout
Call adodbConnection.Open(providerString)
End Sub
'This function execute <query> with <parameters> and returns ADODB.RecordSet
'<parameters> must be array, and must be with the same order as sql query <?> paremetrs
Public Function ExecuteQueryToRecordSet(ByVal query, ByVal parameters)
Dim adodbCommand, oResults
Set adodbCommand = CreateObject("ADODB.Command")
adodbCommand.ActiveConnection = adodbConnection
adodbCommand.CommandText = query
Dim i, dataType, dataSize, parameter, length
length = UBound(parameters)
'Put parameters in to ADODB.Command
For i=0 To length
parameter = parameters(i)
dataType = GetDataTypeEnum(parameter)
dataSize = GetDataTypeSize(parameter)
adodbCommand.Parameters.Append(adodbCommand.CreateParameter("p"&i, dataType, 1, dataSize, parameter)) '1 - this is adParamInput in ADODB. see http://www.w3schools.com/ado/met_comm_createparameter.asp#parameterdirenum
Next
Set ExecuteQueryToRecordSet = adodbCommand.Execute()
End Function
'returns size of string or 0 for others types
Private Function GetDataTypeSize(ByVal vValue)
If TypeName(vValue)="String" Then
GetDataTypeSize = Len(vValue)
Else
GetDataTypeSize = 0
End If
End Function
'this function returns int value of some DataTypeEnum
'values of adodb types see on http://www.w3schools.com/ado/met_comm_createparameter.asp#datatypeenum
Private Function GetDataTypeEnum(ByVal vValue)
If TypeName(vValue) = "Byte" Then
GetDataTypeEnum = 2 ' adSmallInt=2
End If
If TypeName(vValue)="Integer" or TypeName(vValue)="Long" Then
GetDataTypeEnum = 3 ' adInteger=3
End If
If TypeName(vValue)="Double" Then
GetDataTypeEnum = 5 ' adDouble=5
End If
If TypeName(vValue)="String" Then
GetDataTypeEnum = 8 ' adBSTR = 8
End If
End Function
End Class
On Error Resume Next
Dim GetComponentsStatesQuery
GetComponentsStatesQuery = Replace(" SELECT " & _
" nodes.name, " & _
" nodes.pdw_node_id, " & _
" Stat.group_name, " & _
" Stat.component_name, " & _
" CASE " & _
" WHEN Stat.[severity_id] = 0 THEN '?' " & _
" WHEN Stat.[severity_id] = 1 THEN 'Ok' " & _
" WHEN Stat.[severity_id] = 2 THEN 'NonCritical' " & _
" WHEN Stat.[severity_id] = 3 THEN 'Unknown' " & _
" WHEN Stat.[severity_id] = 4 THEN 'Failed' " & _
" WHEN Stat.[severity_id] = 5 THEN 'Critical' " & _
" WHEN Stat.[severity_id] = 6 THEN 'NonRecoverable' " & _
" END AS [component_status]" & _
" FROM ( SELECT " & _
" comp.[pdw_node_id] , " & _
" comp.[group_name] , " & _
" comp.[component_name] , " & _
" MAX ( " & _
" CASE " & _
" WHEN status.[property_value] IS NULL THEN 0 " & _
" WHEN LOWER(status.[property_value]) = 'ok' THEN 1 " & _
" WHEN LOWER(status.[property_value]) = 'noncritical' THEN 2 " & _
" WHEN LOWER(status.[property_value]) = 'unsupported' THEN 2 " & _
" WHEN LOWER(status.[property_value]) = 'unknown' THEN 3 " & _
" WHEN LOWER(status.[property_value]) = 'failed' THEN 4 " & _
" WHEN LOWER(status.[property_value]) = 'critical' THEN 5 " & _
" WHEN LOWER(status.[property_value]) = 'unreachable' THEN 5 " & _
" WHEN LOWER(status.[property_value]) = 'nonrecoverable' THEN 6 " & _
" ELSE 0" & _
" END " & _
" ) as 'severity_id' " & _
" FROM ( SELECT " & _
" n.[pdw_node_id] , " & _
" g.[group_id] , " & _
" g.[group_name] , " & _
" c.[component_id] , " & _
" c.[component_name] " & _
" FROM " & _
" {0}.pdw_health_components c " & _
" CROSS JOIN {0}.dm_pdw_nodes n " & _
" INNER JOIN {0}.pdw_health_component_groups g " & _
" ON g.group_id = c.group_id " & _
" ) comp " & _
" LEFT OUTER JOIN ( SELECT " & _
" s.[pdw_node_id] , " & _
" s.[component_id] , " & _
" s.[component_instance_id] , " & _
" s.[property_id] , " & _
" p.[property_name] , " & _
" s.[property_value] " & _
" FROM " & _
" {0}.dm_pdw_component_health_status s " & _
" JOIN {0}.[pdw_health_component_properties] p " & _
" ON s.property_id = p.property_id AND s.component_id = p.component_id AND p.property_name = 'Status' " & _
" ) status " & _
" ON status.component_id = comp.component_id AND status.pdw_node_id = comp.pdw_node_id " & _
" GROUP BY " & _
" comp.[pdw_node_id] , " & _
" comp.[group_name] , " & _
" comp.[component_name] " & _
" ) Stat " & _
" INNER JOIN {0}.[dm_pdw_nodes] nodes " & _
" ON nodes.[pdw_node_id] = Stat.[pdw_node_id] " & _
" ORDER BY " & _
" nodes.[name], " & _
" Stat.[group_name], " & _
" Stat.[component_name] " & _
" OPTION (LABEL = 'SCOM-PDW-MP');", "{0}", DEFAULT_SCHEMA)
Dim oAPI, oBag
Call Main()
Sub Main()
On Error Resume Next
Dim oParams
Dim oSqlQry
Dim oSecureInput
Dim sUser, sPass
oSecureInput = Split(WScript.StdIn.ReadLine(), "@@secureseparator@@")
sUser = oSecureInput(0)
Call HandleError("Cannot read Username from SecureInput")
sPass = oSecureInput(1)
Call HandleError("Cannot read Password from SecureInput")
Set oAPI = CreateObject("MOM.ScriptAPI")
Call HandleError("Cannot create MOM.ScriptAPI object")
Set oBag = oAPI.CreatePropertyBag()
Call HandleError("Cannot create PropertyBag")
Set oParams = WScript.Arguments
sApplianceID = oParams(0)
Call HandleError("Cannot read Appliance ID from params")
Set oSqlQry = new SQLQuery
Call oSqlQry.Connect(sApplianceID, sUser, sPass, 60)
Call HandleErrorInMonitoring("Connection failed", oBag, oAPI)
Call GetComponentsStates(oSqlQry)
Call oAPI.Return(oBag)
End Sub
Sub GetComponentsStates(ByRef oSqlQry)
On Error Resume Next
Dim oRecordSet
Dim sNodeName, nNodeId, sGroupName, sComponentName, sComponentStatus, sFriendlyName
Set oRecordSet = oSqlQry.ExecuteQueryToRecordSet(GetComponentsStatesQuery, Array())'read all components states
Call HandleErrorInMonitoring("Cannot run query", oBag, oAPI)
Do While Not oRecordSet.EOF
sNodeName = oRecordSet(0)
nNodeId = oRecordSet(1)
sGroupName = oRecordSet(2)
sComponentName = oRecordSet(3)
sComponentStatus = oRecordSet(4)
sFriendlyName = ExtractDisplayName(sNodeName) & ", " & sGroupName & ", " & sComponentName
Call oBag.AddValue(sFriendlyName, sComponentStatus)
Call oRecordSet.MoveNext()
Loop
Call oRecordSet.Close()
End Sub
' Extracts display name from Node or Cluster name
Function ExtractDisplayName(ByVal sFullName)
ExtractDisplayName = Mid(sFullName, InStr(sFullName, "-") + 1)
End Function </Script></ScriptBody>
<SecureInput>$RunAs[Name="PDWLibrary!Microsoft.SQLServerAppliance.PDW2.ActionProfile"]/UserName$@@secureseparator@@$RunAs[Name="PDWLibrary!Microsoft.SQLServerAppliance.PDW2.ActionProfile"]/Password$</SecureInput>
<TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>
</DataSource>
</MemberModules>
<Composition>
<Node ID="DS"/>
</Composition>
</Composite>
</ModuleImplementation>
<OutputType>System!System.PropertyBagData</OutputType>
</DataSourceModuleType>
Home
Show References: