Dark mode

Dark mode

There are 0 results matching

article card image dark article card image light

Published by · Jan 23, 2024 tools · 3 mins read

Introducing: Intune Device Renaming Tool

Rename Intune Devices by setting a Prefix or using a User Attribute as Prefix. Supports Windows, macOS, and Linux ...

See More
article card image dark article card image light

Published by · Dec 8, 2023 intune · 5 mins read

Intune Logs: A Deep Dive into Locations, Interpretation, and Configuration

A Comprehensive Guide to Locations, Interpretation, and Configuration of Intune Logs ...

See More
article card image dark article card image light

Published by · Aug 14, 2023 configmgr · 2 mins read

Configuration Manager Console Extension to show Device Collection Membership with Console Builder

Use the Configuration Manager Console Builder, to add Collection Membership View to the Device Node ...

See More
article card image dark article card image light

Published by · Aug 3, 2023 tools · 3 mins read

Introducing: Configuration Manager SSRS Dashboards

A Configuration Manager Dashboards solution with Reports for Software Updates, Bitlocker and more ...

See More
article card image dark article card image light

Published by · Aug 3, 2023 tools · 2 mins read

Introducing: PowerShell WMI Management Toolkit Module

Streamline your WMI Namespace, Class, and Instance Management with our PowerShell Module ...

See More
article card image dark article card image light

Published by · Jul 14, 2023 configmgr · 1 mins read

Configuration Manager detailed, filterable Port Documentation

Configuration Manager detailed, filterable port documentation as an excel document ...

See More
article card image dark article card image light

Published by · Jul 14, 2023 configmgr · 3 mins read

Configuration Manager PXE TFTP Window Size Bug

Configuration Manager TFTP Block Size and TFTP Window Size Correct Configuration ...

See More
article card image dark article card image light

Published by · Jun 18, 2023 tools · 4 mins read

Introducing: Configuration Manager Client Cache Cleanup Tool

Cleaning the Configuration Manager Client Cache the Right Way with PowerShell and Configuration Baselines ...

See More
article card image dark article card image light

Published by · Jun 18, 2023 tools · 2 mins read

Introducing: Windows Cache Cleanup Tool

Cleaning Windows and Configuration Manager Caches for Configuration Manager Build and Capture Task Sequence or Standalone Use ...

See More
article card image dark article card image light

Published by · Jun 17, 2023 tools · 1 mins read

Introducing: Windows Update Database Reinitialization Tool

Proactively repair corrupted Windows Update Database with Powershell and Configuration Manager ...

See More
article card image dark article card image light

Published by · Mar 31, 2023 tools · 3 mins read

Introducing: Configuration Manager SQL Products Reporting

A Complete SQL Products reporting solution using Configuration Manager ...

See More
article card image dark article card image light

Published by · Jan 28, 2023 configmgr · 1 mins read

Application Detection Method using the Configuration Manager Application Version

Replace hardcoded application version in scripts, with the Configuration Manager Application Version ...

See More
article card image dark article card image light

Published by · Jan 28, 2023 tools · 3 mins read

Introducing: Certificate Management Toolkit

Managing Certificates with Configuration Manager and PowerShell by using just the Public Key ...

See More
article card image dark article card image light

Published by · Jan 7, 2019 reports · 2 mins read

Configuration Manager Device Boundary and Network Information Report

List Device Boundaries and Network Information with Configuration Manager ...

See More
article card image dark article card image light

Published by · Sep 9, 1980 help · 5 mins read

MEM.Zone Blog Publishing Documentation

Publishing Documentation for MEM.Zone ...

See More

We couldn’t find anything related to

“SCCM”

BLOG / tools zone

Introducing: Configuration Manager SQL Products Reporting

Published by Popovici Ioan · Mar 31, 2023 · 3 mins read
article card image dark article card image light

Quick Summary

The second iteration of our SQL version report has been completely rewritten and optimized, to take up less space in your database and offer more information.

It lists the SQL Server Products with versions, including SQL Express.

A custom ConfigMgr hardware extension is used to gather the data together with an SQL Stored Procedure that pivots it to a more parsable format.

Recommendations

  • Do not modify or remove the previous version until you verify that this version has all the data you need.
  • Use a test environment for validation.
  • Back up your configuration.mof file before any changes.
  • Check the configuration.mof modifications using mofcomp.exe on a test machine first.
Notes

This version is compatible with the previous one, they can live side by side.


Prerequisites


HWI Extension

The extension needs to be added to the <ConfigMgrInstall>\Inboxes\clifiles.src\hinv\configuration.mof file on the Primary Site Server or Central Administration Site.

  • Look for the section below at the end of the configuration.mof file.
//========================
// Added extensions start
//========================
//========================
// Added extensions end
//========================
  • Added the extension between the Added extensions headers.
  • Uncomment the Old SQL extension cleanup section to remove the old extension classes from the client repository if needed.
  • Save and compile the configuration.mof file using mofcomp.exe.
mofcomp.exe <CMInstallLocation>\Inboxes\clifiles.src\hinv\Configuration.mof
Notes

Saving and compiling the configuration.mof file in the <ConfigMgrInstall>\Inboxes\clifiles.src\hinv\ folder on the PSS/CAS, will trigger the distribution and compilation on all machines in your environment on the next policy evaluation.

You NEED to check the Validation section first!

  • Wait for the clients to receive the new policy and run a hardware inventory cycle or trigger a hardware inventory cycle manually.
Notes

  • Always use a test environment first.
  • Always back up your configuration.mof file.
  • Never modify outside the Added extensions headers.
  • Try to have consistent extension formatting.
  • Use clear delimitations between extensions.

article card image pwsh-mofcomp-compile.png
Implement HWI Extension in production

HWI Definitions

The new classes need to be added to the Default Client Settings.

  • Import definitions.
article card image configmgr-hwi-import-mof-file.png
Click on Import...
article card image configmgr-hwi-import-summar.png
Review the classes and click on Import
article card image configmgr-hwi-select-clases.png
Select the new extension classes and click Ok
Notes

DO NOT DELETE the old extension definitions if you still want to use the old report!


Validation

HWI Extension Validation

  • Check if configuration.mof was correctly modified.
mofcomp.exe -check <TestDirectory>\Configuration.mof
  • Implement the changes.
mofcomp.exe <TestDirectory>\Configuration.mof
article card image pwsh-mofcomp-validate.png
Validation and implementation
  • Use PowerShell to check if the classes have been created in WMI.
##  At least one SQL version must be installed for these classes to be created

# Get SQL 2019 class
Get-CimClass -ClassName SQL_2019_Property

# Get SQL 2017 class
Get-CimClass -ClassName SQL_2017_Property

# Get SQL 2014 class
Get-CimClass -ClassName SQL_2014_Property

# Get SQL 2012 class
Get-CimClass -ClassName SQL_2012_Property

# Get SQL 2008 class
Get-CimClass -ClassName SQL_2008_Property

# Get SQL Legacy class
Get-CimClass -ClassName SQL_Legacy_Property

# Get SQL ProductID class
Get-CimClass -ClassName SQL_ProductID
article card image pwsh-mofcomp-compile.png
Compile the configuration.mof extension file

Database

  • Use SSMS to check if the views are created in the CMDB.
article card image sql-database-views.png
Database SQL Views

Import Report


SQL Procedure

A stored procedure is used to pivot the data from the SQL Views and must be created in the ConfigMgr database.

  • Paste the code below in SSMS
  1/*
  2.SYNOPSIS
  3    Pivots with dynamic columns.
  4.DESCRIPTION
  5    Pivots with dynamic columns using dynamic SQL to get the pivot columns.
  6.PARAMETER TableName
  7    Specifies the source pivot table name.
  8.PARAMETER NonPivotedColumn
  9    Specifies the non pivoded column name.
 10.PARAMETER DynamicColumn
 11    Specifies the column form which to dinamically get the pivot column list.
 12.PARAMETER AggregationColumn
 13    Specifies the aggregation column.
 14.PARAMETER StaticColumnList
 15    Specifies the static column list for the pivot.
 16    Note that specifying this parameter makes the pivot 'static'.
 17    Must be NULL for the dynamic pivot to work.
 18.EXAMPLE
 19    EXECUTE usp_PivotWithDynamicColumns
 20        @TableName         = N'SomeTableName'
 21        @NonPivotedColumn  = N'ResourceID',
 22        @DynamicColumn     = N'PropertyName0',
 23        @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
 24        @StaticColumnList  = NULL
 25.EXAMPLE
 26    EXECUTE usp_PivotWithDynamicColumns
 27        @TableName         = N'SomeTableName'
 28        @NonPivotedColumn  = N'ResourceID',
 29        @DynamicColumn     = N'PropertyName0',
 30        @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
 31        @StaticColumnList  = N'[Column1],[Column2],[Column3]'
 32.NOTES
 33    Created by Ioan Popovici.
 34    Credit to CSifiso W. Ndlovu.
 35    Replace the <SITE_CODE> with your CM Site Code.
 36    Run the code in SQL Server Management Studio.
 37.LINK
 38    https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/ (Sifiso W. Ndlovu)
 39.LINK
 40    https://MEM.Zone
 41.LINK
 42    https://MEM.Zone/ISSUES
 43*/
 44
 45/*##=============================================*/
 46/*## QUERY BODY                                  */
 47/*##=============================================*/
 48/* #region FunctionQueryBody */
 49
 50USE [CM_<SITE_CODE>]
 51GO
 52
 53SET NOCOUNT ON
 54GO
 55
 56IF OBJECT_ID('dbo.usp_PivotWithDynamicColumns', 'P') IS NOT NULL
 57    DROP PROCEDURE dbo.usp_PivotWithDynamicColumns;
 58GO
 59
 60CREATE PROCEDURE dbo.usp_PivotWithDynamicColumns (
 61    @TableName           AS NVARCHAR(MAX)
 62    , @NonPivotedColumn  AS NVARCHAR(MAX)
 63    , @DynamicColumn     AS NVARCHAR(MAX)
 64    , @AggregationColumn AS NVARCHAR(MAX)
 65    , @StaticColumnList  AS NVARCHAR(MAX)
 66)
 67AS
 68    BEGIN
 69
 70        /* Variable declaration */
 71        DECLARE @DynamicColumnQuery AS NVARCHAR(MAX);
 72        DECLARE @DynamicPivotQuery  AS NVARCHAR(MAX);
 73        DECLARE @ColumnList         AS NVARCHAR(MAX);
 74
 75        IF @StaticColumnList IS NOT NULL
 76            SET @ColumnList = @StaticColumnList
 77        ELSE
 78            BEGIN
 79                /* Assemble pivot columns query */
 80                SET @DynamicColumnQuery = ('
 81                    SET @ColumnList = (
 82                        STUFF(
 83                            (
 84                                SELECT DISTINCT
 85                                    '','' + QUOTENAME(DB.'+@DynamicColumn+')
 86                                FROM '+@TableName+' AS DB
 87                                FOR XML PATH(''''), TYPE
 88                            ).value(''.'', ''NVARCHAR(MAX)'')
 89                            , 1, 1, ''''
 90                        )
 91                    )
 92                ')
 93
 94                /* Get pivot columns dynamically and output to @ColumnList variable */
 95                EXECUTE dbo.sp_executesql @DynamicColumnQuery
 96                    , N'@TableName NVARCHAR(MAX), @DynamicColumn NVARCHAR(MAX), @ColumnList NVARCHAR(MAX) OUTPUT'
 97                    , @TableName
 98                    , @DynamicColumn
 99                    , @ColumnList OUTPUT
100                PRINT @ColumnList
101            END
102
103        /* Assemble pivot query */
104        SET @DynamicPivotQuery = ('
105            SELECT
106                '+@NonPivotedColumn+', '+@ColumnList+'
107            FROM (
108                SELECT
109                    '+@NonPivotedColumn+'
110                    , DynamicColumnAlias    = '+@DynamicColumn+'
111                    , AggregationAlias      = '+@AggregationColumn+'
112            FROM '+@TableName+'
113            )
114            SEARCH PIVOT (MAX(AggregationAlias) FOR DynamicColumnAlias IN ('+@ColumnList+'))p
115        ') --'p' is intentional, do not remove!
116
117        /* Perform pivot */
118        EXECUTE dbo.sp_executesql @DynamicPivotQuery
119    END;
120GO
121
122/* Grants execute rights for this stored procedure to SCCM reporting users */
123GRANT EXECUTE ON OBJECT::dbo.usp_PivotWithDynamicColumns
124    TO smsschm_users;
125
126/* #endregion */
127/*##=============================================*/
128/*## END QUERY BODY                              */
129/*##=============================================*/
  • Change the <SITE_CODE> to match your site code.
  • Click Execute to add the stored procedure to your database.
Notes

  • You might need additional database access to install the stored procedure.
  • Allow some time for the policy to be downloaded or force a policy refresh.
  • Allow some time for the data to be gathered or force a HWI collection.
  • This report was created with SQL 2017 Reporting Services, you might need to remove some report elements if you use an older version.


Preview

article card image report-software-sql-server-products.png
Report screenshot

Code

HWI Extension Code

For reference only, you can download the file in the Prerequisites section.

  1//=====================================================
  2//---------------| SQL extension start |---------------
  3//=====================================================
  4
  5/*
  6.SYNOPSIS
  7    Gathers SQL product info.
  8.DESCRIPTION
  9    Gathers SQL product info, id and product key and adds them to custom WMI Classes.
 10.NOTES
 11    Created by Ioan Popovici.
 12.LINK
 13    https://MEMZ.one/SW-SQL-Server-Products
 14.LINK
 15    https://MEMZ.one/SQL-SupportFunctions
 16.LINK
 17    https://MEMZ.one/SW-SQL-Server-Products-CHANGELOG
 18.LINK
 19    https://MEMZ.one/SW-SQL-Server-Products-GIT
 20.LINK
 21    https://MEM.Zone/ISSUES
 22*/
 23
 24//-- Set default WMI namespace
 25#Pragma NameSpace("\\\\.\\ROOT\\cimv2")
 26
 27//----------| Old SQL extension cleanup |---------
 28
 29//-- !! UNCOMMENT TO ACTIVATE !! (Remove '//' from '//#Pragma')
 30
 31//#Pragma DeleteClass("SQL_2022", NOFAIL)
 32//#Pragma DeleteClass("SQL_2019", NOFAIL)
 33//#Pragma DeleteClass("SQL_2017", NOFAIL)
 34//#Pragma DeleteClass("SQL_2016", NOFAIL)
 35//#Pragma DeleteClass("SQL_2014", NOFAIL)
 36//#Pragma DeleteClass("SQL_2012", NOFAIL)
 37//#Pragma DeleteClass("SQL_2008", NOFAIL)
 38//#Pragma DeleteClass("SQL_2000_And_2005", NOFAIL)
 39
 40
 41//---------------| SQL 2022 class |---------------
 42
 43//-- Create custom WMI class
 44//-  Class qualifiers
 45[
 46    Description("The SQL_2022_Property class contains information about the SQL 2022 service. You can use this information for SQL version reporting."): ToInstance,
 47    UNION,
 48    ViewSources {
 49        "SELECT PropertyIndex, PropertyName, PropertyNumValue, PropertyStrValue, ServiceName, SqlServiceType FROM SqlServiceAdvancedProperty WHERE SQLServiceType=1"
 50    },
 51    ViewSpaces {
 52        "\\\\.\\ROOT\\Microsoft\\SqlServer\\ComputerManagement16"
 53    },
 54    Dynamic,
 55    Provider("MS_VIEW_INSTANCE_PROVIDER")
 56]
 57//-  Class properties
 58Class SQL_2022_Property
 59{
 60    [PropertySources{"PropertyIndex"},  key]    uint32 PropertyIndex;
 61    [PropertySources{"PropertyName"},   key]    string PropertyName;
 62    [PropertySources{"PropertyNumValue"}   ]    uint32 PropertyNumValue;
 63    [PropertySources{"PropertyStrValue"}   ]    string PropertyStrValue;
 64    [PropertySources{"ServiceName"},    key]    string ServiceName;
 65    [PropertySources{"SqlServiceType"}, key]    uint32 SqlServiceType;
 66};
 67
 68//---------------| SQL 2019 class |---------------
 69
 70//-- Create custom WMI class
 71//-  Class qualifiers
 72[
 73    Description("The SQL_2019_Property class contains information about the SQL 2019 service. You can use this information for SQL version reporting."): ToInstance,
 74    UNION,
 75    ViewSources {
 76        "SELECT PropertyIndex, PropertyName, PropertyNumValue, PropertyStrValue, ServiceName, SqlServiceType FROM SqlServiceAdvancedProperty WHERE SQLServiceType=1"
 77    },
 78    ViewSpaces {
 79        "\\\\.\\ROOT\\Microsoft\\SqlServer\\ComputerManagement15"
 80    },
 81    Dynamic,
 82    Provider("MS_VIEW_INSTANCE_PROVIDER")
 83]
 84//-  Class properties
 85Class SQL_2019_Property
 86{
 87    [PropertySources{"PropertyIndex"},  key]    uint32 PropertyIndex;
 88    [PropertySources{"PropertyName"},   key]    string PropertyName;
 89    [PropertySources{"PropertyNumValue"}   ]    uint32 PropertyNumValue;
 90    [PropertySources{"PropertyStrValue"}   ]    string PropertyStrValue;
 91    [PropertySources{"ServiceName"},    key]    string ServiceName;
 92    [PropertySources{"SqlServiceType"}, key]    uint32 SqlServiceType;
 93};
 94
 95//---------------| SQL 2017 class |---------------
 96
 97//-- Create custom WMI class
 98//-  Class qualifiers
 99[
100    Description("The SQL_2017_Property class contains information about the SQL 2017 service. You can use this information for SQL version reporting."): ToInstance,
101    UNION,
102    ViewSources {
103        "SELECT PropertyIndex, PropertyName, PropertyNumValue, PropertyStrValue, ServiceName, SqlServiceType FROM SqlServiceAdvancedProperty WHERE SQLServiceType=1"
104    },
105    ViewSpaces {
106        "\\\\.\\ROOT\\Microsoft\\SqlServer\\ComputerManagement14"
107    },
108    Dynamic,
109    Provider("MS_VIEW_INSTANCE_PROVIDER")
110]
111//-  Class properties
112Class SQL_2017_Property
113{
114    [PropertySources{"PropertyIndex"},  key]    uint32 PropertyIndex;
115    [PropertySources{"PropertyName"},   key]    string PropertyName;
116    [PropertySources{"PropertyNumValue"}   ]    uint32 PropertyNumValue;
117    [PropertySources{"PropertyStrValue"}   ]    string PropertyStrValue;
118    [PropertySources{"ServiceName"},    key]    string ServiceName;
119    [PropertySources{"SqlServiceType"}, key]    uint32 SqlServiceType;
120};
121
122//---------------| SQL 2016 class |---------------
123
124//-- Create custom WMI class
125//-  Class qualifiers
126[
127    Description("The SQL_2016_Property class contains information about the SQL 2016 service. You can use this information for SQL version reporting."): ToInstance,
128    UNION,
129    ViewSources {
130        "SELECT PropertyIndex, PropertyName, PropertyNumValue, PropertyStrValue, ServiceName, SqlServiceType FROM SqlServiceAdvancedProperty WHERE SQLServiceType=1"
131    },
132    ViewSpaces {
133        "\\\\.\\ROOT\\Microsoft\\SqlServer\\ComputerManagement13"
134    },
135    Dynamic,
136    Provider("MS_VIEW_INSTANCE_PROVIDER")
137]
138//-  Class properties
139Class SQL_2016_Property
140{
141    [PropertySources{"PropertyIndex"},  key]    uint32 PropertyIndex;
142    [PropertySources{"PropertyName"},   key]    string PropertyName;
143    [PropertySources{"PropertyNumValue"}   ]    uint32 PropertyNumValue;
144    [PropertySources{"PropertyStrValue"}   ]    string PropertyStrValue;
145    [PropertySources{"ServiceName"},    key]    string ServiceName;
146    [PropertySources{"SqlServiceType"}, key]    uint32 SqlServiceType;
147};
148
149//---------------| SQL 2014 class |---------------
150
151//-- Create custom WMI class
152//-  Class qualifiers
153[
154    Description("The SQL_2014_Property class contains information about the SQL 2014 service. You can use this information for SQL version reporting."): ToInstance,
155    UNION,
156    ViewSources {
157        "SELECT PropertyIndex, PropertyName, PropertyNumValue, PropertyStrValue, ServiceName, SqlServiceType FROM SqlServiceAdvancedProperty WHERE SQLServiceType=1"
158    },
159    ViewSpaces {
160        "\\\\.\\ROOT\\Microsoft\\SqlServer\\ComputerManagement12"
161    },
162    Dynamic,
163    Provider("MS_VIEW_INSTANCE_PROVIDER")
164]
165//-  Class properties
166Class SQL_2014_Property
167{
168    [PropertySources{"PropertyIndex"},  key]    uint32 PropertyIndex;
169    [PropertySources{"PropertyName"},   key]    string PropertyName;
170    [PropertySources{"PropertyNumValue"}   ]    uint32 PropertyNumValue;
171    [PropertySources{"PropertyStrValue"}   ]    string PropertyStrValue;
172    [PropertySources{"ServiceName"},    key]    string ServiceName;
173    [PropertySources{"SqlServiceType"}, key]    uint32 SqlServiceType;
174};
175
176//---------------| SQL 2012 class |---------------
177
178//-- Create custom WMI class
179//-  Class qualifiers
180[
181    Description("The SQL_2012_Property class contains information about the SQL 2012 service. You can use this information for SQL version reporting."): ToInstance,
182    UNION,
183    ViewSources {
184        "SELECT PropertyIndex, PropertyName, PropertyNumValue, PropertyStrValue, ServiceName, SqlServiceType FROM SqlServiceAdvancedProperty WHERE SQLServiceType=1"
185    },
186    ViewSpaces {
187        "\\\\.\\ROOT\\Microsoft\\SqlServer\\ComputerManagement11"
188    },
189    Dynamic,
190    Provider("MS_VIEW_INSTANCE_PROVIDER")
191]
192//-  Class properties
193Class SQL_2012_Property
194{
195    [PropertySources{"PropertyIndex"},  key]    uint32 PropertyIndex;
196    [PropertySources{"PropertyName"},   key]    string PropertyName;
197    [PropertySources{"PropertyNumValue"}   ]    uint32 PropertyNumValue;
198    [PropertySources{"PropertyStrValue"}   ]    string PropertyStrValue;
199    [PropertySources{"ServiceName"},    key]    string ServiceName;
200    [PropertySources{"SqlServiceType"}, key]    uint32 SqlServiceType;
201};
202
203//---------------| SQL 2008 class |---------------
204
205//-- Create custom WMI class
206//-  Class qualifiers
207[
208    Description("The SQL_2008_Property class contains information about the SQL 2008 service. You can use this information for SQL version reporting."): ToInstance,
209    UNION,
210    ViewSources {
211        "SELECT PropertyIndex, PropertyName, PropertyNumValue, PropertyStrValue, ServiceName, SqlServiceType FROM SqlServiceAdvancedProperty WHERE SQLServiceType=1"
212    },
213    ViewSpaces {
214        "\\\\.\\ROOT\\Microsoft\\SqlServer\\ComputerManagement10"
215    },
216    Dynamic,
217    Provider("MS_VIEW_INSTANCE_PROVIDER")
218]
219//-  Class properties
220Class SQL_2008_Property
221{
222    [PropertySources{"PropertyIndex"},  key]    uint32 PropertyIndex;
223    [PropertySources{"PropertyName"},   key]    string PropertyName;
224    [PropertySources{"PropertyNumValue"}   ]    uint32 PropertyNumValue;
225    [PropertySources{"PropertyStrValue"}   ]    string PropertyStrValue;
226    [PropertySources{"ServiceName"},    key]    string ServiceName;
227    [PropertySources{"SqlServiceType"}, key]    uint32 SqlServiceType;
228};
229
230//---------------| SQL 2000/2005 class |---------------
231
232//-- Create custom WMI class
233//-  Class qualifiers
234[
235    Description("The SQL_Legacy_Property class contains information about the SQL 2000 and 2005 service. You can use this information for SQL version reporting."): ToInstance,
236    UNION,
237    ViewSources {
238        "SELECT PropertyIndex, PropertyName, PropertyNumValue, PropertyStrValue, ServiceName, SqlServiceType FROM SqlServiceAdvancedProperty WHERE SQLServiceType=1"
239    },
240    ViewSpaces {
241        "\\\\.\\ROOT\\Microsoft\\SqlServer\\ComputerManagement"
242    },
243    Dynamic,
244    Provider("MS_VIEW_INSTANCE_PROVIDER")
245]
246//-  Class properties
247Class SQL_Legacy_Property
248{
249    [PropertySources{"PropertyIndex"},  key]    uint32 PropertyIndex;
250    [PropertySources{"PropertyName"},   key]    string PropertyName;
251    [PropertySources{"PropertyNumValue"}   ]    uint32 PropertyNumValue;
252    [PropertySources{"PropertyStrValue"}   ]    string PropertyStrValue;
253    [PropertySources{"ServiceName"},    key]    string ServiceName;
254    [PropertySources{"SqlServiceType"}, key]    uint32 SqlServiceType;
255};
256
257//---------------| SQL ProductID class |---------------
258#Pragma DeleteClass("SQL_ProductID", NOFAIL)
259
260//-- Create custom WMI class
261//-  Class qualifiers
262    [
263        Description("The SQL_ProductID class contains the obfuscated SQL license key. You can use this information for SQL reporting."): ToInstance
264    ]
265
266//-  Class properties
267Class SQL_ProductID
268{
269    [key]
270    string  Release;
271    string  ProductID;
272    boolean DigitalProductID[];
273};
274
275//- SQL 2022 class instance
276[DYNPROPS]
277Instance of SQL_ProductID
278{
279    Release = "2022";
280    [
281        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\160\\Tools\\ClientSetup|ProductID"),
282        Dynamic,
283        Provider("RegPropProv")
284    ]
285    ProductID;
286    [
287        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\160\\Tools\\ClientSetup|DigitalProductID"),
288        Dynamic,
289        Provider("RegPropProv")
290    ]
291    DigitalProductID;
292};
293
294//- SQL 2019 class instance
295[DYNPROPS]
296Instance of SQL_ProductID
297{
298    Release = "2019";
299    [
300        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\150\\Tools\\ClientSetup|ProductID"),
301        Dynamic,
302        Provider("RegPropProv")
303    ]
304    ProductID;
305    [
306        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\150\\Tools\\ClientSetup|DigitalProductID"),
307        Dynamic,
308        Provider("RegPropProv")
309    ]
310    DigitalProductID;
311};
312
313//- SQL 2017 class instance
314[DYNPROPS]
315Instance of SQL_ProductID
316{
317    Release = "2017";
318    [
319        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\140\\Tools\\ClientSetup|ProductID"),
320        Dynamic,
321        Provider("RegPropProv")
322    ]
323    ProductID;
324    [
325        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\140\\Tools\\ClientSetup|DigitalProductID"),
326        Dynamic,
327        Provider("RegPropProv")
328    ]
329    DigitalProductID;
330};
331
332//- SQL 2016 class instance
333[DYNPROPS]
334Instance of SQL_ProductID
335{
336    Release = "2016";
337    [
338        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\130\\Tools\\ClientSetup|ProductID"),
339        Dynamic,
340        Provider("RegPropProv")
341    ]
342    ProductID;
343    [
344        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\130\\Tools\\ClientSetup|DigitalProductID"),
345        Dynamic,
346        Provider("RegPropProv")
347    ]
348    DigitalProductID;
349};
350
351//- SQL 2014 class instance
352[DYNPROPS]
353Instance of SQL_ProductID
354{
355    Release = "2014";
356    [
357        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\120\\Tools\\Setup|ProductID"),
358        Dynamic,
359        Provider("RegPropProv")
360    ]
361    ProductID;
362    [
363        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\120\\Tools\\Setup|DigitalProductID"),
364        Dynamic,
365        Provider("RegPropProv")
366    ]
367    DigitalProductID;
368};
369
370//- SQL 2012 class instance
371[DYNPROPS]
372Instance of SQL_ProductID
373{
374    Release = "2012";
375    [
376        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\110\\Tools\\Setup|ProductID"),
377        Dynamic,
378        Provider("RegPropProv")
379    ]
380    ProductID;
381    [
382        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\110\\Tools\\Setup|DigitalProductID"),
383        Dynamic,
384        Provider("RegPropProv")
385    ]
386    DigitalProductID;
387};
388
389//- SQL 2008 class instance
390[DYNPROPS]
391Instance of SQL_ProductID
392{
393    Release = "2008";
394    [
395        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10\\Setup|ProductID"),
396        Dynamic,
397        Provider("RegPropProv")
398    ]
399    ProductID;
400    [
401        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10\\Setup|DigitalProductID"),
402        Dynamic,
403        Provider("RegPropProv")
404    ]
405    DigitalProductID;
406};
407
408//- SQL Legacy class instance
409[DYNPROPS]
410Instance of SQL_ProductID
411{
412    Release = "Legacy";
413    [
414        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\90\\ProductID|ProductID"),
415        Dynamic,
416        Provider("RegPropProv")
417    ]
418    ProductID;
419    [
420        PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\90\\ProductID|DigitalProductID"),
421        Dynamic,
422        Provider("RegPropProv")
423    ]
424    DigitalProductID;
425};
426
427//=====================================================
428//----------------| SQL extension end |----------------
429//=====================================================

HWI Definitions Code

For reference only, you can download the file in the Prerequisites section.

  1//======================================================
  2//---------------| SQL definition start |---------------
  3//======================================================
  4
  5/*
  6.SYNOPSIS
  7    Extends Configuration Manager HWI for SQL Products.
  8.DESCRIPTION
  9    Extends Configuration Manager HWI for SQL Products, by adding already created classes to Hardware Inventory.
 10.NOTES
 11    Created by Ioan Popovici.
 12    Requires the HWI EXT SQL Server Products.mof extension file.
 13.LINK
 14    https://MEMZ.one/SW-SQL-Server-Products
 15.LINK
 16    https://MEMZ.one/SQL-SupportFunctions
 17.LINK
 18    https://MEMZ.one/SW-SQL-Server-Products-CHANGELOG
 19.LINK
 20    https://MEMZ.one/SW-SQL-Server-Products-GIT
 21.LINK
 22    https://MEM.Zone/ISSUES
 23*/
 24
 25
 26//---------------| SQL 2022 definitions |---------------
 27
 28[Dynamic, Provider("MS_VIEW_INSTANCE_PROVIDER"),
 29SMS_Report(true),
 30SMS_Group_Name("SQL 2022 Property"),
 31SMS_Class_ID("EXT|SQL_2022_PROPERTY|1.0")]
 32
 33Class SQL_2022_Property: SMS_Class_Template
 34{
 35    [SMS_Report(true), key]    uint32 PropertyIndex;
 36    [SMS_Report(true), key]    string PropertyName;
 37    [SMS_Report(true)     ]    uint32 PropertyNumValue;
 38    [SMS_Report(true)     ]    string PropertyStrValue;
 39    [SMS_Report(true), key]    string ServiceName;
 40    [SMS_Report(true), key]    uint32 SqlServiceType;
 41};
 42
 43//---------------| SQL 2019 definitions |---------------
 44
 45[Dynamic, Provider("MS_VIEW_INSTANCE_PROVIDER"),
 46SMS_Report(true),
 47SMS_Group_Name("SQL 2019 Property"),
 48SMS_Class_ID("EXT|SQL_2019_PROPERTY|1.0")]
 49
 50Class SQL_2019_Property: SMS_Class_Template
 51{
 52    [SMS_Report(true), key]    uint32 PropertyIndex;
 53    [SMS_Report(true), key]    string PropertyName;
 54    [SMS_Report(true)     ]    uint32 PropertyNumValue;
 55    [SMS_Report(true)     ]    string PropertyStrValue;
 56    [SMS_Report(true), key]    string ServiceName;
 57    [SMS_Report(true), key]    uint32 SqlServiceType;
 58};
 59
 60//---------------| SQL 2017 definitions |---------------
 61
 62[Dynamic, Provider("MS_VIEW_INSTANCE_PROVIDER"),
 63SMS_Report(true),
 64SMS_Group_Name("SQL 2017 Property"),
 65SMS_Class_ID("EXT|SQL_2017_PROPERTY|1.0")]
 66
 67Class SQL_2017_Property: SMS_Class_Template
 68{
 69    [SMS_Report(true), key]    uint32 PropertyIndex;
 70    [SMS_Report(true), key]    string PropertyName;
 71    [SMS_Report(true)     ]    uint32 PropertyNumValue;
 72    [SMS_Report(true)     ]    string PropertyStrValue;
 73    [SMS_Report(true), key]    string ServiceName;
 74    [SMS_Report(true), key]    uint32 SqlServiceType;
 75};
 76
 77//---------------| SQL 2016 definitions |---------------
 78
 79[Dynamic, Provider("MS_VIEW_INSTANCE_PROVIDER"),
 80SMS_Report(true),
 81SMS_Group_Name("SQL 2016 Property"),
 82SMS_Class_ID("EXT|SQL_2016_PROPERTY|1.0")]
 83
 84Class SQL_2016_Property: SMS_Class_Template
 85{
 86    [SMS_Report(true), key]    uint32 PropertyIndex;
 87    [SMS_Report(true), key]    string PropertyName;
 88    [SMS_Report(true)     ]    uint32 PropertyNumValue;
 89    [SMS_Report(true)     ]    string PropertyStrValue;
 90    [SMS_Report(true), key]    string ServiceName;
 91    [SMS_Report(true), key]    uint32 SqlServiceType;
 92};
 93
 94//---------------| SQL 2014 definitions |---------------
 95
 96[Dynamic, Provider("MS_VIEW_INSTANCE_PROVIDER"),
 97SMS_Report(true),
 98SMS_Group_Name("SQL 2014 Property"),
 99SMS_Class_ID("EXT|SQL_2014_PROPERTY|1.0")]
100
101Class SQL_2014_Property: SMS_Class_Template
102{
103    [SMS_Report(true), key]    uint32 PropertyIndex;
104    [SMS_Report(true), key]    string PropertyName;
105    [SMS_Report(true)     ]    uint32 PropertyNumValue;
106    [SMS_Report(true)     ]    string PropertyStrValue;
107    [SMS_Report(true), key]    string ServiceName;
108    [SMS_Report(true), key]    uint32 SqlServiceType;
109};
110
111//---------------| SQL 2012 definitions |---------------
112
113[Dynamic, Provider("MS_VIEW_INSTANCE_PROVIDER"),
114SMS_Report(true),
115SMS_Group_Name("SQL 2012 Property"),
116SMS_Class_ID("EXT|SQL_2012_PROPERTY|1.0")]
117
118Class SQL_2012_Property: SMS_Class_Template
119{
120    [SMS_Report(true), key]    uint32 PropertyIndex;
121    [SMS_Report(true), key]    string PropertyName;
122    [SMS_Report(true)     ]    uint32 PropertyNumValue;
123    [SMS_Report(true)     ]    string PropertyStrValue;
124    [SMS_Report(true), key]    string ServiceName;
125    [SMS_Report(true), key]    uint32 SqlServiceType;
126};
127
128//---------------| SQL 2008 definitions |---------------
129
130[Dynamic, Provider("MS_VIEW_INSTANCE_PROVIDER"),
131SMS_Report(true),
132SMS_Group_Name("SQL 2008 Property"),
133SMS_Class_ID("EXT|SQL_2008_PROPERTY|1.0")]
134
135Class SQL_2008_Property: SMS_Class_Template
136{
137    [SMS_Report(true), key]    uint32 PropertyIndex;
138    [SMS_Report(true), key]    string PropertyName;
139    [SMS_Report(true)     ]    uint32 PropertyNumValue;
140    [SMS_Report(true)     ]    string PropertyStrValue;
141    [SMS_Report(true), key]    string ServiceName;
142    [SMS_Report(true), key]    uint32 SqlServiceType;
143};
144
145//---------------| SQL 2000/2005 definitions |---------------
146
147[Dynamic, Provider("MS_VIEW_INSTANCE_PROVIDER"),
148SMS_Report(true),
149SMS_Group_Name("SQL Legacy Property"),
150SMS_Class_ID("EXT|SQL_LEGACY_PROPERTY|1.0")]
151
152Class SQL_Legacy_Property: SMS_Class_Template
153{
154    [SMS_Report(true), key]    uint32 PropertyIndex;
155    [SMS_Report(true), key]    string PropertyName;
156    [SMS_Report(true)     ]    uint32 PropertyNumValue;
157    [SMS_Report(true)     ]    string PropertyStrValue;
158    [SMS_Report(true), key]    string ServiceName;
159    [SMS_Report(true), key]    uint32 SqlServiceType;
160};
161
162//-------------| SQL ProductID definition |-------------
163
164[Dynamic, Provider("MS_VIEW_INSTANCE_PROVIDER"),
165SMS_Report(true),
166SMS_Group_Name("SQL ProductID"),
167SMS_Class_ID("EXT|SQL_PRODUCTID|1.0")]
168
169Class SQL_ProductID: SMS_Class_Template
170{
171    [SMS_Report(true),  key]    string  Release;
172    [SMS_Report(true)      ]    string  ProductID;
173    [SMS_Report(true)      ]    boolean DigitalProductID[];
174};
175
176//======================================================
177//----------------| SQL definition end |----------------
178//======================================================

SQL Query Code

For reference only, the report includes this query.

  1/*
  2.SYNOPSIS
  3    Gets SQL product info.
  4.DESCRIPTION
  5    Gets SQL product info, id and product key.
  6.NOTES
  7    Created by Ioan Popovici.
  8    Requires the usp_PivotWithDynamicColumns stored procedure (SQL Support Functions).
  9    Requires SQL Property and ProductID HWI extensions.
 10    Part of a report should not be run separately.
 11.LINK
 12    https://MEMZ.one/SW-SQL-Server-Products
 13.LINK
 14    https://MEMZ.one/SQL-SupportFunctions
 15.LINK
 16    https://MEMZ.one/SW-SQL-Server-Products-CHANGELOG
 17.LINK
 18    https://MEMZ.one/SW-SQL-Server-Products-GIT
 19.LINK
 20    https://MEM.Zone/ISSUES
 21*/
 22
 23/*##=============================================*/
 24/*## QUERY BODY                                  */
 25/*##=============================================*/
 26/* #region QueryBody */
 27
 28/* Test variable declaration !! Need to be commented for Production !! */
 29-- DECLARE @UserSIDs          AS NVARCHAR(10) = 'Disabled';
 30-- DECLARE @CollectionID      AS NVARCHAR(10) = 'SMS00001';
 31-- DECLARE @Filter            AS NVARCHAR(20) = 'WID';
 32
 33/* Variable declaration */
 34DECLARE @TableName         AS NVARCHAR(MAX);
 35DECLARE @NonPivotedColumn  AS NVARCHAR(MAX);
 36DECLARE @DynamicColumn     AS NVARCHAR(MAX);
 37DECLARE @AggregationColumn AS NVARCHAR(MAX);
 38DECLARE @StaticColumnList  AS NVARCHAR(MAX);
 39
 40/* Perform cleanup */
 41IF OBJECT_ID('tempdb..#SQLProducts', 'U') IS NOT NULL
 42    DROP TABLE #SQLProducts;
 43
 44/* Create SQLProducts table */
 45CREATE TABLE #SQLProducts (
 46    ResourceID          NVARCHAR(25)
 47    , SKUName           NVARCHAR(100)
 48    , [Version]         NVARCHAR(25)
 49    , FileVersion       NVARCHAR(50)
 50    , SPLevel           NVARCHAR(2)
 51    , IsClustered       NVARCHAR(3)
 52    , SQMReporting      NVARCHAR(3)
 53)
 54
 55/* Create SQLRelease table */
 56DECLARE @SQLRelease Table (FileVersion NVARCHAR(4), Release NVARCHAR(10))
 57
 58/* Populate StaticColumnList */
 59SET @StaticColumnList = N'[SKUNAME],[VERSION],[FILEVERSION],[SPLEVEL],[CLUSTERED],[SQMREPORTING]'
 60
 61/* Populate SQLRelease table */
 62INSERT INTO @SQLRelease (FileVersion, Release)
 63VALUES
 64    ('2022', '2022')
 65    , ('2019', '2019')
 66    , ('2017', '2017')
 67    , ('2016', '2017')
 68    , ('2015', '2016')
 69    , ('2014', '2014')
 70    , ('2013', '2014')
 71    , ('2012', '2012')
 72    , ('2011', '2012')
 73    , ('2010', '2012')
 74    , ('2009', '2008 R2')
 75    , ('2007', '2008')
 76    , ('2005', '2005')
 77    , ('2000', '2000')
 78    , ('',     'Unknown')
 79
 80/* Get SQL 2022 data */
 81INSERT INTO #SQLProducts
 82EXECUTE dbo.usp_PivotWithDynamicColumns
 83    @TableName           = N'dbo.v_GS_EXT_SQL_2022_Property0'
 84    , @NonPivotedColumn  = N'ResourceID'
 85    , @DynamicColumn     = N'PropertyName0'
 86    , @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
 87	, @StaticColumnList  = @StaticColumnList;
 88
 89/* Get SQL 2019 data */
 90INSERT INTO #SQLProducts
 91EXECUTE dbo.usp_PivotWithDynamicColumns
 92    @TableName           = N'dbo.v_GS_EXT_SQL_2019_Property0'
 93    , @NonPivotedColumn  = N'ResourceID'
 94    , @DynamicColumn     = N'PropertyName0'
 95    , @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
 96	, @StaticColumnList  = @StaticColumnList;
 97
 98/* Get SQL 2017 data */
 99INSERT INTO #SQLProducts
100EXECUTE dbo.usp_PivotWithDynamicColumns
101    @TableName           = N'dbo.v_GS_EXT_SQL_2017_Property0'
102    , @NonPivotedColumn  = N'ResourceID'
103    , @DynamicColumn     = N'PropertyName0'
104    , @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
105	, @StaticColumnList  = @StaticColumnList;
106
107/* Get SQL 2016 data */
108INSERT INTO #SQLProducts
109EXECUTE dbo.usp_PivotWithDynamicColumns
110    @TableName           = N'dbo.v_GS_EXT_SQL_2016_Property0'
111    , @NonPivotedColumn  = N'ResourceID'
112    , @DynamicColumn     = N'PropertyName0'
113    , @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
114    , @StaticColumnList  = @StaticColumnList;
115
116/* Get SQL 2014 data data */
117INSERT INTO #SQLProducts
118EXECUTE dbo.usp_PivotWithDynamicColumns
119    @TableName           = N'dbo.v_GS_EXT_SQL_2014_Property0'
120    , @NonPivotedColumn  = N'ResourceID'
121    , @DynamicColumn     = N'PropertyName0'
122    , @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
123    , @StaticColumnList  = @StaticColumnList;
124
125/* Get SQL 2012 data */
126INSERT INTO #SQLProducts
127EXECUTE dbo.usp_PivotWithDynamicColumns
128    @TableName           = N'dbo.v_GS_EXT_SQL_2012_Property0'
129    , @NonPivotedColumn  = N'ResourceID'
130    , @DynamicColumn     = N'PropertyName0'
131    , @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
132    , @StaticColumnList  = @StaticColumnList;
133
134/* Get SQL 2008 data */
135INSERT INTO #SQLProducts
136EXECUTE dbo.usp_PivotWithDynamicColumns
137    @TableName           = N'dbo.v_GS_EXT_SQL_2008_Property0'
138    , @NonPivotedColumn  = N'ResourceID'
139    , @DynamicColumn     = N'PropertyName0'
140    , @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
141    , @StaticColumnList  = @StaticColumnList;
142
143/* Get SQL Legacy data */
144INSERT INTO #SQLProducts
145EXECUTE dbo.usp_PivotWithDynamicColumns
146    @TableName           = N'dbo.v_GS_EXT_SQL_Legacy_Property0'
147    , @NonPivotedColumn  = N'ResourceID'
148    , @DynamicColumn     = N'PropertyName0'
149    , @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
150    , @StaticColumnList  = @StaticColumnList;
151
152/* Aggregate result data */
153WITH SQLProducts_CTE (Release, EditionGroup, [Edition], [Version], ServicePack, CUVersion, IsClustered, Bitness, CEIPReporting, ProductKey, Device, DomainOrWorkgroup, OperatingSystem, IsVirtualMachine, CPUs, PhysicalCores, LogicalCores)
154AS (
155    SELECT
156        Release             = (
157            'SQL ' + (SELECT Release FROM @SQLRelease WHERE FileVersion = LEFT(SQLProducts.FileVersion, 4))
158        )
159        , EditionGroup      = (
160            CASE
161                WHEN SQLProducts.SKUName LIKE '%enter%' THEN 'Enterprise'
162                WHEN SQLProducts.SKUName LIKE '%stand%' THEN 'Standard'
163                WHEN SQLProducts.SKUName LIKE '%expre%' THEN 'Express'
164                WHEN SQLProducts.SKUName LIKE '%devel%' THEN 'Developer'
165                WHEN SQLProducts.SKUName LIKE '%windo%' THEN 'WID'
166                WHEN SQLProducts.SKUName IS NULL        THEN 'N/A'
167                ELSE 'Legacy'
168            END
169        )
170        , [Edition]         = ISNULL(NULLIF(SQLProducts.SKUName, ''), 'N/A')
171        , [Version]         = SQLProducts.[Version]
172        , ServicePack       = SQLProducts.SPLevel
173        , CUVersion         = SQLProducts.FileVersion
174        , IsClustered       = (
175            CASE SQLProducts.IsClustered
176                WHEN 0 THEN 'No'
177                WHEN 1 THEN 'Yes'
178                ELSE NULL
179            END
180        )
181        , Bitness           = (
182            CASE
183                WHEN SQLProducts.SKUName LIKE '%64%' THEN 'x64'
184                WHEN SQLProducts.SKUName IS NOT NULL THEN 'x86'
185                ELSE 'N/A'
186            END
187        )
188        , CEIPReporting     = (
189            CASE SQLProducts.SQMReporting
190                WHEN 0 THEN 'No'
191                WHEN 1 THEN 'Yes'
192                ELSE NULL
193            END
194        )
195        , ProductKey        = ISNULL(SQLProductID.DigitalProductID0, 'N/A')
196        , Device            = Devices.[Name]
197        , DomainOrWorkgroup = ISNULL(Systems.Full_Domain_Name0, Systems.Resource_Domain_Or_Workgr0)
198        , OperatingSystem = (
199            IIF(
200                OperatingSystem.Caption0 != N''
201                , CONCAT(
202                    REPLACE(OperatingSystem.Caption0, N'Microsoft ', N''),         --Remove 'Microsoft ' from OperatingSystem
203                    REPLACE(OperatingSystem.CSDVersion0, N'Service Pack ', N' SP') --Replace 'Service Pack ' with ' SP' in OperatingSystem
204                )
205                , Systems.Operating_System_Name_And0
206            )
207        )
208        , IsVirtualMachine  = (
209            CASE Devices.IsVirtualMachine
210                WHEN 0 THEN 'No'
211                WHEN 1 THEN 'Yes'
212                ELSE NULL
213            END
214        )
215        , CPUs              = COUNT(Processor.ResourceID)
216        , PhysicalCores     = SUM(Processor.NumberOfCores0)
217        , LogicalCores      = SUM(Processor.NumberOfLogicalProcessors0)
218    FROM fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembers
219        JOIN v_R_System AS Systems ON Systems.ResourceID = CollectionMembers.ResourceID
220        JOIN v_CombinedDeviceResources AS Devices ON Devices.MachineID = CollectionMembers.ResourceID
221        JOIN v_GS_PROCESSOR AS Processor ON Processor.ResourceID = CollectionMembers.ResourceID
222        JOIN #SQLProducts AS SQLProducts ON SQLProducts.ResourceID = CollectionMembers.ResourceID
223        LEFT JOIN fn_rbac_GS_OPERATING_SYSTEM(@UserSIDs) AS OperatingSystem ON OperatingSystem.ResourceID = CollectionMembers.ResourceID
224        LEFT JOIN dbo.v_GS_EXT_SQL_PRODUCTID0 AS SQLProductID ON SQLProductID.ResourceID = SQLProducts.ResourceID
225            AND SQLProductID.Release0 = (
226                SELECT Release FROM @SQLRelease WHERE FileVersion = LEFT(SQLProducts.FileVersion, 4)
227            )
228            AND SQLProductID.ProductID0 IS NOT NULL
229    WHERE CollectionMembers.CollectionID = @CollectionID
230    GROUP BY
231        SQLProducts.FileVersion
232        , SQLProducts.SKUName
233        , SQLProducts.[Version]
234        , SQLProducts.SPLevel
235        , SQLProducts.IsClustered
236        , SQLProducts.SQMReporting
237        , SQLProductID.DigitalProductID0
238        , Devices.[Name]
239        , Systems.Full_Domain_Name0
240        , Systems.Resource_Domain_Or_Workgr0
241        , Systems.Operating_System_Name_and0
242        , Systems.Build01
243        , OperatingSystem.Caption0
244        , OperatingSystem.CSDVersion0
245        , Devices.IsVirtualMachine
246        , Processor.NumberOfCores0
247        , Processor.NumberOfLogicalProcessors0
248)
249
250/* Filter results */
251SELECT
252    Release
253    , EditionGroup
254    , [Edition]
255    , [Version]
256    , ServicePack
257    , CUVersion
258    , IsClustered
259    , Bitness
260    , CEIPReporting
261    , ProductKey
262    , Device
263    , DomainOrWorkgroup
264    , OperatingSystem
265    , IsVirtualMachine
266    , CPUs
267    , PhysicalCores
268    , LogicalCores
269FROM SQLProducts_CTE
270WHERE EditionGroup NOT IN (@Filter)
271
272/* Perform cleanup */
273IF OBJECT_ID('tempdb..#SQLProducts', 'U') IS NOT NULL
274    DROP TABLE #SQLProducts;
275
276/* #endregion */
277/*##=============================================*/
278/*## END QUERY BODY                              */
279/*##=============================================*/

VB Support Function Code

For reference only, the report includes this function.

 1'.SYNOPSIS
 2'    Gets SQL product key.
 3'.DESCRIPTION
 4'    Gets SQL product key from a binary string array.
 5'.PARAMETER astrBinaryKey
 6'    Specifies the obfuscated key.
 7'.PARAMETER intVersion
 8'    Specifies the SQL version.
 9'.EXAMPLE
10'    Code.GetSQLProductKey(Fields!SomeField.Value, 12) (SSRS)
11'.EXAMPLE
12'    GetSQLProductKey({1, 1, 1, 1, 0, 0, 1, 1, 0, 1, 1, 1, 0, 1, 1, 0}, 12) (VB.Net)
13'.NOTES
14'    Created by Ioan Popovici.
15'    Credit to Jakob Bindslet and Chrissy LeMaire.
16'    I only translated the script in Visual Basic, nothing else.
17'.LINK
18'    http://mspowershell.blogspot.com/2010/11/sql-server-product-key.html (Jakob Bindslet)
19'    https://gallery.technet.microsoft.com/scriptcenter/Get-SQL-Server-Product-4b5bf4f8 (Chrissy LeMaire)
20'.LINK
21'    https://MEM.Zone
22'.LINK
23'    https://MEMZ.one/SW-SQL-Server-Products
24'.LINK
25'    https://MEM.Zone/ISSUES
26'
27'/*##=============================================*/
28'/*## SCRIPT BODY                                 */
29'/*##=============================================*/
30'/* #region FunctionBody */
31
32Function GetSQLProductKey(ByVal astrBinaryKey As String(), ByVal intVersion As Integer) As String
33    Dim achrKeyChars As Char() = {"B", "C", "D", "F", "G", "H", "J", "K", "M", "P", "Q", "R", "T", "V", "W", "X", "Y", "2", "3", "4", "6", "7", "8", "9"}
34    Dim strSQLProductKey As String
35    Dim iastrBinaryKey As Long
36    Dim iachrKeyChars As Long
37    Dim iastrBinaryKeyOuterLoop As Long
38    Dim iastrBinaryKeyInnerLoop As Long
39    Try
40        If (intVersion >= 11) Then
41            iastrBinaryKey = 0
42        Else
43            iastrBinaryKey = 52
44        End If
45        For iastrBinaryKeyOuterLoop = 24 To 0 Step -1
46            iachrKeyChars = 0
47            For iastrBinaryKeyInnerLoop = 14 To 0 Step -1
48                iachrKeyChars = iachrKeyChars * 256 Xor astrBinaryKey(iastrBinaryKeyInnerLoop + iastrBinaryKey)
49                astrBinaryKey(iastrBinaryKeyInnerLoop + iastrBinaryKey) = Math.Truncate(iachrKeyChars / 24)
50                iachrKeyChars = iachrKeyChars Mod 24
51            Next iastrBinaryKeyInnerLoop
52            strSQLProductKey = achrKeyChars(iachrKeyChars) + strSQLProductKey
53            If (iastrBinaryKeyOuterLoop Mod 5) = 0 And iastrBinaryKeyOuterLoop <> 0 Then
54                strSQLProductKey = "-" + strSQLProductKey
55            End If
56        Next iastrBinaryKeyOuterLoop
57    Catch
58        strSQLProductKey = "Cannot decode product key."
59    End Try
60    GetSQLProductKey = strSQLProductKey
61End Function
62
63'/* #endregion */
64'/*##=============================================*/
65'/*## END SCRIPT BODY                             */
66'/*##=============================================*/
Notes

Credit to Jakob Bindslet and Chrissy LeMaire.

SHARE

article card image dark article card image light

Published by · Jan 7, 2019 reports · 2 mins read

Configuration Manager Device Boundary and Network Information Report

List Device Boundaries and Network Information with Configuration Manager ...

See More
article card image dark article card image light

Published by · Jan 28, 2023 configmgr · 1 mins read

Application Detection Method using the Configuration Manager Application Version

Replace hardcoded application version in scripts, with the Configuration Manager Application Version ...

See More