Jan 112012
 

Hi All

So for this project I’ve been working on recently, I’ve had to write a script to get the names of nodes and the groups they are a part of out of OMw9.
Thought I’d share the SQL query I used to retrieve the data I needed, though this query will only get the parent group of the node and not anything above that.


SELECT
SUBSTRING(a.object_text,CHARINDEX('n = "',a.object_text)+5,(CHARINDEX('Certif',a.object_text) - CHARINDEX('n = "',a.object_text)-9)) as NodeName,
SUBSTRING(c.object_text,CHARINDEX('n = "',c.object_text)+5,(CHARINDEX('Name = "',c.object_text) - CHARINDEX('n = "',c.object_text)-9)) as GroupName
FROM sto_ov_managednode a
LEFT OUTER JOIN
sto_ov_nodegroupmember b
ON
b.partcomponent LIKE '%'+a.name+'%'
LEFT OUTER JOIN
sto_ov_nodegroup c
ON
b.groupcomponent LIKE '%'+c.name+'%'

With that query you can narrow it down with a where clause at the end, for example


WHERE
c.object_text LIKE '%net devices%'

Will get all nodes in the net devices node group.

Share