—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_CUSTOM_SQL_Legacy_Property_2_00] [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;