We are attempting to create some reports by running direct queries against the DFM database. We would like to query against a specific group defined in Operations Manager.
Reading through the database schema, it looks like the following fields may help:
View name: resGroupMembersView
ID of the resource group
ID of the member object
But we cannot figure out how to run a query pulling objects (such as aggregates and volumes) and limit the output to a specific group.
Here's an example of a query we built that is running against every aggregate and volume on the system:
dfm database query run -F csv "select objName AS 'Aggregate', str(aggrTotalSpaceKB/1024/1024/1024,5,2) AS 'Total', str(aggrTotalCommittedSpaceKB/1024/1024/1024,5,2) AS 'Committed', str((((aggrTotalCommittedSpaceKB/1024/1024/1024) / (aggrTotalSpaceKB/1024/1024/1024))*100),5,2) AS 'Committed %', str(aggrUsedCapacityKB/1024/1024/1024,5,2) AS 'Used', str((((aggrUsedCapacityKB/1024/1024/1024) / (aggrTotalSpaceKB/1024/1024/1024))*100),5,2) AS 'Used %', str(aggrAvailableSpaceKB /1024/1024/1024,5,2) AS 'Available', count(volId) AS 'FlexVols' from objectView, aggregateView, volumeView where objectView.objId = aggregateView.aggrId and volumeView.volAggrId = aggregateView.aggrId and objName <> 'aggr0' and objName <> 'vol0' and objName <> 'aggr1' group by objName, aggrTotalSpaceKB, aggrTotalCommittedSpaceKB, aggrUsedCapacityKB, aggrAvailableSpaceKB order by objName"
Is there a way we can limit this to query against a specific group of systems? It doesn't look like the relationships between views allow us to do this.
Also, is there a way to build our own database views?