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)
- Version
- Patch Level
- Collation
- TCP Port (if not default)
- Edition
- 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.
[ms_button style=”normal” link=”https://github.com/jason4tw/ConfigMgrScripts/blob/master/Inventory/SQLInventory.vbs” size=”medium” shape=”rounded” shadow=”yes” block=”no” target=”_blank” gradient=”no” color=”#4f4f4f” text_color=”#ffffff” icon=”fa-github” icon_animation_type=”” border_width=”0″ class=”” id=””] SQL Instance Inventory: Get it on GitHub[/ms_button]
Hi Jason,
Thank you for the article. Does the vbscript need to be run regularly for information that is changing? For instance in tracking when SPs are installed?
Thanks in advance.
Yes, that is correct. Without the script, there’s no way for the updated data to be available to ConfigMgr to collect.
Does this gather db filenames?
No. That would require permissions within the SQL instance which the local System account doesn’t necessarily have.
The link to your session at System Center Universe 2014 is dead, and I can’t find the presentation by searching through the site. Can you post a new link?
This should get you there: https://channel9.msdn.com/Blogs/System-Center-Universe-America-2014/Custom-Data-Collection-and-Reporting-with-ConfigMgr. Note however I gave an updated session at System Center Universe Europe 2015 a couple of months back and at MMS this week (the SCUE video is not posted though as I think they had an issue transcoding it and MMS doesn’t record sessions).
Hi Jason,
I found that the clustername value your script returns, is always empty. Turns out the reg value doesn’t exist. I’ve changed line 58 of your script to clusterName = ReadRegValue(HKLM, “SYSTEMCurrentControlSetservicesClusSvcParameters”, “ClusterName”, “String”, context, locator, regProvider)
Thanks for the update — when I wrote the script I don’t think I had any clustered SQL Servers available.
Hi Jason,
Thank you for this – I have deployed the script to my clients and results are coming in. Unfortunately, my query/reporting skillz are not gangnam style, nor metal machine music, nor even the free credit report jingle, and thus my query does not return the name of the workstation on which a given instance is running. I see the ResourceID and can search my device list for each in order to find the names, but it would be more convenient to have this information automatically returned. Could you tell me how I would have to change the query to display the device name?
This is the query as I have it now.
select SMS_G_System_SQLINSTANCE.* from SMS_R_System inner join SMS_G_System_SQLINSTANCE on SMS_G_System_SQLINSTANCE.ResourceId = SMS_R_System.ResourceId order by SMS_G_System_SQLINSTANCE.* DESC
If I try to go to the design view I get the “syntax error or unsupported feature” error dialog, but the query does show some results, so something’s working.
Hi Michael,
Sorry for the delay on this. Here’s a modified query you can use (it’s as simple as adding the field that you want to the select clause):
select SMS_R_System.Name, SMS_G_System_SQLINSTANCE.*
from SMS_R_System
inner join SMS_G_System_SQLINSTANCE
on SMS_G_System_SQLINSTANCE.ResourceId = SMS_R_System.ResourceId
order by SMS_G_System_SQLINSTANCE.* DESC
Thanks Jason. For anyone who finds this post this is a great framework for adding in all kinds of good SQL information. Pretty easy to add to the class. For instance, I added in a section to gather the resource DNS name for cluster, as the cluster name doesn’t give you a connection string. Here is what I added (I added it right below where the edition, patch level, and version variables were set):
resourceName = ReadRegValue(HKLM, “SOFTWAREMicrosoftMicrosoft SQL Server” & instanceName & “Cluster”, “ClusterName”, “String”, context, locator, regProvider)
If it helps anyone else, here is a good base report (update v_GS_SQLINVENTORY with your class name):
SELECT DISTINCT
CASE
WHEN sqli.ResourceName0 is not null and sqli.InstanceName0 like ‘%MSSQLSERVER’ THEN ResourceName0
WHEN sqli.ResourceName0 is not null and sqli.InstanceName0 not like ‘%MSSQLSERVER’THEN ResourceName0 + ” + sqli.InstanceName0
WHEN sqli.ResourceName0 is null and sqli.InstanceName0 like ‘%MSSQLSERVER’ THEN vcs.name0
ELSE vcs.name0 + ” + substring(sqli.InstanceName0,charindex(‘.’,sqli.InstanceName0)+1,len(sqli.InstanceName0))
END as [Instance],
CASE
WHEN Version0 = ‘8.0.194’ THEN ‘SQL 2000’
WHEN Version0 = ‘8.0.384’ THEN ‘SQL 2000 SP1’
WHEN Version0 = ‘8.0.532’ THEN ‘SQL 2000 SP2’
WHEN Version0 = ‘8.0.760’ THEN ‘SQL 2000 SP3’
WHEN Version0 = ‘8.0.2039’ THEN ‘SQL 2000 SP4’
WHEN Version0 = ‘9.0.1399.06’ THEN ‘SQL 2005’
WHEN Version0 = ‘9.0.2047’ THEN ‘SQL 2005 SP1’
WHEN Version0 = ‘9.0.3042’ THEN ‘SQL 2005 SP2’
WHEN Version0 = ‘9.0.4035’ THEN ‘SQL 2005 SP3’
WHEN Version0 = ‘9.0.5000’ THEN ‘SQL 2005 SP4’
WHEN Version0 = ‘10.0.1600.22’ THEN ‘SQL 2008’
WHEN Version0 = ‘10.0.2531.0’ or Version0 = ‘10.1.2531.0’ THEN ‘SQL 2008 SP1’
WHEN Version0 = ‘10.0.4000.0’ or Version0 = ‘10.2.4000.0’ THEN ‘SQL 2008 SP2’
WHEN Version0 = ‘10.0.5500.0’ or Version0 = ‘10.3.5500.0’ THEN ‘SQL 2008 SP3’
WHEN Version0 = ‘10.0.6000.29’ or Version0 = ‘10.4.6000.29’ THEN ‘SQL 2008 SP3’
WHEN Version0 = ‘10.50.1600.1’ THEN ‘SQL 2008 R2’
WHEN Version0 = ‘10.50.2500.0’ or Version0 = ‘10.51.2500.0’ THEN ‘SQL 2008 R2 SP1’
WHEN Version0 = ‘10.50.4000.0’ or Version0 = ‘10.52.4000.0’ THEN ‘SQL 2008 R2 SP2’
WHEN Version0 = ‘10.50.6000.34’ or Version0 = ‘10.53.6000.34’ THEN ‘SQL 2008 R2 SP3’
WHEN Version0 = ‘11.0.2100.60’ THEN ‘SQL 2012’
WHEN Version0 = ‘11.0.3000.0’ or Version0 = ‘11.1.3000.0’ THEN ‘SQL 2012 SP1’
WHEN Version0 = ‘11.0.5058.0’ or Version0 = ‘11.2.5058.0’ THEN ‘SQL 2012 SP2’
WHEN Version0 = ‘11.0.6020.0’ or Version0 = ‘11.3.6020.0’ THEN ‘SQL 2012 SP3’
WHEN Version0 = ‘12.0.2000.8’ THEN ‘SQL 2014’
WHEN Version0 = ‘12.0.4100.1’ or Version0 = ‘12.1.4100.1’ THEN ‘SQL 2014 SP1’
ELSE Version0
END as [Version],
sqli.edition0 as [Edition]
FROM
v_GS_SQLINVENTORY sqli
JOIN v_GS_COMPUTER_SYSTEM vcs on vcs.ResourceID = sqli.ResourceID
WHERE
sqli.instancetype0 = ‘SQL’
ORDER BY
[Instance]
Does this script work on Windows Server 2003? When I run it, it’s bombing out on line 55. As far as I can tell, it’s because the “StdRegProv” class doesn’t exist in the namespace. According to the documentation I found on the MS Developer site ).aspx), the minimum supported OS for that class is Server 2008 and Vista.
It should work fine as the WMI Registry Provider has been around for a long time. The page says Windows Server 2008 as a minimum because Windows Server 2003 is no longer supported.
Hi there! Just came across this fabulous script and it works great through SCCM 2012 with Configuration Baseline.
However, we still have some Windows Server 2003 in our domains and i came across the same issue as Mike Carroll. But I managed to put it to work.
In line 55, change “cimv2” to “default”:
Set regProvider = locator.ConnectServer(“”, “root\default”, “”, “”,,,, context).Get(“StdRegProv”)
After running the script, the WMI filled up with the data we need and I have it all at my sql table.
Once again, thank you for this great script!
I forgot to mention: i had to do separate configuration items to achieve this: one for Windows 2003, other for 2008 and beyond!
I am running the script as a configuration baseline, but I’m getting -2147217405, which correlates to permission denied when the script is attempting to create the new WMI Class. If I manually run the script in an elevated command prompt, it works fine. I can’t seem to find a good way to run this VBScript with elevated permissions. Any suggestions? Thanks.
I’ve never tried as a baseline but it should work fine there also as long as you don’t run it as a user item. I can’t say I’ve ever tried it that way though.
Have the same problem, did you solved it?
Hi,
The script works wonderfully in sccm 2012 r2. Many thanks.
Can it be extended to list all databases also, using a for each statement in the script?
Thanks.
Getting an inventory of actual databases in a SQL instance would require having permissions in that SQL instance which cannot be assumed and is certainly not default security. Thus, no, this cannot truly be done.
This script is great, Thank you.
You are quite welcome — I’m glad that you like it.
Jason,
Great script… Could this additional information be grabbed? Post on TechNet lead me here:
https://social.technet.microsoft.com/Forums/en-US/911f8637-05d6-426c-8349-c6561d708a56/sql-inventory-in-sccm?forum=configmanagergeneral#911f8637-05d6-426c-8349-c6561d708a56
All of the SQL specific items from that post are already grabbed by the script except database names; grabbing database names requires querying the SQL instance itself and also actual permissions in the SQL instance which I doubt the ConfigMgr agent will ever have (and rightly so).
Most of the other items are collected by default in ConfigMgr. VMWare “stuff” is external to the local client though so there’s no way to grab that from the client itself. You’ll have to inject that in some other fashion — like a custom DDR.
Jason,
I have been asked what permissions exactly would be need to just retrieve DB name for SCCM to be able to report on this… What are your thoughts minus the security risk you have already mentioned?
That’s specific to your SQL instances. Your DBAs will know the answer.
So if you run this as a package does it create the WMI Namespace/Class and then continues to update as Instances are added or is 1 time data grab? If so should we run this as a scheduled task on the server say once a week?
No, there’s nothing to update them, that’s the purpose of the script. Thus, yes, if you want updated results, you need to re-run the script. Note that today the script doesn’t delete old data though so there is a slight problem with doing this if SQL instances are removed.
To protect against that should I have it delete the namespace and let script recreate each time to keep it fresh and up to date?
I wouldn’t delete the namespace, just the class or better yet, just the instances. The script can be modified to do this, I just haven’t recently been back to a customer with lots of SQL Servers where this is required.
Hmmm… Well anyone else good with VB I do batch and Powershell to help me accomplish this? Great script Jason.
How hard would it be to add # of CPUs, # of Core per CPU, and Logical CPUs?
The data is there, just not sure how to incorporate this info into you report.
SELECT
DISTINCT(CPU.SystemName0) AS [System Name],
CPU.Manufacturer0 AS Manufacturer,
CPU.Name0 AS Name,
COUNT(CPU.ResourceID) AS [Number of CPUs],
CPU.NumberOfCores0 AS [Number of Cores per CPU],
CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
FROM [dbo].[v_GS_PROCESSOR] CPU
GROUP BY
CPU.SystemName0,
CPU.Manufacturer0,
CPU.Name0,
CPU.NumberOfCores0,
CPU.NumberOfLogicalProcessors0
And Platform, Virtual or Physical.
Just join on ResourceID as with all hardware inventory data and it’ll work fine.
Hi,
Very instersting solution.
However by using the script as discovery script (in a configuration item) it does not create the custome wmi class. The only way is by executing the vb script manually from a powershell prompt “run as admin”. Is there a trick I missed here?
Same answer as above: I can’t say I’ve tried or why it wouldn’t work as it is running under the exact same context. Honestly, I don’t like running my inventory scripts this way though. Is there a reason that you are averse to running it from a package and program?
This is fantastic! Thank you so much for publishing this. How do you address situations when you remove SQL? For instance, say you have a server that has SSAS and the DB Engine installed and you remove SSAS. Normally they say you should not delete anything from the DB, so how would you go about periodically addressing that situation? Thanks again.
Sorry, late reply here. The script currently doesn’t delete existing hardware (an oversight on my part) but could easily be modified to do this. If interested, let me know and I’ll modify it.
I like the thought of this as it’s customizable and stores everything in one SCCM DB Table, but not too fond of vbscript as server 2003 isn’t even supported by current versions of SCCM. It likely was at the time of this article being written. Other thing is that it will create the WMI namespace/class even if SQL isn’t found on the machine, so either you end it making the namespace on everything or you create a collection with only servers that have SQL. If you add to the script to check for the WMI class that SQL creates then create the namespace/class this will allow you to send this to all computers and it will do nothing if SQL isn’t found. Porting to powershell wouldn’t be too bad, would need to use powershell v1 to support the earliest versions of server 2008. If you are looking for a bunch of custom information this may be worth it to you, but not for the data I need(instances, versions, editions). We are just going with https://www.mnscug.org/blogs/sherry-kissinger/328-installed-sql05-sql08-sql12-sql14-version-information-via-configmgr-hardware-inventory . This uses the SQL WMI classes that are already created. Downside with this is that for each version of SQL it will generate a different table in the SCCM DB. Not ideal, but more supportable from Microsoft, no client changes, and less effort.
> If you add to the script to check for the WMI class that SQL creates then create the namespace/class this will allow you to send this to all computers and it will do nothing if SQL isn’t found.
True, but a namespace and class in WMI are trivial. Worrying about them has no value as they have zero impact.
As for VBScript vs. PowerShell, same reply. If it works, is supported, and meets your requirements, why does it matter at all except to quibble about something that has zero impact. Also note though that it isn’t just about Windows 2003 as Windows Server 2008 does not have PowerShell built in either (it is an add-on feature).
> We are just going with “Sherry’s solution”
Awesome. Sherry writes great and useful stuff. As noted, you should use what works, what meets your requirements, and what is supported. However, it seems a bit (very really) petty to write a comment on a blog post just to say this.
> but more supportable from Microsoft
I have no idea what you are referring to here. Both solutions use 100% supported methods. If instead you meant to say that you can’t write your own scripts so instead like to judge others work on arbitrary criteria, then yes, that’s correct.