Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: MS OpsMgr, Dynamic groups from CMDB - VBscript Database Data Validation

    Hello,

    I am having some issues with a SQL vbscript i am writing/modifying to populate some dynamic groups in Microsoft System Center Operation Manager 2007 from our CMDB database. I think it has something to do with the need for data validation in my script. I think the CMDB database has entires that SCOM's database does not and it is causing the script to fail.

    Can someone help me make the script validate the data between the two databases before i perform the other tasks in the script. I guess i need some kind of "IF EXIST" statement between the two DBs.

    I've been using the process outlined here Populating groups from a SQL server CMDB step by step

    The VBscript below is what i am using (minus the DB connection string info), im unsure how best to validate the data and make it case INsensitive before performing actions on it.

    Code:
    Dim objConnection
    Dim oRS
    Dim oRS2
    Dim sConnectString
    Dim ManagedEntityID
    Dim oAPI
    Dim oDiscoveryData
    
    SourceId = WScript.Arguments(0)
    ManagedEntityId = WScript.Arguments(1)
    
    Set oAPI = CreateObject("MOM.ScriptAPI")
    Set oDiscoveryData = oAPI.CreateDiscoveryData(0,SourceId,ManagedEntityId)
    Set groupInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name="GroupPopulation.SLA5X10HealthWatchers']$")
    oDiscoveryData.IsSnapshot = true
    
    sConnectString = "Provider=SQLOLEDB;User ID=*******;Password=*******;Initial Catalog=MDB;Data Source=*******;Network Library=DBMSSOCN;"
    
    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open sConnectString
    
    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open "SELECT DISTINCT LTRIM(RTRIM(LOWER(alt_ci_id))) FROM mdb.dbo.vSCOMCIUpdate WHERE service_type LIKE '%7_24%' AND alt_ci_id LIKE '%.au'", objConnection
    
    While Not oRS.EOF
    
    sConnectString2 = "Provider=SQLOLEDB.1;Initial Catalog=OperationsManager;Data Source=*******;Network Library=DBMSSOCN;
    Set objConnection2 = CreateObject("ADODB.Connection")
    objConnection2.Open sConnectString2
    
    Set oRS2 = CreateObject("ADODB.Recordset")
    oRS2.Open("SELECT DISTINCT [Id] FROM [OperationsManager].[dbo].[ManagedEntityGenericView] WHERE LOWER(path) = '" + oRS.Fields("instancename") + "' AND FullName LIKE 'Microsoft.SystemCenter.HealthService:%'"), objConnection2
    
    id = oRS2.Fields("Id")
    id = Left(Right(Id,37),36)
    Set serverInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='SC!Microsoft.SystemCenter.AgentWatcher']$")
    Set relationshipInstance = oDiscoveryData.CreateRelationshipInstance("$MPElement[Name='GroupPopulation.SLA5X10ContainsHealthWatchers']$")  
    serverInstance.AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$",oRS.Fields("InstanceName")
    serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatcher']/HealthServiceId$",id
    serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatcher']/HealthServiceName$",oRS.Fields("InstanceName")
    serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatchersGroup']/WatcherGroupName$","Microsoft.SystemCenter.AgentWatchersGroup"
    relationshipInstance.Source = groupInstance
    relationshipInstance.Target = serverInstance
    oDiscoveryData.AddInstance relationshipInstance
    oRS.MoveNext
    
    Wend
    
    objConnection2.Close
    objConnection.Close
    
    Call oAPI.Return(oDiscoveryData)
    Thanks for your help.

  2. #2
    Join Date
    Jun 2012
    Posts
    2
    I managed to get this working myself. For those that are interested here is the working VBS script.

    The problem i had was the CMDB we use includes a huge number of items that may or may not have agents installed in SCOM so i needed to do some error handling and then use the information from the Datawarehouse which will always be in an expected format for itself.

    FYI, this script will populate with "Health Service Watcher" items.

    Code:
    Option Explicit
    
    Dim objConnection
    Dim objConnection2
    Dim oRS
    Dim oRS2
    Dim sConnectString
    Dim sConnectString2
    Dim SourceId
    Dim ManagedEntityID
    Dim oAPI
    Dim oDiscoveryData
    Dim groupInstance
    Dim serverInstance
    Dim relationshipInstance
    Dim id
    
    SourceId = WScript.Arguments(0)
    ManagedEntityId = WScript.Arguments(1)
    
    Set oAPI = CreateObject("MOM.ScriptAPI")
    Set oDiscoveryData = oAPI.CreateDiscoveryData(0,SourceId,ManagedEntityId)
    Set groupInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='Group.CAASLA5X16.HealthWatchers']$")
    oDiscoveryData.IsSnapshot = true
    
    sConnectString = "Provider=SQLOLEDB;User ID=**********;Password=*********;Initial Catalog=MDB;Data Source=**********;"
    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open sConnectString
    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open("SELECT DISTINCT [resource_name] FROM [database] WHERE [inactive_flag] != '1' AND [service_type] LIKE '%16%' AND [service_contract] LIKE '%CAA'"), objConnection
    
    While Not oRS.EOF
    	sConnectString2 = "Provider=SQLOLEDB.1;Data Source=********;Initial Catalog=OperationsManager;User Id=**********;Password=*********;"
    	Set objConnection2 = CreateObject("ADODB.Connection")
    	objConnection2.Open sConnectString2
    	Set oRS2 = CreateObject("ADODB.Recordset")
    	oRS2.Open("SELECT DISTINCT [Id], [Path] FROM [OperationsManager].[dbo].[ManagedEntityGenericView] WHERE FullName LIKE '" + "Microsoft.SystemCenter.HealthService:" + LTRIM(RTRIM(oRS.Fields("resource_name"))) + "%" + "'"), objConnection2
    	
    	If Not oRS2.BOF Then
    		oRS2.MoveFirst
    	End If
    	If Not oRS2.EOF Then
    		If Not IsNull(oRS2.Fields("Id")) AND oRS2.Fields("Id") <> "" Then
    			id = oRS2.Fields("Id")
    			id = Left(Right(Id,37),36)
    			Set serverInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='SC!Microsoft.SystemCenter.AgentWatcher']$")
    			Set relationshipInstance = oDiscoveryData.CreateRelationshipInstance("$MPElement[Name='Group.CAASLA5X16.ContainsHealthWatchers']$")  
    			serverInstance.AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$",oRS2.Fields("Path")
    			serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatcher']/HealthServiceId$",id
    			serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatcher']/HealthServiceName$",oRS2.Fields("Path")
    			serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatchersGroup']/WatcherGroupName$","Microsoft.SystemCenter.AgentWatchersGroup"
    			relationshipInstance.Source = groupInstance
    			relationshipInstance.Target = serverInstance
    			oDiscoveryData.AddInstance relationshipInstance
    		End If
    	End If
    	oRS.MoveNext
    Wend
    
    objConnection2.Close
    objConnection.Close
    Set objConnection2 = nothing
    Set objConnection = nothing
    
    Call oAPI.Return(oDiscoveryData)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •