I was asked last week if knew a query that would return the size of the databases that have been discovered by Operations Manager. I went about digging through some old queries I put together and found the information that was requested.
Inside the OperationsManager database look for a view named MTV_Database. All discovered databases will be there. There should be a column for DatabaseSize, but the column will have a GUID at the end of the name.
Now, I forget where I found it, but a while back I came across one of the view definitions that helped me to return a lot of other information, such as SQL version. The view that has SQL version is named MTV_DBEngine, but in order to join the MTV_Database to the MTV_DBEngine I needed to add in a handful of relationship tables and filter on the BaseManagedTypeID. I was going mad trying to make everything work until I found that specific view definition and I don’t recall which one it is, but what I do have is the end result which I can share (your GUIDs will be different, I imagine):
SELECT *
FROM [OperationsManager].[dbo].MT_Database T1
LEFT JOIN [OperationsManager].[dbo].MT_Database_2 T2
ON T1.BaseManagedEntityId = T2.BaseManagedEntityId
LEFT JOIN [OperationsManager].[dbo].MT_Database_1 T3
ON T1.BaseManagedEntityId = T3.BaseManagedEntityId
LEFT JOIN [OperationsManager].[dbo].MT_Database_0 T4
ON T1.BaseManagedEntityId = T4.BaseManagedEntityId
LEFT JOIN [OperationsManager].[dbo].[Relationship] RT5
ON (RT5.RelationshipTypeId = ‘328C1DCD-4AA5-9964-F3B6-0E607AE58416’
AND RT5.TargetEntityId = T1.BaseManagedEntityId)
LEFT JOIN [OperationsManager].[dbo].MTV_DBEngine T5
ON RT5.SourceEntityId = T5.BaseManagedEntityId
LEFT JOIN [OperationsManager].[dbo].[Relationship] RT6
ON (RT6.RelationshipTypeId = ‘6284518A-90CC-FADD-E56B-26669E2A0A8F’
AND RT6.TargetEntityId = T5.BaseManagedEntityId)
LEFT JOIN [OperationsManager].[dbo].MTV_Computer T6
ON RT6.SourceEntityId = T6.BaseManagedEntityId
JOIN [OperationsManager].[dbo].BaseManagedEntity BME
ON BME.BaseManagedEntityId = T1.BaseManagedEntityId
WHERE BME.IsDeleted = 0
AND BME.BaseManagedTypeId = ’10C1C7F7-BA0F-5F9B-C74A-79A891170934′
I think what you want to do is first find the BaseManagedTypeId for the ‘Microsoft.SQLServer.Database’ entity (that is the one in my where clause). Not sure how I got those relationship GUIDs, I must have hunted for them manually at some point.
At any rate, the simplest answer is to query the MTV_Database view for the database size. Or you can modify some of the database state views to include the information, but I always like to know the T-SQL that is being done behind the scenes. It just makes me feel better knowing everything that is happening under the hood.
When you want to get more details about the instances, like version information, then you have to really dive into the relationships. The OpsMgr console can provide you with the information, but not always in the format you want, which means you may need to build your own T-SQL to get all the right details.
