IoanPopovici
1/19/2018 - 4:36 PM
Gets SQL version information
Gets SQL Service Pack and Cumulative Update version information. Requires SCCM HWI Extension
/*
*********************************************************************************************************
* Requires | SCCM Hardware extentsion *
* ===================================================================================================== *
* Modified by | Date | Revision | Comments *
* _____________________________________________________________________________________________________ *
* Octavian Cordos | 2016-01-15 | v1.0 | First version *
* Ioan Popovici | 2017-01-18 | v1.1 | Format and fixes, added support for SQL 2016 *
* Ioan Popovici | 2018-01-19 | v1.2 | Support for SQL 2017, bitness, workgroup edition *
* ===================================================================================================== *
* *
*********************************************************************************************************
.SYNOPSIS
This SQL Query is used to get SQL Service Pack and Cumulative Update version information.
.DESCRIPTION
This SQL Query is used to get SQL Service Pack and Cumulative Update version information.
.NOTES
Part of a report should not be run separately.
.LINK
https://sccm-zone.com
https://github.com/JhonnyTerminus/SCCM
*/
/*##=============================================*/
/*## QUERY BODY */
/*##=============================================*/
--DECLARE @CollectionID VARCHAR(16);
--SELECT @CollectionID = 'WT10000A';
WITH temp (
[ResourceID],
[ComputerName],
--[Company],
[SQL TYPE],
[SQL Service Pack],
[SQL Version],
[SQL CU Version],
[Version]
) AS (
SELECT DISTINCT
[ResourceID],
[ComputerName],
--[Company],
[SQL TYPE],
[SQL Service Pack],
[SQL Version],
[SQL CU Version],
[Version]
FROM (
--region SQL 2017
SELECT
[vrs].[resourceID] AS [ResourceID],
[VRS].[Netbios_name0] [ComputerName],
--ISNULL([vrs].[company0], '<Unknown>') AS 'Company',
--vrs.company0 as 'Company',
MAX (
CASE [sql2017].[PropertyName0]
WHEN 'SKUName' THEN [sql2017].[PropertySTRValue0]
END
) AS [SQL TYPE],
MAX (
CASE [sql2017].[PropertyName0]
WHEN 'SPLEVEL' THEN [sql2017].[PropertyNUMValue0]
END
) AS [SQL Service Pack],
MAX (
CASE [sql2017].[PropertyName0]
WHEN 'VERSION' THEN [sql2017].[PropertySTRValue0]
END
) AS [SQL Version],
MAX (
CASE [sql2017].[PropertyName0]
WHEN 'FILEVERSION' THEN [sql2017].[PropertySTRValue0]
END
) AS [SQL CU Version],
MAX (
CASE [sql2017].[PropertyName0]
WHEN 'FILEVERSION' THEN
CASE LEFT ([sql2017].[PropertySTRValue0], 4)
WHEN '2017' THEN '2017'
WHEN '2016' THEN '2016'
WHEN '2014' THEN '2014'
WHEN '2011' THEN '2012'
WHEN '2009' THEN '2008 R2'
WHEN '2007' THEN '2008'
WHEN '2005' THEN '2005'
WHEN '2000' THEN '2000'
ELSE '2016'
END
END
) AS [Version]
FROM [dbo].[v_R_System] [VRS]
LEFT JOIN [v_GS_SQL_2017_Property0] [sql2017] ON [sql2017].[ResourceID] = [VRS].[ResourceID]
LEFT OUTER JOIN [v_ClientCollectionMembers] [c] ON [c].[ResourceID] = [vrs].[ResourceID]
WHERE [sql2017].[PropertyName0] IN ('SKUNAME', 'SPLevel', 'version', 'fileversion')
AND [c].[CollectionID] = @CollectionID
AND ISNULL([sql2017].[ServiceName0], 0) NOT LIKE '%EXPRESS%'
GROUP BY
[VRS].[Netbios_name0],
[sql2017].[ServiceName0],
--[vrs].[company0],
[vrs].[resourceID]
UNION ALL
--endregion
--region SQL 2016
SELECT
[vrs].[resourceID] [ResourceID],
[VRS].[Netbios_name0] [ComputerName],
--ISNULL([vrs].[company0], '<Unknown>') AS 'Company',
--vrs.company0 as 'Company',
MAX (
CASE [sql2016].[PropertyName0]
WHEN 'SKUName' THEN [sql2016].[PropertySTRValue0]
END
) AS [SQL TYPE],
MAX (CASE [sql2016].[PropertyName0]
WHEN 'SPLEVEL' THEN [sql2016].[PropertyNUMValue0]
END
) AS [SQL Service Pack],
MAX (
CASE [sql2016].[PropertyName0]
WHEN 'VERSION' THEN [sql2016].[PropertySTRValue0]
END
) AS [SQL Version],
MAX (
CASE [sql2016].[PropertyName0]
WHEN 'FILEVERSION' THEN [sql2016].[PropertySTRValue0]
END
) AS [SQL CU Version],
MAX (
CASE [sql2016].[PropertyName0]
WHEN 'FILEVERSION' THEN
CASE LEFT ([sql2016].[PropertySTRValue0], 4)
WHEN '2017' THEN '2017'
WHEN '2016' THEN '2016'
WHEN '2014' THEN '2014'
WHEN '2011' THEN '2012'
WHEN '2009' THEN '2008 R2'
WHEN '2007' THEN '2008'
WHEN '2005' THEN '2005'
WHEN '2000' THEN '2000'
ELSE '2016'
END
END
) AS [Version]
FROM [dbo].[v_R_System] [VRS]
LEFT JOIN [v_GS_SQL_2016_Property0] [sql2016] ON [sql2016].[ResourceID] = [VRS].[ResourceID]
LEFT OUTER JOIN [v_ClientCollectionMembers] [c] ON [c].[ResourceID] = [vrs].[ResourceID]
WHERE [sql2016].[PropertyName0] IN ('SKUNAME', 'SPLevel', 'version', 'fileversion')
AND [c].[CollectionID] = @CollectionID
AND ISNULL([sql2016].[ServiceName0], 0) NOT LIKE '%EXPRESS%'
GROUP BY [VRS].[Netbios_name0],
[sql2016].[ServiceName0],
--[vrs].[company0],
[vrs].[resourceID]
--endregion
--region SQL 2014
UNION ALL
SELECT
[vrs].[resourceID] [ResourceID],
[VRS].[Netbios_name0] [ComputerName],
--ISNULL([vrs].[company0], '<Unknown>') AS 'Company',
--vrs.company0 as 'Company',
MAX (
CASE [sql2014].[PropertyName0]
WHEN 'SKUName' THEN [sql2014].[PropertySTRValue0]
END
) AS [SQL TYPE],
MAX (
CASE [sql2014].[PropertyName0]
WHEN 'SPLEVEL' THEN [sql2014].[PropertyNUMValue0]
END
) AS [SQL Service Pack],
MAX (CASE [sql2014].[PropertyName0]
WHEN 'VERSION' THEN [sql2014].[PropertySTRValue0]
END
) AS [SQL Version],
MAX (
CASE [sql2014].[PropertyName0]
WHEN 'FILEVERSION' THEN [sql2014].[PropertySTRValue0]
END
) AS [SQL CU Version],
MAX (
CASE [sql2014].[PropertyName0]
WHEN 'FILEVERSION' THEN
CASE LEFT([sql2014].[PropertySTRValue0], 4)
WHEN '2017' THEN '2017'
WHEN '2016' THEN '2016'
WHEN '2014' THEN '2014'
WHEN '2011' THEN '2012'
WHEN '2009' THEN '2008 R2'
WHEN '2007' THEN '2008'
WHEN '2005' THEN '2005'
WHEN '2000' THEN '2000'
ELSE '2014'
END
END
) AS [Version]
FROM [v_R_System] [VRS]
LEFT JOIN [v_GS_SQL_2014_Property0] [sql2014] ON [sql2014].[ResourceID] = [VRS].[ResourceID]
LEFT OUTER JOIN [v_ClientCollectionMembers] [c] ON [c].[ResourceID] = [vrs].[ResourceID]
WHERE [sql2014].[PropertyName0] IN ('SKUNAME', 'SPLevel', 'version', 'fileversion')
AND [c].[CollectionID] = @CollectionID
AND ISNULL([sql2014].[ServiceName0], 0) NOT LIKE '%EXPRESS%'
GROUP BY
[VRS].[Netbios_name0],
[sql2014].[ServiceName0],
--[vrs].[company0],
[vrs].[resourceID]
--endregion
--region SQL 2012
UNION ALL
SELECT
[vrs].[resourceID] [ResourceID],
[VRS].[Netbios_name0] [ComputerName],
--ISNULL([vrs].[company0], '<Unknown>') AS 'Company',
MAX (
CASE [sql2012].[PropertyName0]
WHEN 'SKUName' THEN [sql2012].[PropertySTRValue0]
END
) AS [SQL TYPE],
MAX (
CASE [sql2012].[PropertyName0]
WHEN 'SPLEVEL' THEN [sql2012].[PropertyNUMValue0]
END
) AS [SQL Service Pack],
MAX (
CASE [sql2012].[PropertyName0]
WHEN 'VERSION' THEN [sql2012].[PropertySTRValue0]
END
) AS [SQL Version],
MAX (CASE [sql2012].[PropertyName0]
WHEN 'FILEVERSION' THEN [sql2012].[PropertySTRValue0]
END
) AS [SQL CU Version],
MAX (
CASE [sql2012].[PropertyName0]
WHEN 'FILEVERSION' THEN
CASE LEFT([sql2012].[PropertySTRValue0], 4)
WHEN '2017' THEN '2017'
WHEN '2016' THEN '2016'
WHEN '2014' THEN '2014'
WHEN '2011' THEN '2012'
WHEN '2009' THEN '2008 R2'
WHEN '2007' THEN '2008'
WHEN '2005' THEN '2005'
WHEN '2000' THEN '2000'
ELSE '2012'
END
END
) AS [Version]
FROM [V_R_System] [VRS]
LEFT JOIN [v_GS_SQL_2012_Property0] [sql2012] ON [sql2012].[ResourceID] = [VRS].[ResourceID]
LEFT OUTER JOIN [v_ClientCollectionMembers] [c] ON [c].[ResourceID] = [vrs].[ResourceID]
WHERE [sql2012].[PropertyName0] IN('SKUNAME', 'SPLevel', 'version', 'fileversion')
AND [c].[CollectionID] = @CollectionID
AND ISNULL([ServiceName0], 0) NOT LIKE '%EXPRESS%'
GROUP BY
[VRS].[Netbios_name0],
[sql2012].[ServiceName0],
--[vrs].[company0],
[vrs].[resourceID]
--endregion
--region SQL 2008
UNION ALL
SELECT
[vrs].[resourceID] [ResourceID],
[VRS].[Netbios_name0] [ComputerName],
--ISNULL([vrs].[company0], '<Unknown>') AS 'Company',
MAX (
CASE [sql2008].[PropertyName0]
WHEN 'SKUName' THEN [sql2008].[PropertySTRValue0]
END
) AS [SQL TYPE],
MAX (
CASE [sql2008].[PropertyName0]
WHEN 'SPLEVEL' THEN [sql2008].[PropertyNUMValue0]
END
) AS [SQL Service Pack],
MAX (
CASE [sql2008].[PropertyName0]
WHEN 'VERSION' THEN [sql2008].[PropertySTRValue0]
END
) AS [SQL Version],
MAX (
CASE [sql2008].[PropertyName0]
WHEN 'FILEVERSION' THEN [sql2008].[PropertySTRValue0]
END
) AS [SQL CU Version],
MAX (
CASE [sql2008].[PropertyName0]
WHEN 'FILEVERSION' THEN
CASE LEFT([sql2008].[PropertySTRValue0], 4)
WHEN '2017' THEN '2017'
WHEN '2016' THEN '2016'
WHEN '2014' THEN '2014'
WHEN '2011' THEN '2012'
WHEN '2009' THEN '2008 R2'
WHEN '2007' THEN '2008'
WHEN '2005' THEN '2005'
WHEN '2000' THEN '2000'
ELSE '2008'
END
END
) AS [Version]
FROM [V_R_System] [VRS]
LEFT JOIN [v_GS_SQL_2008_Property0] [sql2008] ON [sql2008].[ResourceID] = [VRS].[ResourceID]
LEFT OUTER JOIN [v_ClientCollectionMembers] [c] ON [c].[ResourceID] = [vrs].[ResourceID]
WHERE [sql2008].[PropertyName0] IN ('SKUNAME', 'SPLevel', 'version', 'fileversion')
AND [c].[CollectionID] = @CollectionID
AND ISNULL([sql2008].[ServiceName0], 0) NOT LIKE '%EXPRESS%'
AND ISNULL([sql2008].[ServiceName0], 0) NOT LIKE 'SQLBrowser'
GROUP BY
[VRS].[Netbios_name0],
[sql2008].[ServiceName0],
--[vrs].[company0],
[vrs].[resourceID]
--endregion
--region SQL Legacy
UNION ALL
SELECT [vrs].[resourceID] [ResourceID],
[VRS].[Netbios_name0] [ComputerName],
--ISNULL([vrs].[company0], '<Unknown>') AS 'Company',
MAX (
CASE [sqlLgcy].[PropertyName0]
WHEN 'SKUName' THEN [sqlLgcy].[PropertySTRValue0]
END
) AS [SQL TYPE],
MAX (
CASE [sqlLgcy].[PropertyName0]
WHEN 'SPLEVEL' THEN [sqlLgcy].[PropertyNUMValue0]
END
) AS [SQL Service Pack],
MAX (
CASE [sqlLgcy].[PropertyName0]
WHEN 'VERSION' THEN [sqlLgcy].[PropertySTRValue0]
END
) AS [SQL Version],
MAX (
CASE [sqlLgcy].[PropertyName0]
WHEN 'FILEVERSION' THEN [sqlLgcy].[PropertySTRValue0]
END
) AS [SQL CU Version],
MAX (
CASE [sqllgcy].[PropertyName0]
WHEN 'FILEVERSION' THEN
CASE LEFT ([sqllgcy].[PropertySTRValue0], 4)
WHEN '2017' THEN '2017'
WHEN '2016' THEN '2016'
WHEN '2014' THEN '2014'
WHEN '2011' THEN '2012'
WHEN '2009' THEN '2008 R2'
WHEN '2007' THEN '2008'
WHEN '2005' THEN '2005'
WHEN '2000' THEN '2000'
ELSE '2005'
END
END
) AS [Version]
FROM [V_R_System] [VRS]
LEFT JOIN [v_GS_SQL_Legacy_Property0] [sqlLgcy] ON [sqlLgcy].[ResourceID] = [VRS].[ResourceID]
LEFT OUTER JOIN [v_ClientCollectionMembers] [c] ON [c].[ResourceID] = [vrs].[ResourceID]
WHERE [sqlLgcy].[PropertyName0] IN('SKUNAME', 'SPLevel', 'version', 'fileversion')
AND [c].[CollectionID] = @CollectionID
AND ISNULL([sqlLgcy].[ServiceName0], 0) NOT LIKE '%EXPRESS%'
AND ISNULL([sqlLgcy].[ServiceName0], 0) NOT LIKE 'SQLBrowser'
GROUP BY
[VRS].[Netbios_Name0],
[sqlLgcy].[ServiceName0],
--[vrs].[company0],
[vrs].[resourceID]
) AS [SQLInv]
--endregion
WHERE [SQL TYPE] NOT LIKE 'Express%'
AND [SQL TYPE] NOT LIKE 'Windows Internal Database%'
AND SUBSTRING ( [SQL Version], 1, 2) != SUBSTRING ([SQL CU Version], 1, 2) )
SELECT DISTINCT
--[Company],
[ComputerName],
[SQL Type],
[SQL Service Pack] AS [Service Pack],
[SQL Version] AS Version,
[SQL CU Version] AS [CU Version],
[version] AS Release,
(
CASE
WHEN [SQL Type] LIKE '%workgroup%' THEN 'Workgroup Edition'
WHEN [SQL Type] LIKE '%develop%' THEN 'Developer Edition'
WHEN [SQL Type] LIKE '%standard%' THEN 'Standard Edition'
WHEN [SQL Type] LIKE '%enterprise%' THEN 'Enterprise Edition'
END
) AS 'Edition',
(
CASE
WHEN [SQL Type] LIKE '%64%' THEN 'x64'
ELSE 'x32'
END
) AS 'Bitness'
FROM temp
ORDER BY
--temp.[Company],
Release,
Edition,
Bitness,
Version,
ComputerName;
/*##=============================================*/
/*## END QUERY BODY */
/*##=============================================*/