0
/ConfigMgr_A01/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}
None
a6956036-330b-4f81-9ac1-36ab952cf9ad
DataSource1
=Parameters!UserSIDs.Value
SELECT
v_Collection_Alias.CollectionID ,v_Collection_Alias.Name
FROM
fn_rbac_Collection(@UserSIDs) v_Collection_Alias
WHERE
v_Collection_Alias.CollectionType = 2
order by v_Collection_Alias.Name
true
CollectionID
System.String
Name
System.String
DataSource1
=Parameters!CollectionID.Value
true
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;
true
ComputerName
System.String
SQL Type
System.String
Service Pack
System.Int32
CU Version
System.String
Release
System.String
Edition
System.String
Version
System.String
Bitness
System.String
DataSource1
=Parameters!UserTokenSIDs.Value
select dbo.fn_rbac_GetAdminIDsfromUserSIDs (@UserTokenSIDs) as UserSIDs
UserSIDs
System.String
true
true
Microsoft SQL Servers Inventory
Textbox26
0.10888in
0.39792in
2.48958in
#fdfdfb
#fdfdfb
#fdfdfb
#e1e1e1
Middle
2pt
2pt
2pt
2pt
2.02375cm
6.22003cm
3.47896cm
1.52104cm
3.92875cm
1.865cm
0.6cm
true
true
Numbers
Textbox73
#4c68a2
2pt
2pt
2pt
2pt
true
true
Computer Name
Textbox45
#4c68a2
2pt
2pt
2pt
2pt
true
true
Version
Textbox46
#4c68a2
2pt
2pt
2pt
2pt
true
true
SP
Textbox83
#4c68a2
2pt
2pt
2pt
2pt
true
true
CU
Textbox50
#4c68a2
2pt
2pt
2pt
2pt
true
true
Bitness
Textbox1
#4c68a2
2pt
2pt
2pt
2pt
0.6cm
true
true
Textbox69
#c6daf8
2pt
2pt
2pt
2pt
true
true
=Fields!ComputerName.Value
ComputerName1
White
2pt
2pt
2pt
2pt
true
true
=Fields!Version.Value
Version
White
2pt
2pt
2pt
2pt
true
true
=Fields!Service_Pack.Value
Service_Pack
White
2pt
2pt
2pt
2pt
true
true
=Fields!CU_Version.Value
CU_Version
White
2pt
2pt
2pt
2pt
true
true
=Fields!Bitness.Value
Bitness
White
2pt
2pt
2pt
2pt
true
0.6cm
true
true
=Count(Fields!ComputerName.Value)
ComputerName4
#c6daf8
2pt
2pt
2pt
2pt
true
true
Textbox69
#c6daf8
2pt
2pt
2pt
2pt
true
true
Textbox70
#c6daf8
2pt
2pt
2pt
2pt
true
true
Textbox71
#c6daf8
2pt
2pt
2pt
2pt
true
true
Textbox72
#c6daf8
2pt
2pt
2pt
2pt
true
true
Textbox3
#c6daf8
2pt
2pt
2pt
2pt
0.6cm
true
true
=Count(Fields!ComputerName.Value)
ComputerName2
#9eb6e4
2pt
2pt
2pt
2pt
true
true
Textbox64
#9eb6e4
2pt
2pt
2pt
2pt
true
true
Textbox65
#9eb6e4
2pt
2pt
2pt
2pt
true
true
Textbox66
#9eb6e4
2pt
2pt
2pt
2pt
true
true
Textbox67
#9eb6e4
2pt
2pt
2pt
2pt
true
true
Textbox4
#9eb6e4
2pt
2pt
2pt
2pt
0.6cm
true
true
=Count(Fields!ComputerName.Value)
ComputerName3
#7292cc
2pt
2pt
2pt
2pt
true
true
Textbox58
#7292cc
2pt
2pt
2pt
2pt
true
true
Textbox59
#7292cc
2pt
2pt
2pt
2pt
true
true
Textbox78
#7292cc
2pt
2pt
2pt
2pt
true
true
Textbox61
#7292cc
2pt
2pt
2pt
2pt
true
true
Textbox5
#7292cc
2pt
2pt
2pt
2pt
5.2305cm
true
true
Release
Textbox54
#4c68a2
2pt
2pt
2pt
2pt
4.04857cm
true
true
Edition
Textbox52
#4c68a2
2pt
2pt
2pt
2pt
After
=Fields!Release.Value
=Fields!Version.Value
5.2305cm
true
true
=Fields!Release.Value
Release
#9eb6e4
2pt
2pt
2pt
2pt
=Fields!Edition.Value
=Fields!Edition.Value
4.04857cm
true
true
=Fields!Edition.Value
Edition1
#c6daf8
2pt
2pt
2pt
2pt
true
Edition1
Before
true
Release
4.04857cm
true
true
Total
Textbox63
#9eb6e4
2pt
2pt
2pt
2pt
Before
5.2305cm
true
true
Total
Textbox56
#7292cc
2pt
2pt
2pt
2pt
4.04857cm
true
true
Total
Textbox57
#7292cc
2pt
2pt
2pt
2pt
Before
DataSetSQLVersions
1.46366cm
3cm
28.3166cm
1
true
0.08208in
0.125in
1.75735in
11.17952in
#2b627f
4.81465cm
#616161
28.71348cm
0.65315cm
true
true
true
Copyright © SCCM-Zone.com
Textbox23
0.125in
0.24271in
11.14827in
2pt
2pt
2pt
2pt
#616161
21cm
29.7cm
2.54cm
2.54cm
2.54cm
2.54cm
1.27cm
String
=SrsResources.UserIdentity.GetUserSIDs(User!UserID)
UserTokenSIDs
true
7b5f538e-1262-4705-a7df-7d4d398a195e
/Report Parts/UserTokenSIDs
2015-02-25T10:56:20.9331936+01:00
String
DataSetAdminID
UserSIDs
UserSIDs
true
2c7b7dc1-c255-43f4-b6eb-9836f5be644e
/Report Parts/UserSIDs
2015-02-25T10:56:21.3870000+01:00
String
Collection
DataSetCollection
CollectionID
Name
SrsResources, culture=neutral
Cm
http://resscmp02/ReportServer
6fbd236a-d836-4950-8788-14b828deb616