I often get questions for getting SQL info together, such as names, instances, versions, editions and so on for all kinds of purposes. Sometimes as inventory, sometimes to find instances no longer supported, rogue instances, needed for licensing info and so on.
The first thing to understand is that SCOM is not a CMDB. There are tools like SCCM and SCSM for those kind of things. However if a SCOM agent is installed and the SQL management packs are imported they will discover the SQL component and put some info in the discovered inventory for you.
So first thing I usually do for this and other reasons is to go in the monitoring pane all the way to the top in the left hand side menu and find Discovered Inventory. Next on the right hand Actions Menu go for Change Target Type. Next find the SQL DB Engine and select it. Now you should get a list of all SQL database engines and their versions and names and lots of other information. In the case of this management pack it is also possible to go to the Microsft SQL Server management pack folder to the left hand side and expand the server roles folder and select a state view, such as for database engine. It has the same information (could be you use the Personalize View actions item to add columns you are interested in). Keep in mind that the SQL DB Engine is not the only possible SQL component which can be installed. There is also Reporting Services for instance which is very common. The state views here are nice and fast to find your instances of those as well.
Now, lets pull this info into a CSV file using the Operations Manager Shell (these are two lines, enter as separate commands, and note these are SCOM 2012 commands):
$MyDevices = get-scomclass -Displayname “SQL DB Engine” | get-scomclassinstance
$MyDevices | select @{Label=”Computer”;Expression= {$_.'[Microsoft.Windows.Computer].PrincipalName’}}, @{Label=”Instance”;Expression= {$_.'[Microsoft.SQLServer.ServerRole].InstanceName’}}, @{Label=”ConnectionString”;Expression= {$_.'[Microsoft.SQLServer.DBEngine].ConnectionString’}}, @{Label=”Version”;Expression= {$_.'[Microsoft.SQLServer.DBEngine].Version’}}, @{Label=”Edition”;Expression= {$_.'[Microsoft.SQLServer.DBEngine].Edition’}} | Export-CSV -notype C:\sqlinstances.txt
And Voila you have a text file with the required info. What happened is that we are looking for a class called SQL DB Engine and we pull in all instances of that class. Next we select for each DB engine the ComputerName (you could have used Path as well there), Instance Name, Connection string, SQL version (as a number) and SQL edition (Standard/Enterprise/Express). Throw the CSV file into Excel and you will have the data in clear format.
This basically works the same way as in a post I did earlier about how to get devices (network device, windows agents, unix/linux agents) out of SCOM through PowerShell.
You can go deeper for instance by trying to find only instances of a certain version or edition and to sort the output. It is very versatile.
Enjoy!
Bob Cornelissen
