Populating groups from a SQL server CMDB – step by step

re: Populating groups from a SQL server CMDB – step by step

SteveH — Wed, 28 Jan 2009 01:43:03 GMT

I used something similar to this to create a "Critical Servers List". But I did not use a DB but a text file sitting on the RMS.

This allowed me to monitor our whole environment but only be alerted in the middle of the night for critical servers.

re: Populating groups from a SQL server CMDB – step by step

Nomad — Wed, 28 Jan 2009 22:28:23 GMT

Fantastic post. We needed this about a year ago.

re: Populating groups from a SQL server CMDB – step by step

ziembor — Tue, 03 Feb 2009 01:03:51 GMT

nice. But I would rather prefer see such "manual" using Autoring console :)

re: use the auth console

kevinhol — Tue, 03 Feb 2009 01:36:32 GMT

I agree. I plan to document just that.... would be nice - huh?

re: Populating groups from a SQL server CMDB – step by step

Alex — Wed, 18 Mar 2009 12:07:29 GMT

I try create group which contain agent with heartbeat failures.

But have problem. Could you view my MP and may be you see wrong code ?

...

</ClassTypes>

<Source>GroupPopulation.Team1Group</Source>

<Target>SC!Microsoft.SystemCenter.AgentWatcher</Target>

</RelationshipType>

</RelationshipTypes>

</EntityTypes>

</TypeDefinitions>

<Category>Discovery</Category>

</DiscoveryTypes>

<ScriptName>Team1GroupDiscoverynew.vbs</ScriptName>

<Arguments>$MPElement$ $Target/Id$</Arguments>

<ScriptBody><![CDATA[Dim SourceId

Dim objConnection

Dim oRS

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)

sConnectString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OperationsManager;Data Source=."

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open sConnectString

Set oRS = CreateObject("ADODB.Recordset")

oRS.Open "SELECT ManagedEntityGenericView.Id FROM ManagedEntityGenericView INNER JOIN ManagedTypeView ON ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.Id WHERE (ManagedEntityGenericView.IsAvailable = 'false') AND (ManagedTypeView.Name = 'Microsoft.SystemCenter.Agent') ORDER BY ManagedEntityGenericView.AvailabilityLastModified ", objConnection

Set groupInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='GroupPopulation.Team1Group']$")

While Not oRS.EOF

Set serverInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='SC!Microsoft.SystemCenter.AgentWatcher']$")

serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatcher']/HealthServiceId$",oRS.Fields("Id")

serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatchersGroup']/WatcherGroupName$","Microsoft.SystemCenter.AgentWatchersGroup"

Set relationshipInstance = oDiscoveryData.CreateRelationshipInstance("$MPElement[Name='GroupPopulation.Team1GroupContainsWindowsComputers']$")

relationshipInstance.Source = groupInstance

relationshipInstance.Target = serverInstance

'Set serverInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='SC!Microsoft.SystemCenter.AgentWatcher']$")

'serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatcher']/HealthServiceId$",oRS.Fields("Id")

'serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatchersGroup']/WatcherGroupName$","{8dd68103-6ee8-2654-2c32-bb9339a8e53b}"

'Set relationshipInstance = oDiscoveryData.CreateRelationshipInstance("$MPElement[Name='GroupPopulation.Team1GroupContainsWindowsComputers']$")

'relationshipInstance.Source = groupInstance

'relationshipInstance.Target = serverInstance

'oDiscoveryData.AddInstance relationshipInstance

oRS.MoveNext

Wend

objConnection.Close

Call oAPI.Return(oDiscoveryData)]]></ScriptBody>

</DataSource>

</Discovery>

</Discoveries>

.....

The group is empety, and I have error 33333 and 10801 (about Invalid relationship target specified in the discovery data item.)

Thanks

re: Populating groups from a SQL server CMDB – step by step

Alex — Wed, 18 Mar 2009 12:11:25 GMT

oops

'oDiscoveryData.AddInstance relationshipInstance

without '

but problem not resolved yet.

re: Populating groups from a SQL server CMDB – step by step

ziembor — Sat, 04 Apr 2009 02:21:36 GMT

How to avoid error: 'The group can not be modified becouse trere is no group population rule' on OM Console/Authoring/Groups/Properies?

re: Populating groups from a SQL server CMDB – step by step

mccocha — Fri, 17 Apr 2009 18:03:30 GMT

I am having the same problem with 'The group can not be modified because there is no group population rule' when attempting to view the properties of my group. Additionally, no group members are created.

re: Populating groups from a SQL server CMDB – step by step

jg — Thu, 28 May 2009 23:30:31 GMT

This is very cool but I would like to read in from a text or csv. What would be the syntax to read in from a csv file?

re: text or CSV

kevinhol — Thu, 28 May 2009 23:38:11 GMT

That would simply be a modification to the VBscript - instead of querying SQL for your recordset - you parse the text file. There are lots of examples on the web for this.

re: Populating groups from a SQL server CMDB – step by step

shall@troweprice.com — Fri, 12 Jun 2009 00:03:28 GMT

This is very nice. However, I'm getting a "ADODB.Connection: Provider is not specified and there is no designated default provider." error. This is likely a 64-bit RMS issue. Anyone have a solution?

re: Populating groups from a SQL server CMDB – step by step

shall@troweprice.com — Fri, 12 Jun 2009 02:41:27 GMT

Problem solved: Used this:

sConnectString = "Provider=SQLOLEDB;User ID=<username>;Password=<Password>;Initial Catalog=<database>;Data Source=<server\Instance>;Network Library=DBMSSOCN;"