'*************************************************************************
' $ScriptName: "FixDupRelAgsToSrvrsTask" $
'
' Purpose: This script fix duplicates relationship between agents and servers.
'
' $File: FixDupRelAgsToSrvrsTask.vbs $
'*************************************************************************
Option Explicit
'Declarations
Dim objCN,objRS,objRS2, strQuery, intRecordsCount
Dim oArgs,oAPI,oBag
Dim strDBServer,strDatabase
Dim strRelationshipId, strDiscoverySourceId
'Set DB connection
Set objCN = CreateObject("ADODB.Connection")
objCN.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & _
strDatabase & ";Data Source=" & strDBServer & ""
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
WScript.Echo "Task Fail - Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
oAPI.LogScriptEvent "FixDupRelAgsToSrvrsTask.vbs",100,EVENT_TYPE_ERROR,"Script executed with Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
WScript.Quit
End If
intRecordsCount = 0
strQuery = "SELECT R.RelationshipId, DS.DiscoverySourceId " & _
"FROM Relationship R (NoLock) " & _
"JOIN RelationshipType RT (NoLock) ON RT.RelationshipTypeId = R.RelationshipTypeId " & _
"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
WScript.Echo "Task Fail - Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
oAPI.LogScriptEvent "FixDupRelAgsToSrvrsTask.vbs",100,EVENT_TYPE_ERROR,"Script executed with Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
WScript.Quit
End If
Do While objRS.EOF = false
intRecordsCount = intRecordsCount + 1
strRelationshipId = objRS.Fields(0).Value
strDiscoverySourceId = objRS.Fields(1).Value
strQuery = "declare @RelationshipId uniqueidentifier;" & _
"declare @DiscoverySourceId uniqueidentifier;" & _
"declare @TimeGenerated datetime;" & _
"set @RelationshipId = '" & strRelationshipId & "'; " & _
"set @DiscoverySourceId = '" & strDiscoverySourceId & "';" & _
"set @TimeGenerated = getutcdate();" & _
"begin transaction " & _
"exec p_RemoveRelationshipFromDiscoverySourceScope @RelationshipId, @DiscoverySourceId, @TimeGenerated " &_
"commit transaction"
On Error Resume Next
Set objRS2 = objCN.Execute(strQuery)
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
WScript.Echo "Task Fail - Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
oAPI.LogScriptEvent "FixDupRelAgsToSrvrsTask.vbs",100,EVENT_TYPE_ERROR,"Script executed with Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
WScript.Quit
End If
objRS.MoveNext()
Loop
'Check for error condition before continuing.
If ObjError.Number <> 0 Then
WScript.Echo "Task Fail - Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
oAPI.LogScriptEvent "FixDupRelAgsToSrvrsTask.vbs",100,EVENT_TYPE_ERROR,"Script executed with Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
WScript.Quit
End If
Set objRS = Nothing
Set objRS2 = Nothing
Set objCN = Nothing
oAPI.LogScriptEvent "FixDupRelAgsToSrvrsTask.vbs",100,EVENT_TYPE_INFORMATION,"Script executed to fix Duplicate Records"
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></Contents>
<Unicode>true</Unicode>
</File>
</Files>
</WriteAction>
</MemberModules>
<Composition>
<Node ID="RunScript"/>
</Composition>
</Composite>
</ModuleImplementation>
<InputType>System!System.TriggerData</InputType>
</WriteActionModuleType>
Home
CHT 
Show References: