Quick Summary

  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/*##=============================================*/

  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//=====================================================

  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//======================================================

  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/*##=============================================*/