'*************************************************************************
' $ScriptName: "GetSqlNativeClientComman" $
'
' Purpose: This script is used to get SQL native client Driver name.
'
' $File: GetSqlNativeClientComman.vbs $
'*************************************************************************
Option Explicit
Function GetSqlDriverName
Dim oReg
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_CLASSES_ROOT = &H80000000
Dim strComputer, strDriverPath, strKeyPath, intValue, strValueName,arrSubKeys,oSubkey,strNewKeyPath,intDotIndex, strDriverPathFromHKeyClassRoot
strComputer = "."
strDriverPath="SQLOLEDB"
Set oReg=GetObject("winmgmts:\\" & _
strComputer & "\root\default:StdRegProv")
strKeyPath = "CLSID\{5A23DE84-1D7B-4A16-8DED-B29C09CB648D}"
'strDriverPathFromHKeyClassRoot is used to check driver MSOLEDBSQL is installed or not. If found MSOLEDBSQL will be returned from function else strDriverPath="SQLOLEDB" will be used for further checking.
oReg.GetStringValue HKEY_CLASSES_ROOT, strKeyPath, "", strDriverPathFromHKeyClassRoot
If strDriverPathFromHKeyClassRoot = "MSOLEDBSQL" Then
GetSqlDriverName = strDriverPathFromHKeyClassRoot
Exit Function
End If
For Each oSubkey In arrSubKeys
intValue = InStr(oSubkey, "SQL Server Native Client")
if intValue <> 0 Then
strValueName = "Driver"
strNewKeyPath = strKeyPath&"\"&oSubkey
oReg.GetStringValue HKEY_LOCAL_MACHINE, strNewKeyPath, strValueName , strDriverPath
strDriverPath = Mid (strDriverPath, InStrRev(strDriverPath, "\")+1)
intDotIndex = InStr(strDriverPath, ".")
strDriverPath = Left(strDriverPath , intDotIndex-1)
end if
Next
GetSqlDriverName = strDriverPath
End Function
'Copyright (c) Microsoft Corporation. All rights reserved.
'Declarations
Dim objCN,objRS,strQuery
Dim oArgs,oAPI, oReg
Dim strCNString
Dim strComputerName, strAgentName, strMessageToUse
Dim strAvailabilityLastModified, strLastSubmittedConfigurationDateTime, strReasonCode, strServerName, strPingStatus, strHealthServiceState
Dim stroffsetMin
If ObjError.Number <> 0 Then
strMessageToUse = "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
call GetResponse(true, strMessageToUse, strComputerName, "", "", "", "", "", "")
End If
'Set DB connection
Set objCN = CreateObject("ADODB.Connection")
objCN.Open strCNString
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
strMessageToUse = "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
call GetResponse(true, strMessageToUse, strComputerName, "", "", "", "", "", "")
End If
strQuery = "SELECT MEG.DisplayName As AgentName, MEG.AvailabilityLastModified , AGN.LastSubmittedConfigurationDateTimeUtc, AVL.ReasonCode, MEV.DisplayName As ServerName " & _
" FROM ManagedEntityGenericView MEG " & _
" INNER JOIN ManagedTypeView MTV ON MEG.MonitoringClassId = MTV.Id " & _
" INNER JOIN CS.Agent AGN ON AGN.NetworkName = MEG.DisplayName " & _
" INNER JOIN Availability AVL ON AVL.BaseManagedEntityId = MEG.BaseManagedEntityId " & _
" INNER JOIN Relationship REL ON REL.SourceEntityId = MEG.Id " & _
" INNER JOIN RelationshipType RET ON REL.RelationshipTypeId = RET.RelationshipTypeId And RET.RelationshipTypeName = 'Microsoft.SystemCenter.HealthServiceCommunication' " & _
" INNER JOIN ManagedEntityGenericView MEV ON MEV.Id = REL.TargetEntityId AND MEV.MonitoringClassId = RET.TargetManagedTypeId " & _
" WHERE MTV.Name = 'microsoft.systemCenter.agent' " & _
" AND MEG.IsAvailable = 0 " & _
" And MEG.DisplayName = '" & strComputerName & "'" & _
" ORDER BY MEG.DisplayName"
On Error Resume Next
'Query DB
Set objRS = objCN.Execute(strQuery)
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
strMessageToUse = "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
call GetResponse(true, strMessageToUse, strComputerName, "", "", "", "", "", "")
End If
If ISNULL(strLastSubmittedConfigurationDateTime) = false Then
strLastSubmittedConfigurationDateTime = LocalDateString(strLastSubmittedConfigurationDateTime, stroffsetMin)
End If
If ObjError.Number <> 0 Then
strMessageToUse = "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
call GetResponse(true, strMessageToUse, strComputerName, "", "", "", "", "", "")
Else
strMessageToUse = "Script GetGrayAgentConnectivityData executed Successfully"
call GetResponse(false, strMessageToUse, strComputerName, strAvailabilityLastModified, strLastSubmittedConfigurationDateTime, strReasonCode, strServerName, strPingStatus, strHealthServiceState)
End If
'---------------------------------------------------------------------------
' Retrieves the script output.
'---------------------------------------------------------------------------
Sub GetResponse(boolErrorFlag, strMessage, strComputerName, strAvailabilityLastModified, strLastSubmittedConfigurationDateTime, strReasonCode, strServerName, strPingStatus, strHealthServiceState)
If boolErrorFlag = true Then
WScript.Echo strMessage
Else
WScript.Echo "Agent Name: " & strComputerName
WScript.Echo "Last Time Agent Heartbeat Was Received: " & strAvailabilityLastModified
WScript.Echo "Is Health Service Running On Agent: " & strHealthServiceState
WScript.Echo "Is Agent Responding To Ping: " & strPingStatus
WScript.Echo "Last Time Configuration Was Updated On Agent: " & strLastSubmittedConfigurationDateTime
WScript.Echo "Reason the agent is unavailable: " & strReasonCode
WScript.Echo "Management Server That The Agent Reports To: " & strServerName
End If
oAPI.LogScriptEvent "GetGrayAgentConnectivityData.vbs",100,EVENT_TYPE_INFORMATION, strMessage
WScript.Quit
End Sub
'---------------------------------------------------------------------------
' Gets Ping Status.
'---------------------------------------------------------------------------
Function GetPingStatus(strComputerName)
Dim Status
Status = ExecuteWMIQuery(strComputerName, "cimv2", "SELECT StatusCode, ResponseTime FROM Win32_PingStatus WHERE Address = '" + strComputerName + "'", "StatusCode")
If Status = "0" Then
GetPingStatus = "Yes"
Else
GetPingStatus = "No"
End If
End Function
'---------------------------------------------------------------------------
' Gets The Health Service State.
'---------------------------------------------------------------------------
Function GetHealthServiceState(strComputerName)
Dim State
State = ExecuteWMIQuery(strComputerName, "cimv2", "SELECT State FROM Win32_Service WHERE name = 'HealthService'", "State")
If State = "Running" Then
GetHealthServiceState = "Running"
Else
GetHealthServiceState = "Not Running"
End If
End Function
'---------------------------------------------------------------------------
' Retrieves a WMI object from the specified namespace.
'---------------------------------------------------------------------------
Function GetWMIObject(ByVal sNamespace)
dim oWMI
'get the object
on error resume next
set oWMI = GetObject(sNamespace)
ObjError.Save
on error goto 0
'did it work?
if IsEmpty(oWMI) then
'no
strMessageToUse = "Error Number: " & ObjError.number & " Unable to open WMI Namespace '" & sNamespace & "'. Check to see if the WMI service is enabled and running, and ensure this WMI namespace exists."
call GetResponse(true, strMessageToUse, strComputerName, "", "", "", "", "", "")
end if
set GetWMIObject = oWMI
End Function
'---------------------------------------------------------------------------
' Retrieves a WMI registry object
'---------------------------------------------------------------------------
Function GetRegistryObject(ByVal sComputerName)
set GetRegistryObject = GetWMIObject("winmgmts:{impersonationLevel=impersonate}!\\" & sComputerName & "\root\default:StdRegProv")
End Function
'---------------------------------------------------------------------------
' Execute a WMI Query.
'---------------------------------------------------------------------------
Function ExecuteWMIQuery(TargetComputer, strBaseClass, strQuery, strPropertyName)
Dim WbemSrv, WbemObjectSet, objItem
Set WbemSrv = GetWMIObject("winmgmts:{impersonationLevel=impersonate}!\\" & TargetComputer & "\root\" & strBaseClass)
Set WbemObjectSet = WbemSrv.ExecQuery(strQuery)
For Each objItem in WbemObjectSet
ExecuteWMIQuery = objItem.Properties_(strPropertyName).value
Next
End Function
'---------------------------------------------------------------------------
' Retrieves a Ops Manager DB Connection String.
'---------------------------------------------------------------------------
Function GetOpsMgrDBConnectionString(reg)
Dim sDBName, sDBServerName, strDriverName
'get db name
on error resume next
reg.GetStringValue HKEY_LOCAL_MACHINE, SCOM_SETUP_BASE_KEY, SCOM_DB_NAME_VALUE, sDBName
ObjError.Save
on error goto 0
if ObjError.Number <> 0 then
strMessageToUse = "Error Number: " & ObjError.number & " Unable to determine SCOM Database name. Please verify that Operations Manager is installed on this computer."
call GetResponse(true, strMessageToUse, strComputerName, "", "", "", "", "", "")
end if
'get db server name
on error resume next
reg.GetStringValue HKEY_LOCAL_MACHINE, SCOM_SETUP_BASE_KEY, SCOM_DB_SERVER_VALUE, sDBServerName
ObjError.Save
on error goto 0
if ObjError.Number <> 0 then
strMessageToUse = "Error Number: " & ObjError.number & " Unable to determine SCOM Database Server name. Please verify that Operations Manager is installed on this computer."
call GetResponse(true, strMessageToUse, strComputerName, "", "", "", "", "", "")
end if
'generate connection string and return
strDriverName = GetSqlDriverName
GetOpsMgrDBConnectionString = "Provider=" & strDriverName &";Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & sDBName & ";Data Source=" & sDBServerName & ""
End Function
'---------------------------------------------------------------------------
' Retuns Reason Description
'---------------------------------------------------------------------------
Function GetReasonDescText(strReasonCode)
Select Case strReasonCode
Case 0
GetReasonDescText = "UnKnown"
Case 1
GetReasonDescText = "Unavailable- No heartbeat"
Case 17
GetReasonDescText = "Connector Service Paused"
Case 25
GetReasonDescText = "Action Account Issue"
Case 41
GetReasonDescText = "Config Data Handling Issue"
Case 42
GetReasonDescText = "Config Data Loading Issue"
Case 43
GetReasonDescText = "System Workflows Unloaded"
Case 49
GetReasonDescText = "Entity State Collection Stalled"
Case 50
GetReasonDescText = "Monitor State Collection Stalled"
Case 51
GetReasonDescText = "Alert Collection Stalled"
Case 97
GetReasonDescText = "Solution Event Source Not Open"
End Select
End Function
'------------------------------------------------------------------------------
' Retrieves a Windows Time Zone Setting (Bias) from Registry.
' Function to obtain Windows TimeZone setting to convert from UTC to Local Time
'------------------------------------------------------------------------------
Function GetBias(reg)
Dim strBiasValue
on error resume next
reg.GetDWORDValue HKEY_LOCAL_MACHINE, WINDOWS_TIMEZONE_KEY, WINDOWS_TIMEZONE_VALUE, strBiasValue
ObjError.Save
on error goto 0
if ObjError.Number <> 0 then
strMessageToUse = "Error Number: " & ObjError.number & " Unable to get BIAS value."
call GetResponse(true, strMessageToUse, strComputerName, "", "", "", "", "", "")
end if
GetBias = -(strBiasValue)
End Function
'---------------------------------------------------------------------------
' Gets Local Date String
'---------------------------------------------------------------------------
Function LocalDateString(dtmInstallDate, offsetMin)
Dim od, ad
on error resume next
od = CDate(dtmInstallDate)
on error goto 0
if ObjError.Number <> 0 then
strMessageToUse = "Error Number: " & ObjError.number & " Unable to Set Date time."
call GetResponse(true, strMessageToUse, strComputerName, "", "", "", "", "", "")
end if
' OffSetMin Formula: UTC = local time + bias.
ad = dateadd("n", offsetMin, od)
on error goto 0
if ObjError.Number <> 0 then
strMessageToUse = "Error Number: " & ObjError.number & " Unable to Convert To Local Date time."
call GetResponse(true, strMessageToUse, strComputerName, "", "", "", "", "", "")
end if
LocalDateString = ad
End Function
'---------------------------------------------------------------------------
' Error Handling Class
'---------------------------------------------------------------------------
Class Error
Private m_lNumber
Private m_sSource
Private m_sDescription
Private m_sHelpContext
Private m_sHelpFile
Public Sub Save()
m_lNumber = Hex(Err.number)
m_sSource = Err.Source
m_sDescription = Err.Description
m_sHelpContext = Err.HelpContext
m_sHelpFile = Err.helpfile
End Sub
Public Sub Raise()
Err.Raise m_lNumber, m_sSource, m_sDescription, m_sHelpFile, m_sHelpContext
End Sub
Public Sub Clear()
m_lNumber = 0
m_sSource = ""
m_sDescription = ""
m_sHelpContext = ""
m_sHelpFile = ""
End Sub
Public Default Property Get Number()
Number = m_lNumber
End Property
Public Property Get Source()
Source = m_sSource
End Property
Public Property Get Description()
Description = m_sDescription
End Property
Public Property Get HelpContext()
HelpContext = m_sHelpContext
End Property
Public Property Get HelpFile()
HelpFile = m_sHelpFile
End Property
End Class
Home
ENU 
Show References: