Here’s a script to gather complete SQL Server inventory information using ConfigMgr. I’ve tested and used it to gather information on SQL Server 2005, 2008, 2008 R2, and 2012. I haven’t tested it on 2014 explicitly, but it should work fine.
Note that Sherry already has a series of mof edits posted at Installed SQL05, SQL08, SQL12, SQL14 version information via ConfigMgr Hardware Inventory. This SQL Server inventory script is more comprehensive though. Additionally it unifies all of the information into a single WMI class so that when ConfigMgr picks it up (using hardware inventory of course) it ends up in a single database view.
Here’s what this SQL Server inventory script gathers:
- Instance name
- Instance type (SQL, SSRS, or AS)
- Cluster name (if the instance is hosted on a failover cluster)
- Patch Level
- TCP Port (if not default)
- Architecture (x64 or x86)
This SQL Server inventory script places all of this into a custom WMI namespace (because you should never use the built-in namespaces IMO) named ITLocal and a custom WMI class named SQLInstance. You can rename either or both of these by directly editing lines 5 and/or 6 of the script.
This SQL Server inventory script should be run on all of your clients using either a package via software distribution or as a discovery script in a compliance item (whichever you prefer). If there is no SQL Server instance, then no data will be gathered. Once at least one client (your test/pilot client) has run the script, just add the class to your hardware inventory in the appropriate client settings package. My session at System Center Universe 2014 covered how to do all of this and it’s similar to the steps I outlined in my Collecting USMT Estimates using ConfigMgr post. Alternatively, you can search the web to quickly find info on the exact steps involved. Reporting is an exercise I’ll leave up to you though.
Yes this this SQL Server inventory script is a VBScript — until Windows server 2003 is never heard from again, VBScript will still have a place.