The hardware inventor feature of ConfigMgr helps us discover and track several items. One recurring item is SQL Server and by default there is no nice way of tracking this. Basically the only option out of the box is using the application inventory to find where SQL server is installed. However this usualy results in a large list with mixed servers and clients and you still have no idea if its running SQLExpress, Standard or Enterprise edtion.
So what can we do to fix this besides buy a third party solution. Well the good thing is that SQL stores all the information into WMI, this means we can track editions, installations and versions with a bit of trickery with the hardware inventory engine.
There are number of steps included here at it is important to follow all of them for a successful result.
Step 1 – Update Configuration.mof
The first part of this is updateing configuration.mof. This file will control classes that inventoried and then index into the database.
The file can be a bit tricky to find if you have never done this before. You need to go to this location <Your ConfigMgr Install folder>\Inboxes\clifiles.src\hinv
In here is the configuration.mof file, now first make a backup copy of the file in case you want to revert the changes later and don’t want edit the file again.
At the very end of the file there is a section for adding custom extensions.
//========================
// Added extensions start //========================
// Added extensions end //======================== |
In between these block we need to add the following
//———————————————
// SQL 2017 Properties //———————————————
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement14”}, dynamic, Provider(“MS_VIEW_INSTANCE_PROVIDER”)]
[PropertySources{“IsReadOnly”} ] boolean IsReadOnly; [PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex; [PropertySources{“PropertyName”},key ] string PropertyName; [PropertySources{“PropertyNumValue”} ] uint32 PropertyNumValue; [PropertySources{“PropertyStrValue”} ] string PropertyStrValue; [PropertySources{“PropertyValueType”} ] uint32 PropertyValueType; [PropertySources{“ServiceName”},key ] string ServiceName; [PropertySources{“SqlServiceType”},key] uint32 SqlServiceType; };
// SQL 2016 Properties //———————————————
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement13”}, dynamic, Provider(“MS_VIEW_INSTANCE_PROVIDER”)]
[PropertySources{“IsReadOnly”} ] boolean IsReadOnly; [PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex; [PropertySources{“PropertyName”},key ] string PropertyName; [PropertySources{“PropertyNumValue”} ] uint32 PropertyNumValue; [PropertySources{“PropertyStrValue”} ] string PropertyStrValue; [PropertySources{“PropertyValueType”} ] uint32 PropertyValueType; [PropertySources{“ServiceName”},key ] string ServiceName; [PropertySources{“SqlServiceType”},key] uint32 SqlServiceType; };
// SQL 2014 Properties //———————————————
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement12”}, dynamic, Provider(“MS_VIEW_INSTANCE_PROVIDER”)]
[PropertySources{“IsReadOnly”} ] boolean IsReadOnly; [PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex; [PropertySources{“PropertyName”},key ] string PropertyName; [PropertySources{“PropertyNumValue”} ] uint32 PropertyNumValue; [PropertySources{“PropertyStrValue”} ] string PropertyStrValue; [PropertySources{“PropertyValueType”} ] uint32 PropertyValueType; [PropertySources{“ServiceName”},key ] string ServiceName; [PropertySources{“SqlServiceType”},key] uint32 SqlServiceType; };
// SQL 2012 Properties //———————————————
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement11”}, dynamic, Provider(“MS_VIEW_INSTANCE_PROVIDER”)]
[PropertySources{“IsReadOnly”} ] boolean IsReadOnly; [PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex; [PropertySources{“PropertyName”},key ] string PropertyName; [PropertySources{“PropertyNumValue”} ] uint32 PropertyNumValue; [PropertySources{“PropertyStrValue”} ] string PropertyStrValue; [PropertySources{“PropertyValueType”} ] uint32 PropertyValueType; [PropertySources{“ServiceName”},key ] string ServiceName; [PropertySources{“SqlServiceType”},key] uint32 SqlServiceType; };
// SQL 2008 Properties //———————————————
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement10”}, dynamic, Provider(“MS_VIEW_INSTANCE_PROVIDER”)]
[PropertySources{“IsReadOnly”} ] boolean IsReadOnly; [PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex; [PropertySources{“PropertyName”},key ] string PropertyName; [PropertySources{“PropertyNumValue”} ] uint32 PropertyNumValue; [PropertySources{“PropertyStrValue”} ] string PropertyStrValue; [PropertySources{“PropertyValueType”} ] uint32 PropertyValueType; [PropertySources{“ServiceName”},key ] string ServiceName; [PropertySources{“SqlServiceType”},key] uint32 SqlServiceType; };
// SQL 2000/2005 Properties //———————————————
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement”}, dynamic,Provider(“MS_VIEW_INSTANCE_PROVIDER”)]
[PropertySources{“IsReadOnly”} ] boolean IsReadOnly; [PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex; [PropertySources{“PropertyName”},key ] string PropertyName; [PropertySources{“PropertyNumValue”} ] uint32 PropertyNumValue; [PropertySources{“PropertyStrValue”} ] string PropertyStrValue; [PropertySources{“PropertyValueType”} ] uint32 PropertyValueType; [PropertySources{“ServiceName”},key ] string ServiceName; [PropertySources{“SqlServiceType”},key] uint32 SqlServiceType; }; |
Save the configuration.mof file
Done! On to step two.
Step 2 – Import hardware inventory information
The next step is done in the console and you need some nice permissions to do this. You will need to edit the default client settings object.
First thing is first. So we need to create a custom mof file to import. To do this, copy the following and save it as SQLProperties.mof (pick a nice location, like the desktop).
//=================SQL 2017 Information
SMS_Report(TRUE), SMS_Group_Name(“SQL17 Property”), SMS_Class_ID(“CUSTOM|SQL17_Property|1.0”)] class cm_sql17 : SMS_Class_Template
[SMS_Report(TRUE) ] boolean IsReadOnly; [SMS_Report(TRUE),key] uint32 PropertyIndex; [SMS_Report(TRUE),key] string PropertyName; [SMS_Report(TRUE) ] uint32 PropertyNumValue; [SMS_Report(TRUE) ] string PropertyStrValue; [SMS_Report(TRUE) ] uint32 PropertyValueType; [SMS_Report(TRUE),key] string ServiceName; [SMS_Report(TRUE),key] uint32 SqlServiceType; };
SMS_Report(TRUE), SMS_Group_Name(“SQL16 Property”), SMS_Class_ID(“CUSTOM|SQL16_Property|1.0”)] class cm_sql16 : SMS_Class_Template
[SMS_Report(TRUE) ] boolean IsReadOnly; [SMS_Report(TRUE),key] uint32 PropertyIndex; [SMS_Report(TRUE),key] string PropertyName; [SMS_Report(TRUE) ] uint32 PropertyNumValue; [SMS_Report(TRUE) ] string PropertyStrValue; [SMS_Report(TRUE) ] uint32 PropertyValueType; [SMS_Report(TRUE),key] string ServiceName; [SMS_Report(TRUE),key] uint32 SqlServiceType; };
SMS_Report(TRUE), SMS_Group_Name(“SQL14 Property”), SMS_Class_ID(“CUSTOM|SQL14_Property|1.0”)] class cm_sql14 : SMS_Class_Template
[SMS_Report(TRUE) ] boolean IsReadOnly; [SMS_Report(TRUE),key] uint32 PropertyIndex; [SMS_Report(TRUE),key] string PropertyName; [SMS_Report(TRUE) ] uint32 PropertyNumValue; [SMS_Report(TRUE) ] string PropertyStrValue; [SMS_Report(TRUE) ] uint32 PropertyValueType; [SMS_Report(TRUE),key] string ServiceName; [SMS_Report(TRUE),key] uint32 SqlServiceType; }; //=================SQL 2012 Information
SMS_Report(TRUE), SMS_Group_Name(“SQL12 Property”), SMS_Class_ID(“CUSTOM|SQL12_Property|1.0”)] class cm_sql12 : SMS_Class_Template
[SMS_Report(TRUE) ] boolean IsReadOnly; [SMS_Report(TRUE),key] uint32 PropertyIndex; [SMS_Report(TRUE),key] string PropertyName; [SMS_Report(TRUE) ] uint32 PropertyNumValue; [SMS_Report(TRUE) ] string PropertyStrValue; [SMS_Report(TRUE) ] uint32 PropertyValueType; [SMS_Report(TRUE),key] string ServiceName; [SMS_Report(TRUE),key] uint32 SqlServiceType; };
SMS_Report(TRUE), SMS_Group_Name(“SQL Property”), SMS_Class_ID(“CUSTOM|SQL_Property|2.0”)] class cm_sql08 : SMS_Class_Template
[SMS_Report(TRUE) ] boolean IsReadOnly; [SMS_Report(TRUE),key] uint32 PropertyIndex; [SMS_Report(TRUE),key] string PropertyName; [SMS_Report(TRUE) ] uint32 PropertyNumValue; [SMS_Report(TRUE) ] string PropertyStrValue; [SMS_Report(TRUE) ] uint32 PropertyValueType; [SMS_Report(TRUE),key] string ServiceName; [SMS_Report(TRUE),key] uint32 SqlServiceType; };
SMS_Report(TRUE), SMS_Group_Name(“SQL Property Legacy”), SMS_Class_ID(“CUSTOM|SQL_Property_Legacy|2.0”)]
[SMS_Report(TRUE) ] boolean IsReadOnly; [SMS_Report(TRUE),key] uint32 PropertyIndex; [SMS_Report(TRUE),key] string PropertyName; [SMS_Report(TRUE) ] uint32 PropertyNumValue; [SMS_Report(TRUE) ] string PropertyStrValue; [SMS_Report(TRUE) ] uint32 PropertyValueType; [SMS_Report(TRUE),key] string ServiceName; [SMS_Report(TRUE),key] uint32 SqlServiceType; }; |
Next, head on over to the Administration workspace in the ConfigMgr console and find the Client Settings in the menu to the left. Open up the Default Client Settings and check the Hardware Inventory tab.
There is a nice little button named Set Classes, hit that and then hit import. Browse to the SQLProperties.mof file and hit Open.
You will be greated by this screen where you need to make sure the Import both hardware inventory classes and hardware inventory class settings is selected
If you are like me and don’t want this inventory to hit each and every device you have deselect the following classes in your default settings. If you are only using the default client settings object, please skip to the next step.
If you are using custom client settings, please add the above classes to be included in hardware inventory under the same tabe as we did the import.
This means SQL information will now be imorted into the database! We can now create collections based on this, please do leave enough time for hardware inventory to run on each client before you find data in the database.
Step 3 – The Report
For me the last piece of the pussel is to create a custom report to easily show me the information needed. I will not cover the details of how to create a custom reports as the options are plenty. However I will share the query used to get a nice detailed view with the server and edition info.
Note that this is a simple report and can be extended in a lot of ways and if you are already using PowerBI you can extract the same data that way.
Query used:
— SQL 2017 select sys1.Netbios_name0 as [Computer Name] ,max(Case sql6.PropertyName0 when ‘VERSION’ then (case when sql6.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’ when sql6.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2′ when sql6.PropertySTRValue0 like ’10.%’ then ‘SQL 2008′ when sql6.PropertySTRValue0 like ’11.%’ then ‘SQL 2012′ when sql6.PropertySTRValue0 like ’12.%’ then ‘SQL 2014′ when sql6.PropertySTRValue0 like ’13.%’ then ‘SQL 2016′ when sql6.PropertySTRValue0 like ’14.%’ then ‘SQL 2017’ else ‘SQL Other’ End) end) as [SQL] ,max(Case sql6.PropertyName0 when ‘SKUName’ then sql6.PropertySTRValue0 end) as [SQL Type] ,max(Case sql6.PropertyName0 when ‘SPLEVEL’ then sql6.PropertyNUMValue0 end) as [SQL Service Pack] ,max(Case sql6.PropertyName0 when ‘VERSION’ then sql6.PropertySTRValue0 end) as [SQL Version] ,max(Case sql6.PropertyName0 when ‘FILEVERSION’ then sql6.PropertySTRValue0 end) as [SQL CU Version] from v_r_system sys1 left join v_gs_custom_SQL17_Property0 sql6 on sys1.ResourceID=sql6.ResourceID where sql6.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’) group by sys1.Netbios_name0 union — SQL 2016 — SQL 2014 — SQL 2012 — SQL 2008 — SQL 2005 |
This might not be the prettiest query every created or the smartest way (there are alot smarter people out there) so don’t go flaming it’s not optimized. It does the job : )
/Peter
Hi,
there is a typo in mof: both SQL17 and 16 has the same class cm_sql16
LikeLike
Hi Alex,
Thank you. I have now updated the post with a correction for that.
LikeLike
and SQL Builds in report are wrong: https://sqlserverbuilds.blogspot.ru/
LikeLike
another one note: you can not copy and paste contents above into the configuration.mof as is. Blog engine breaks the format so your editrd configuration.mof would not compile.
BEFORE you paste the lines ensure you removed line breaks from strings like this one:
[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,
ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement”}, dynamic,Provider(“MS_VIEW_INSTANCE_PROVIDER”)]
This must be a single string, no CR\LF
LikeLike
And another one typo (in the report query):
left join v_gs_custom_SQL14_Property0 sql3 on sys1.ResourceID=sql3.ResourceID
in the SQL 2012 block should be left join v_gs_custom_SQL12_Property0 sql3 on sys1.ResourceID=sql3.ResourceID
LikeLike
Hi,
Thank you for the script. I have tested it and seems to be working. In results i get the servers listed. In messages i get the following information:
“Warning: Null value is eliminated by an aggregate or other SET operation.”
Is that normal?
Could i be sure, that the script is running fine and all servers are listed in results?
BR
Julian
LikeLike
Sounds like you are getting an error when running the script. Try running the detection part manually on a machines that gives you the failure.
LikeLike
Doesn’t work for me, when I Update Configuration.mof it reverts back.
LikeLike
Sounds like an issue in your environment. Done this solution at a couple of diffrent customers and in several diffrent lab and demo environments and it always works in a fully functioning environment.
Without knowing anything make sure you actually have write permission to the configuration.mof file in that location or copy the file and copy it back in to the correct folder and accept the UAC prompt to do so.
LikeLike
It works perfectly, thanks a lot.
Any Update for SQL Server 2019?
LikeLike
Not at the moment, but doing so should be easy enough given that SQL follows the same principle. Updating the MOF files to include the required WMI class should be straight forward. I make no promises but will do my best to provide an update in the near future.
LikeLike
First, thank you for this solution. Its great and I really appreciate it. I have run into one issue with it. I noticed some of my SQL 2016 servers weren’t being inventoried. When I explored the WMI I found the SQLService class under ROOT\Microsoft\SqlServer\ComputerManagement13\MS_409
Do you know how this could be incorporated into this solution?
LikeLike
Hi, they mof files needs to be updated to include additional WMI classes, you can use the mof files provided as a template to include them.
LikeLike