<Diagnostic ID="Microsoft.SystemCenter.2007.OpsMgrDB.DetectDuplicateRelationshipsforAgentsToServers.Diagnostic" Accessibility="Public" Enabled="false" Target="SCLibrary!Microsoft.SystemCenter.OpsMgrDBWatcher" Monitor="Microsoft.SystemCenter.OpsMgrDB.DetectDuplicateRelationshipsforAgentsToServers" ExecuteOnState="Error" Remotable="true" Timeout="300">
<Category>Maintenance</Category>
<ProbeAction ID="Probe" TypeID="Microsoft.SystemCenter.2007.OpsMgrDB.Generic.DS">
<TimeoutSeconds>300</TimeoutSeconds>
<ScriptName>DetectDuplicateRelAgnToSrvDiagnostic.vbs</ScriptName>
<ScriptBody><Script>
'Copyright (c) Microsoft Corporation. All rights reserved.
'*************************************************************************
' $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.
'*************************************************************************
' $ScriptName: "DetectDuplicateRelAgnToSrvDiagnostic" $
'
' Purpose: This script reports duplicates relationship between agents and servers.
'
' $File: DetectDuplicateRelAgnToSrvDiagnostic.vbs $
'*************************************************************************
'Declarations
Dim objCN,objRS,strQuery, intRecordsCount
Dim oArgs,oAPI,oBag
Dim strDBServer,strDatabase, strDriverName
Dim strRelationshipTypeName, strFullName, strLastModified, strDiscoverySource, strTimeGenerated
'Set DB connection
Set objCN = CreateObject("ADODB.Connection")
strDriverName = GetSqlDriverName
objCN.Open "Provider=" & strDriverName &";Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & _
strDatabase & ";Data Source=" & strDBServer & ""
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
Set oBag = oAPI.CreatePropertyBag()
Call oBag.AddValue("Error", "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description)
Call oAPI.Return(oBag)
oAPI.LogScriptEvent "DetectDuplicateRelAgnToSrvDiagnostic.vbs",100,EVENT_TYPE_ERROR,"Script executed with Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
WScript.Quit
End If
strQuery = "SELECT RT.RelationshipTypeName,S.FullName ,R.LastModified ,Coalesce(D.DiscoveryName, '(system)') As DiscoverySource ,DSR.TimeGenerated " & _
"FROM Relationship R (NoLock) " & _
"JOIN RelationshipType RT (NoLock) ON RT.RelationshipTypeId = R.RelationshipTypeId " & _
"JOIN BaseManagedEntity S (NoLock) ON R.SourceEntityId = S.BaseManagedEntityId " & _
"JOIN DiscoverySourceToRelationship DSR (NoLock) ON DSR.RelationshipId = R.RelationshipId " & _
"Left Outer Join DiscoverySource DS (NoLock) ON DS.DiscoverySourceId = DSR.DiscoverySourceId " & _
"Left Outer Join Discovery D (NoLock) ON D.Discoveryid = DS.DiscoveryRuleId " & _
"Where R.IsDeleted = 0 " & _
"And RT.RelationshipTypeName IN ('Microsoft.SystemCenter.HealthServiceCommunication', 'Microsoft.SystemCenter.HealthServiceSecondaryCommunication') " & _
"And R.RelationshipId NOT IN " & _
"( " & _
" SELECT RO.RelationshipId " & _
" FROM " & _
" ( " & _
" SELECT RANK() OVER(PARTITION BY R1.SourceEntityId, R1.TargetEntityId ORDER BY R1.TimeAdded asc) as RowNumber, " & _
" R1.RelationshipId " & _
" FROM Relationship R1 (NoLock) " & _
" JOIN RelationshipType RT1 (NoLock) " & _
" ON RT1.RelationshipTypeId = R1.RelationshipTypeId " & _
" WHERE RT1.RelationshipTypeName In ('Microsoft.SystemCenter.HealthServiceCommunication','Microsoft.SystemCenter.HealthServiceSecondaryCommunication') " & _
" AND R1.IsDeleted = 0 " & _
" Group By R1.RelationshipId,R1.SourceEntityId, R1.TargetEntityId, R1.TimeAdded " & _
" ) As RO " & _
" Where RO.RowNumber = 1 " & _
") " & _
"Order By R.LastModified Desc"
'Query DB
Set objRS = objCN.Execute(strQuery)
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
Set oBag = oAPI.CreatePropertyBag()
Call oBag.AddValue("Error", "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description)
Call oAPI.Return(oBag)
oAPI.LogScriptEvent "DetectDuplicateRelAgnToSrvDiagnostic.vbs",100,EVENT_TYPE_ERROR,"Script executed with Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
WScript.Quit
End If
oAPI.LogScriptEvent "DetectDuplicateRelAgnToSrvDiagnostic.vbs",100,EVENT_TYPE_INFORMATION,"Script executed with Duplicate Records Count " & CStr(intRecordsCount)
Public Sub Save()
m_lNumber = 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 </Script></ScriptBody>
<DatabaseServerName>$Target/Property[Type="SCLibrary!Microsoft.SystemCenter.OpsMgrDBWatcher"]/DatabaseServerName$</DatabaseServerName>
<DatabaseName>$Target/Property[Type="SCLibrary!Microsoft.SystemCenter.OpsMgrDBWatcher"]/DatabaseName$</DatabaseName>
<Threshold>0</Threshold>
</ProbeAction>
</Diagnostic>
Home
ENU