Retrieving a List of Monitored SQL Databases and Database Files

Here is a tid-bit of housekeeping information. Our DBA wanted to know how many databases were floating around out there. I realized, there didn't seem to be a consolidated report that showed all SQL 2000, 2005 and 2008 instances. Most reports are segmented by SQL version and still don't really show all this information in a quick list. I concocted a couple of queries that pry that information out of the System Center Database. Try them out!

Open up SQL Management studio, connect to the SQL server running your System Center databases and give these queries a shot:

Use OperationsManager

Select *

From ManagedEntityGenericView

Where FullName Like '%SQLServer.Database%' AND

NOT FullName Like '%Exchange%' AND

MonitoringClassId = '10C1C7F7-BA0F-5F9B-C74A-79A891170934'

ORDER By Path

If nothing is returned, try removing the filter for the Monitoring ClassId

------------------------------------------------------------------------------------------

Use OperationsManager

Select *

From ManagedEntityGenericView

Where FullName Like '%DBFile:%' AND

NOT FullName Like '%Exchange%'

ORDER By Id