SCCM SQL Queries -1

1. A Specific System Is Part of what are all Collections

2. All Users Names Part of Domain Admin Group

3. Advertisement status for specific Collection ID with Computer Details

4. All SQL Server Installed Version and Computer Details

5. All Computers with Last Heartbeat Discovery Time Stamp

6. All IE Version using Software Inventory

7. All Outlook Version using Software Inventory

8. All Packages Total Targeted DP Counts

9. All Packages Compare targeted Packages on Two DPS

10. All Software Distribution Packages without Advertisements

11. All Client Settings Status

12. All Workstations Agent Health Status  

13. All Workstations Agent Detailed Health Status  

14. All Workstations Client Health Summary Status

15. All Active and Inactive Workstations Client Status  

16. All Active Workstations Client Health Evaluation Status  

17. All Active Workstations Client Heartbeat (DDR) Status

18. All Active Workstations Client Hardware Inventory Status

19. All Active Workstations Client Software Inventory Status  

20. All Active Workstations Client Policy Request Status  

21. All PCs discovered from specific site

22. All PCs Information with IP address and subnet details  

23. All PCs with chassis type information

24. All Desktops and Laptops counts details

25. All PCs Information with subnet and OU details  

26. All PCs with particular application last used date  

27. All PCs with particular software inventory Exe file

28. All PCs with Username and Email ID details  

29. All PCs with Configuration Manager Console installed details

30. All PCs client assigned and installed site code details

31. All PCs with No Clients based on OS Category status

32. All Workstations Client version status  

33. All PCs with chassis type information

34. All Workstations Client Installation Failure status  

35. All Workstations with Last Boot up time status Internal Use Only

36. All ConfigMgr Roles Status  

37. All IE Version using Software Inventory based on OU

38. All Packages which are waiting to distribute content to DPs  

39. All Packages Content Distribution Status

40. All ConfigMgr Issue Servers Status  

41. All Software applications required deployments status within 30 days

42. All Software applications available deployments status within 30 days

43. All Software applications simulate deployments status within 30 days

44. All Software packages required deployments status within 30 days

45. All Software packages available deployments status within 30 days  

46. All Software updates required deployments status within 30 days

47. All Software updates available deployments status within 30 days  

48. All OSD required deployments status within 30 days 26

49. All OSD available deployments status within 30 days

50. All ConfigMgr Roles Detailed status

51. All SCCM Server Software Update Sync status  

52. All Software applications required deployments status within 5 days

53. All Software packages required deployments status within 5 days  

54. All Software updates required deployments status within 5 days

55. All Software applications deployments status within 30 days  

56. All Software packages deployments status within 30 days  

57. All Software updates deployments status within 30 days

58. All OS deployments status within 30 days  

59. All Software updates deployments status within 30 days

60. All OS deployments status within 30 days

61. All Site Servers Issue MP components status

62. All Site Servers Issue DP components status

63. All Site Servers Issue DDR components status  

64. All Site Servers Issue CCR components status  

65. All Site Servers Issue WSUS components status

66. All Site Servers Issue Discovery components status

67. All Site Servers Issue Collection Evaluator components status

68. All Site Servers Issue Hardware Inventory components status  

69. All Site Servers Issue Despooler components status

70. All Site Servers Issue Inbox Monitor components status

71. All Site Servers Issue Component Monitor components status

72. All Site Servers Issue Others components status

73. All Workstations Not Assigned Clients detailed status

74. All Workstations Unhealthy Clients detailed status Internal Use Only

75. All Workstations Inactive Clients detailed status

76. All Obsolete Clients detailed status  

77. All Packages available in SCCM  

78. All Collections available in SCCM  

79. All Managed Workstations details status

80. All Workstations Assets Inventory details status

81. All PCs with Office 365 Installed Machines Report Based on Installed Software  

82. All PCs without Office 365 Installed Machines Report Based on Installed Software  

83. All PCs with SEP Antivirus Installed Machines Report Based on Installed Software

84. All PCs without SEP Antivirus Installed Machines Report Based on Installed Software  

85. All Workstations Client Agent Detailed Report  

86. All Workstations Low Free Disk Space Report  

87. All Workstations Machines Names Last Logon with Serial No Report  

88. All Workstations with Adobe Acrobat Reader Installed Machines Report

89. All Workstations with Adobe Acrobat Reader Last Usage Machines Report

90. All Workstations with Adobe Products Not Used More Than 90 Days Machines Report

91. All Client and Inventory Health Report

92. All Total Scope machines details

93. All Total Healthy machines details

94. All Total Unhealthy machines details

95. All Total Hardware Inventory within 30 Days machines details

96. All Total Hardware Inventory not within 30 Days machines details

97. All Total Software Inventory within 30 Days machines details

98. All Total Software Inventory not within 30 Days machines details  

99. All Total WSUS Scan within 30 Days machines details

100. All Total WSUS Scan not within 30 Days machines details

101. All Deployments status for Specific Application

102. All Deployments status for Specific Package

103. All Deployments status for Specific Software Update Group

104. All Deployments status for Specific Task Sequence

105. Deployment status for specific application with specific collection

106. Deployment status for specific package with specific collection  

107. Deployment status for specific software Update deployment

108. All Collections with RefreshType

109. All Software Inventory Report for Specific Computer Based on Installed Software Class

110. All Applications Deployments Status for Specific Computers 

SCCM SQL Queries 

1. A Specific System Is Part of what are all Collections

SELECT v_R_System.Name0,v_R_System.Client0,v_Collection.Name as 'Collection Name',v_Collection.CollectionID

FROM v_FullCollectionMembership

INNER JOIN v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID

INNER JOIN v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID

WHERE (v_R_System.Name0 = 'Client01') and v_Collection.CollectionID not like 'SMS%'

2. All Users Names Part of Domain Admin Group

SELECT

dbo.v_RA_User_UserGroupName.User_Group_Name0, dbo.v_R_User.User_Name0

FROM dbo.v_RA_User_UserGroupName INNER JOIN

dbo.v_R_User ON dbo.v_RA_User_UserGroupName.ResourceID = dbo.v_R_User.ResourceID

WHERE dbo.v_RA_User_UserGroupName.User_Group_Name0 LIKE '%\Domain Admins' ORDER BY

dbo.v_RA_User_UserGroupName.User_Group_Name0

3. Advertisement status for specific Collection ID with Computer Details

Select sys.Netbios_Name0, sys.User_Domain0, sys.AD_Site_Name0, sys.User_Name0,

LastAcceptanceStateName, LastStateName,LastStatusMessageIDName, LastExecutionResult,

MAX(LastStatusTime) as 'LastStatusTime', AdvertisementID,coll.CollectionID

from v_ClientAdvertisementStatus stat

join v_R_System sys on stat.ResourceID=sys.ResourceID

left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID

join v_FullCollectionMembership coll on sys.ResourceID = coll.ResourceID

where stat.AdvertisementID = 'AdvertID' and coll.CollectionID = 'Collection ID'

GROUP BY sys.Netbios_Name0,sys.User_Domain0,sys.AD_Site_Name0, sys.User_Name0,

LastAcceptanceStateName,LastStateName,

LastStatusMessageIDName,LastExecutionResult, LastStatusTime, AdvertisementID,coll.CollectionID

4. All SQL Server Installed Version and Computer Details

Select distinct VRS.name0,ADRM.DisplayName0,ADRM.Version0,ADRM.Publisher0 from v_GS_ADD_REMOVE_PROGRAMS ADRM join v_r_system VRS on VRS.ResourceID = ADRM.ResourceID where ADRM.Publisher0 like '%Microsoft%' and (ADRM.DisplayName0 like '%Microsoft SQL Server%' or ADRM.DisplayName0 like '%SQL Server%') order by VRS.Name0

5. All Computers with Last Heartbeat Discovery Time Stamp

SELECT v_R_System.Netbios_Name0 AS Name, v_R_System.Client0,Min(A.AgentTime) as 'Time Stamp', v_R_System.Operating_System_Name_and0 AS [AD Operating System] FROM v_R_System INNER JOIN v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID =v_R_System.ResourceID inner join v_AgentDiscoveries A ON A.ResourceId=dbo.v_R_System.ResourceID WHERE A.AgentName like 'Heartbeat Discovery' group by Netbios_Name0,Client0,AgentTime, Operating_System_Name_and0 order by Netbios_Name0 desc

Internal Use Only

6. All IE Version using Software Inventory

SELECT b.Netbios_Name0, b.User_Name0, b.AD_Site_Name0, CASE WHEN a.FileVersion LIKE '5.%' THEN 'Internet Explorer 5' WHEN a.FileVersion LIKE '6.%' THEN 'Internet Explorer 6' WHEN a.FileVersion LIKE '7.%' THEN 'Internet Explorer 7' WHEN a.FileVersion LIKE '8.%' THEN 'Internet Explorer 8' WHEN a.FileVersion LIKE '9.%' THEN 'Internet Explorer 9' WHEN a.FileVersion LIKE '10.%' THEN 'Internet Explorer 10' WHEN a.FileVersion LIKE '11.%' THEN 'Internet Explorer 11' ELSE 'Other Version' END AS 'IE Version', a.FileName, a.FileVersion, a.FilePath FROM v_GS_SoftwareFile a JOIN v_R_System b ON a.ResourceID = b.ResourceID JOIN v_RA_System_SystemOUName c ON a.ResourceID = c.ResourceID WHERE FileName = 'iexplore.exe' and FilePath like '_:\Program%Internet Explorer%' GROUP BY b.Netbios_Name0, b.User_Name0, b.AD_Site_Name0, a.FileName, a.FileVersion, a.FilePath ORDER BY b.Netbios_Name0

7. All Outlook Version using Software Inventory

SELECT b.Netbios_Name0, b.User_Name0, CASE WHEN a.FileVersion LIKE '12.%' THEN 'Office 2007' ELSE 'Other Version' END AS 'Office Version', a.FileName, a.FileVersion, a.FilePath FROM v_GS_SoftwareFile a JOIN v_R_System b ON a.ResourceID = b.ResourceID JOIN v_RA_System_SystemOUName c ON a.ResourceID = c.ResourceID WHERE FileName = 'outlook.exe' and FilePath = '_\Pr%' GROUP BY b.Netbios_Name0, b.User_Name0, a.FileName, a.FileVersion, a.FilePath ORDER BY b.Netbios_Name0

8. All Packages Total Targeted DP Counts

SELECT PackageID, Name, Version, Manufacturer, Language, Description,PkgSourcePath , LastRefreshTime, (SELECT COUNT(PkgID) FROM vPkgStatusSummaryDistPts WHERE PkgID = v_Package.PackageID) AS [Total Number of DP’s] FROM v_Package order by [Total Number of DP’s] desc

Internal Use Only

9. All Packages Compare targeted Packages on Two DP

S select s.SiteCode,s.PackageID,p.Name,(p.sourcesize)/1024 as 'Size(MB)', s.sourceversion as 'DPVersion',p.storedpkgversion as 'LastVersion',s.Installstatus as 'Package Status', Case v_Package.PackageType When 0 Then 'Package' When 3 Then 'Driver' When 4 Then 'Task Sequence' When 5 Then 'software Update' When 7 Then 'Virtual' When 8 Then 'Application' When 257 Then 'Image' When 258 Then 'Boot Image' When 259 Then 'OS' Else ' ' END AS 'Type' from v_PackageStatusDistPointsSumm s inner join smspackages p on s.packageid = p.pkgid inner join v_Package on v_Package.PackageID=p.[PkgID] where s.PackageID not in (select PackageID from v_DistributionPoint where ServerNALPath like '%DPServerName1%') and ServerNALPath like '%DPServerName1%' order by 8

10. All Software Distribution Packages without Advertisements select v_Package.PackageID, v_Package.Name,v_Package.SourceVersion,v_Package.SourceDate from dbo.v_package Where packageID not in (select PackageID from dbo.v_Advertisement)and PackageID not in (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo) and v_Package.name not like '%osd%' and V_package.PackageType ='0' group by v_Package.PackageID, v_Package.Name,v_Package.SourceVersion,v_Package.SourceDate order by v_Package.PackageID

11. All Client Settings Status

SELECT * FROM v_CH_Settings where SettingsID = 1

12. All Workstations Agent Health Status

Declare @CollectionID as Varchar(8) Declare @Total as Numeric(8) Declare @Healthy as Numeric(8) Declare @Unhealthy as Numeric(8) Declare @HWInventoryOK as Numeric(8) Declare @HWInventoryNotOK as Numeric(8) Declare @SWInventoryOK as Numeric(8) Declare @SWInventoryNotOK as Numeric(8) Declare @WSUSInventoryOK as Numeric(8) Declare @WSUSInventoryNotOK as Numeric(8)

Set @CollectionID = 'SMS00001' -- specify scope collection ID

select @Total = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like '%Workstation%') )

select @Healthy = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete = 0 and IsClient = 1 and ResourceID in (

Internal Use Only

select ResourceID from v_R_System where Operating_System_Name_and0 like '%Workstation%') )

select @Unhealthy = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID Not in (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete = 0 and IsClient = 1 ) and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like '%Workstation%') )

select @HWInventoryOK = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete = 0 and IsClient = 1 and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like '%Workstation%') and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where DATEDIFF (day,LastHWScan,GetDate())<30) )

select @HWInventoryNotOK = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete = 0 and IsClient = 1 and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like '%Workstation%') and ResourceID Not in (select ResourceID from v_GS_WORKSTATION_STATUS where DATEDIFF (day,LastHWScan,GetDate())<30) )

select @SWInventoryOK = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete = 0 and IsClient = 1 and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like '%Workstation%') and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<30) )

select @SWInventoryNotOK = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete = 0 and IsClient = 1 and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like '%Workstation%') and ResourceID Not in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<30) )

select @WSUSInventoryOK = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete = 0 and IsClient = 1 and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like '%Workstation%') and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and DATEDIFF (day,LastScanTime,GetDate())<30) )

select @WSUSInventoryNotOK = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete = 0 and IsClient = 1 and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like '%Workstation%') and ResourceID Not in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and DATEDIFF (day,LastScanTime,GetDate())<30) )

select @Total as 'Total', @Healthy as 'Healthy', @Unhealthy as 'Unhealthy', @HWInventoryOK as 'HW<30Days', @HWInventoryNotOK as 'HW>30Days', @SWInventoryOK as 'SW<30Days',

Internal Use Only

@SWInventoryNotOK as 'SW>30Days', @WSUSInventoryOK as 'WSUS<30Days', @WSUSInventoryNotOK as 'WSUS>30Days', case when (@Total = 0) or (@Total is null) Then '100' Else (round(@Healthy/ convert (float,@Total)*100,2)) End as 'Healthy%', case when (@Healthy = 0) or (@Healthy is null) Then '100' Else (round(@HWInventoryOK/ convert (float,@Healthy)*100,2)) End as 'HW%', case when (@Healthy = 0) or (@Healthy is null) Then '100' Else (round(@SWInventoryOK/ convert (float,@Healthy)*100,2)) End as 'SW%', case when (@Healthy = 0) or (@Healthy is null) Then '100' Else (round(@WSUSInventoryOK/ convert (float,@Healthy)*100,2)) End as 'WSUS%'

13. All Workstations Agent Detailed Health Status

Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' --Specify the collection ID

select distinct(Name),Case when IsClient= 1 then 'Healthy' else 'Unhealthy' end as 'HealthStatus', (select case when count (v_GS_WORKSTATION_STATUS.ResourceID)=1 then 'Healthy' else 'Unhealthy' end from v_GS_WORKSTATION_STATUS where DATEDIFF (day,LastHWScan,GetDate())<31 and ResourceID=v_FullCollectionMembership.ResourceID) as 'HWScanStatus', (select case when count (v_GS_LastSoftwareScan.ResourceID)=1 then 'Healthy' else 'Unhealthy' end from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<31 and ResourceID=v_FullCollectionMembership.ResourceID) as 'SWScanStatus', (select case when count (v_UpdateScanStatus.ResourceID)=1 then 'Healthy' else 'Unhealthy' end from v_UpdateScanStatus where DATEDIFF (day,LastScanTime,GetDate())<31 and LastErrorCode = 0 and ResourceID=v_FullCollectionMembership.ResourceID) as 'WSUSScanStatus', (select DATEDIFF (day,LastHWScan,GetDate()) from v_GS_WORKSTATION_STATUS where ResourceID=v_FullCollectionMembership.ResourceID) as 'LastHWScanDays', (select DATEDIFF (day,LastScanDate,GetDate()) from v_GS_LastSoftwareScan where ResourceID=v_FullCollectionMembership.ResourceID) as 'LastSWScanDays', (select DATEDIFF (day,LastScanTime,GetDate()) from v_UpdateScanStatus where LastErrorCode = 0 and ResourceID=v_FullCollectionMembership.ResourceID) as 'LastWSUSScanDays' from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like '%Workstation%') order by 2 desc

14. All Workstations Client Health Summary Status

Declare @CollectionID as Varchar(8) Declare @TotalClient as Numeric(8) Declare @ClientInstalled as Numeric(8) Declare @ClientNotInstalled as Numeric(8)

Set @CollectionID = 'SMS00001' --Specify the collection ID

select @TotalClient = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID in ( select ResourceID from v_R_System where (Client0 = 1 or Client0 = 0 or Client0 is null) and Unknown0 is null and Operating_System_Name_and0 like '%Workstation%') )

select @ClientInstalled = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID in ( select ResourceID from v_R_System where Client0 = 1 and Operating_System_Name_and0 like '%Workstation%') )

select @ClientNotInstalled = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID in ( select ResourceID from v_R_System where (Client0 = 0 or Client0 is null) and Unknown0 is null

Internal Use Only

and Operating_System_Name_and0 like '%Workstation%') )

select @TotalClient as 'TotalClient', @ClientInstalled as 'ClientInstalled', @ClientNotInstalled as 'ClientNotInstalled', case when (@TotalClient = 0) or (@TotalClient is null) Then '100' Else (round(@ClientInstalled/ convert (float,@TotalClient)*100,2)) End as 'ClientInstalled%'

15. All Active and Inactive Workstations Client Status

Declare @CollectionID as Varchar(8) Declare @TotalClientInstalled as Numeric(8) Declare @ClientActive as Numeric(8) Declare @ClientInActive as Numeric(8)

Set @CollectionID = 'SMS00001' --Specify the collection ID

select @TotalClientInstalled = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (Ch.ClientActiveStatus = 1 or Ch.ClientActiveStatus = 0) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @ClientActive = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @ClientInActive = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (Ch.ClientActiveStatus = 0) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @TotalClientInstalled as 'TotalClientInstalled', @ClientActive as 'ClientActive', @ClientInActive as 'ClientInActive', case when (@TotalClientInstalled = 0) or (@TotalClientInstalled is null) Then '100' Else (round(@ClientActive/ convert (float,@TotalClientInstalled)*100,2)) End as 'ClientActive%'

16. All Active Workstations Client Health Evaluation Status

Declare @CollectionID as Varchar(8) Declare @TotalActive as Numeric(8) Declare @ActiveEvalPass as Numeric(8) Declare @ActiveEvalFail as Numeric(8) Declare @ActiveEvalUnknown as Numeric(8)

Set @CollectionID = 'SMS00001' --Specify the collection ID

select @TotalActive = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (Ch.ClientStateDescription = 'Active/Pass' or Ch.ClientStateDescription = 'Active/Fail'

Internal Use Only

or Ch.ClientStateDescription = 'Active/Unknown') and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @ActiveEvalPass = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (Ch.ClientStateDescription = 'Active/Pass') and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @ActiveEvalFail = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (Ch.ClientStateDescription = 'Active/Fail') and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @ActiveEvalUnknown = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (Ch.ClientStateDescription = 'Active/Unknown') and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @TotalActive as 'TotalActive', @ActiveEvalPass as 'ActiveEvalPass', @ActiveEvalFail as 'ActiveEvalFail', @ActiveEvalUnknown as 'ActiveEvalUnknown', case when (@TotalActive = 0) or (@TotalActive is null) Then '100' Else (round(@ActiveEvalPass/ convert (float,@TotalActive)*100,2)) End as 'ActiveEvalPass%'

17. All Active Workstations Client Heartbeat (DDR) Status

Declare @CollectionID as Varchar(8) Declare @TotalActive as Numeric(8) Declare @ActiveHeartBeatDDR as Numeric(8) Declare @InActiveHeartBeatDDR as Numeric(8)

Set @CollectionID = 'SMS00001' --Specify the collection ID

select @TotalActive = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @ActiveHeartBeatDDR = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (IsActiveDDR = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @InActiveHeartBeatDDR = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs

Internal Use Only

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (IsActiveDDR = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @TotalActive as 'TotalActive', @ActiveHeartBeatDDR as 'ActiveHeartBeatDDR', @InActiveHeartBeatDDR as 'InActiveHeartBeatDDR', case when (@TotalActive = 0) or (@TotalActive is null) Then '100' Else (round(@ActiveHeartBeatDDR/ convert

(float,@TotalActive)*100,2)) End as 'ActiveHeartBeatDDR%'

18. All Active Workstations Client Hardware Inventory Status

Declare @CollectionID as Varchar(8) Declare @TotalActive as Numeric(8) Declare @ActiveHWInv as Numeric(8) Declare @InActiveHWInv as Numeric(8)

Set @CollectionID = 'SMS00001' --Specify the collection ID

select @TotalActive = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @ActiveHWInv = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (IsActiveHW = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @InActiveHWInv = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (IsActiveHW = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @TotalActive as 'TotalActive', @ActiveHWInv as 'ActiveHWInv', @InActiveHWInv as 'InActiveHWInv', case when (@TotalActive = 0) or (@TotalActive is null) Then '100' Else (round(@ActiveHWInv/ convert (float,@TotalActive)*100,2)) End as 'ActiveHWInv%'

19. All Active Workstations Client Software Inventory Status

Declare @CollectionID as Varchar(8) Declare @TotalActive as Numeric(8) Declare @ActiveSWInv as Numeric(8) Declare @InActiveSWInv as Numeric(8)

Set @CollectionID = 'SMS00001' --Specify the collection ID

select @TotalActive = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID

Internal Use Only

and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @ActiveSWInv = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (IsActiveSW = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @InActiveSWInv = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (IsActiveSW = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @TotalActive as 'TotalActive', @ActiveSWInv as 'ActiveSWInv', @InActiveSWInv as 'InActiveSWInv', case when (@TotalActive = 0) or (@TotalActive is null) Then '100' Else (round(@ActiveSWInv/ convert (float,@TotalActive)*100,2)) End as 'ActiveSWInv%'

20. All Active Workstations Client Policy Request Status

Declare @CollectionID as Varchar(8) Declare @TotalActive as Numeric(8) Declare @ActivePolicyRequest as Numeric(8) Declare @InActivePolicyRequest as Numeric(8)

Set @CollectionID = 'SMS00001' --Specify the collection ID

select @TotalActive = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @ActivePolicyRequest = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (IsActivePolicyRequest = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

select @InActivePolicyRequest = ( select COUNT(*) as 'Count' from v_FullCollectionMembership where CollectionID = @CollectionID and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID where (IsActivePolicyRequest = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like '%Workstation%') )

Internal Use Only

select @TotalActive as 'TotalActive', @ActivePolicyRequest as 'ActivePolicyRequest', @InActivePolicyRequest as 'InActivePolicyRequest', case when (@TotalActive = 0) or (@TotalActive is null) Then '100' Else (round(@ActivePolicyRequest/ convert (float,@TotalActive)*100,2)) End as 'ActivePolicyRequest%'

21. All PCs discovered from specific site

select agent.AgentSite,sys.Netbios_Name0, sys.Resource_Domain_OR_Workgr0, MAX(AgentTime) as AgentTime from v_R_System sys join v_AgentDiscoveries agent on sys.ResourceID=agent.ResourceId where agent.AgentSite= 'A00' group by agent.AgentSite,sys.Netbios_Name0, sys.Resource_Domain_OR_Workgr0 order by agent.AgentSite, Netbios_Name0

22. All PCs Information with IP address and subnet details

Declare @CollectionID as Varchar(8)

Set @CollectionID = 'SMS00001' -- specify scope collection ID

SELECT distinct SYS.Netbios_Name0, [IPAddress0] as 'IP',[IPSubnet0] as 'Mask',[DefaultIPGateway0] as 'Gateway', MAX(IPSub.IP_Subnets0) as 'Subnet',SYS.AD_Site_Name0 as 'ADSiteName', [MACAddress0] as 'MAC',OPSYS.Caption0 as OS, OPSYS.CSDVersion0 as ServicePack, fcm.SiteCode, MEM.TotalPhysicalMemory0, CSYS.Manufacturer0, CSYS.Model0, BIOS.SerialNumber0, OPSYS.InstallDate0, MAX(OU.System_OU_Name0) as OrganizationUnit, SYS.Operating_System_Name_and0 , OPSYS.LastBootUpTime0, [DHCPEnabled0]as 'DHCPEnabled', [DHCPServer0] as 'DHCPServer' FROM v_R_System SYS INNER JOIN v_FullCollectionMembership fcm on SYS.ResourceID=fcm.ResourceID INNER JOIN v_RA_System_SystemOUName OU on SYS.ResourceID=OU.ResourceID LEFT JOIN v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID LEFT JOIN v_GS_PC_BIOS BIOS on SYS.ResourceID = BIOS.ResourceID LEFT JOIN v_RA_System_IPSubnets IPSub on SYS.ResourceID = IPSub.ResourceID LEFT JOIN v_GS_NETWORK_ADAPTER_CONFIGUR IP on SYS.ResourceID = IP.ResourceID WHERE IP.DefaultIPGateway0 IS NOT NULL and fcm. CollectionID = @CollectionID Group by SYS.Netbios_Name0, fcm.SiteCode,OPSYS.Caption0,OPSYS.CSDVersion0, SYS.Operating_System_Name_and0,MEM.TotalPhysicalMemory0, CSYS.Manufacturer0, CSYS.Model0, BIOS.SerialNumber0, OPSYS.InstallDate0, OPSYS.LastBootUpTime0,IP.MACAddress0,IP.IPAddress0, IP.IPSubnet0, Sys.AD_Site_Name0,IP.DefaultIPGateway0,IP.DHCPEnabled0,IP.DHCPServer0

23. All PCs with chassis type information

Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' --Specify the collection ID

Select distinct (v_R_System.ResourceID), v_R_System.Name0 as 'Machine Name', AD_Site_Name0 as 'AD Site', v_R_System.Operating_System_Name_and0 as 'Operating System', v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Installed Site', 'Chassis Type' = CASE when ChassisTypes0 = 1 THEN 'Virtual Machine' when ChassisTypes0 = 2 THEN 'Unknown' when ChassisTypes0 = 3 THEN 'Desktop' when ChassisTypes0 = 4 THEN 'Low-profile Desktop' when ChassisTypes0 = 5 THEN 'Pizza Box' when ChassisTypes0 = 6 THEN 'Mini Tower' when ChassisTypes0 = 7 THEN 'Tower' when ChassisTypes0 = 8 THEN 'Portable' when ChassisTypes0 = 9 THEN 'Laptop' when ChassisTypes0 = 10 THEN 'Notebook'

Internal Use Only

when ChassisTypes0 = 11 THEN 'Handheld' when ChassisTypes0 = 12 THEN 'Docking Station' when ChassisTypes0 = 13 THEN 'All-in-One' when ChassisTypes0 = 14 THEN 'Subnotebook' when ChassisTypes0 = 15 THEN 'Space-Saving' when ChassisTypes0 = 16 THEN 'Lunch Box' when ChassisTypes0 = 17 THEN 'Main System chassis' when ChassisTypes0 = 18 THEN 'Expansion chassis' when ChassisTypes0 = 19 THEN 'Sub-Chassis' when ChassisTypes0 = 20 THEN 'Bus-expansion chassis' when ChassisTypes0 = 21 THEN 'Peripheral chassis' when ChassisTypes0 = 22 THEN 'Storage chassis' when ChassisTypes0 = 23 THEN 'Rack-mount chassis' when ChassisTypes0 = 24 THEN 'Sealed-case computer' END from v_R_System inner join v_GS_SYSTEM_ENCLOSURE on (v_GS_SYSTEM_ENCLOSURE.ResourceID = v_R_System.ResourceID) inner join v_RA_System_SMSInstalledSites on (v_RA_System_SMSInstalledSites.ResourceID = v_R_System.ResourceID) inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System.ResourceID) where v_FullCollectionMembership.CollectionID = @CollectionID

24. All Desktops and Laptops counts details

SELECT CASE

WHEN ChassisTypes0 = '8' THEN 'Notebooks' WHEN ChassisTypes0 = '9' THEN 'Notebooks' WHEN ChassisTypes0 = '10' THEN 'Notebooks' ELSE 'Desktops' END AS "Workstation Type", count(sys.name0) as ClientCount from v_R_System SYS LEFT JOIN v_GS_SYSTEM_ENCLOSURE ENC ON ENC.ResourceID = SYS.ResourceID LEFT JOIN v_FullCollectionMembership FCM ON FCM.ResourceID = ENC.ResourceID WHERE FCM.CollectionID = 'Collection ID' and sys.Obsolete0 = 0 GROUP BY CASE ChassisTypes0

WHEN '8' THEN 'Notebooks' WHEN '9' THEN 'Notebooks' WHEN '10' THEN 'Notebooks' ELSE 'Desktops' END ORDER BY 2 desc

25. All PCs Information with subnet and OU details

SELECT DISTINCT SYS.Netbios_Name0, SYS.User_Name0,OPSYS.InstallDate0 as InitialInstall, BIOS.SerialNumber0, CSYS.Model0, MEM.TotalPhysicalMemory0, HWSCAN.LastHWScan, ASSG.SMS_Installed_Sites0, MAX(IPSub.IP_Subnets0) as 'Subnet', OPSYS.Caption0 as 'OS Name', MAX(SYSOU.System_OU_Name0) as 'OU' FROM v_R_System as SYS JOIN v_RA_System_SMSInstalledSites as ASSG on SYS.ResourceID=ASSG.ResourceID LEFT JOIN v_RA_System_IPSubnets IPSub on SYS.ResourceID = IPSub.ResourceID LEFT JOIN v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID LEFT JOIN v_GS_PROCESSOR Processor on Processor.ResourceID = SYS.ResourceID LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID LEFT JOIN v_GS_LastSoftwareScan SWSCAN on SYS.ResourceID = SWSCAN.ResourceID LEFT JOIN v_GS_PC_BIOS BIOS on SYS.ResourceID = BIOS.ResourceID LEFT JOIN v_RA_System_SystemOUName SYSOU on SYS.ResourceID=SYSOU.ResourceID LEFT JOIN v_R_User USR on SYS.User_Name0 = USR.User_Name0

Internal Use Only

LEFT JOIN v_FullCollectionMembership FCM on FCM.ResourceID = SYS.ResourceID WHERE SYS.Obsolete0 = 0 --AND FCM.CollectionID = 'Collection ID' GROUP BY SYS.Netbios_Name0, SYS.Obsolete0,SYS.Resource_Domain_OR_Workgr0, CSYS.Manufacturer0, CSYS.Model0, BIOS.SerialNumber0,OPSYS.InstallDate0,HWSCAN.LastHWScan, MEM.TotalPhysicalMemory0, SYS.User_Name0, SYS.User_Domain0, ASSG.SMS_Installed_Sites0, SYS.Client_Version0, OPSYS.Caption0 ORDER BY OPSYS.InstallDate0 DESC

26. All PCs with particular application last used date

Declare @Monthold int set @Monthold = 2 SELECT DISTINCT SYS.Netbios_Name0 as Name, SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath, max(apps.LastUsedTime0) as LastUsedTime, SYS.User_Name0 as Login, CSYS.Manufacturer0 as Manufacturer, CSYS.Model0 as Model, BIOS.SerialNumber0 as SN, MAX(IPSub.IP_Subnets0) as 'Subnet', sys.AD_Site_Name0 as ADSite, MAX(SYSOU.System_OU_Name0) as 'OU' FROM v_GS_SoftwareFile SF join v_R_System SYS on SYS.ResourceID = SF.ResourceID LEFT JOIN v_RA_System_IPSubnets IPSub on SYS.ResourceID = IPSub.ResourceID LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID LEFT JOIN v_GS_PC_BIOS BIOS on SYS.ResourceID = BIOS.ResourceID LEFT JOIN v_RA_System_SystemOUName SYSOU on SYS.ResourceID=SYSOU.ResourceID LEFT JOIN v_R_User USR on SYS.User_Name0 = USR.User_Name0 LEFT JOIN v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID LEFT JOIN (select * from v_GS_CCM_RECENTLY_USED_APPS where ExplorerFileName0 = 'notepad.exe') APPS on SYS.ResourceID = APPS.ResourceID Where SF.FileName LIKE 'notepad.exe' GROUP BY SYS.Netbios_Name0, --apps.LastUsedTime0, SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath, SYS.User_Name0, CSYS.Manufacturer0, CSYS.Model0, BIOS.SerialNumber0, sys.AD_Site_Name0 HAVING max(apps.LastUsedTime0) < dateadd(month, -(@Monthold), dateadd(day,0,datediff(day,0,getdate()))) OR max(apps.LastUsedTime0) IS NULL ORDER BY SYS.Netbios_Name0

27. All PCs with particular software inventory Exe file

SELECT DISTINCT SYS.Netbios_Name0 as Name, SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath, SYS.User_Name0 as Login, CSYS.Manufacturer0 as Manufacturer, CSYS.Model0 as Model, BIOS.SerialNumber0 as SN, MAX(IPSub.IP_Subnets0) as 'Subnet', sys.AD_Site_Name0 as ADSite, MAX(SYSOU.System_OU_Name0) as 'OU' FROM v_GS_SoftwareFile SF join v_R_System SYS on SYS.ResourceID = SF.ResourceID LEFT JOIN v_RA_System_IPSubnets IPSub on SYS.ResourceID = IPSub.ResourceID LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID LEFT JOIN v_GS_PC_BIOS BIOS on SYS.ResourceID = BIOS.ResourceID LEFT JOIN v_RA_System_SystemOUName SYSOU on SYS.ResourceID=SYSOU.ResourceID LEFT JOIN v_R_User USR on SYS.User_Name0 = USR.User_Name0 LEFT JOIN v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID Where SF.FileName LIKE 'Microsoft.ConfigurationManagement.exe' GROUP BY SYS.Netbios_Name0,SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath, SYS.User_Name0, CSYS.Manufacturer0, CSYS.Model0, BIOS.SerialNumber0, sys.AD_Site_Name0 ORDER BY SYS.Netbios_Name0

Internal Use Only

28. All PCs with Username and Email ID details

SELECT SYS.User_Name0 as Login, USR.Mail0 as 'EMail ID', SYS.Netbios_Name0 as Machine, Operating_System_Name_and0 as OS FROM v_R_System SYS JOIN v_R_User USR on USR.User_Name0 = SYS.User_Name0 --WHERE SYS.User_Name0 LIKE 'Username' ORDER BY SYS.User_Name0, SYS.Netbios_Name0

29. All PCs with Configuration Manager Console installed details

Select sys.Name0 As Name, sys.user_name0 as UserName, arp.DisplayName0 as DisplayName, arp.Publisher0 as Publisher, arp.Version0 as Version, max(arp.InstallDate0) AS InstallDate FROM v_Add_Remove_Programs arp JOIN v_R_System sys ON arp.ResourceID = sys.ResourceID LEFT JOIN v_FullCollectionMembership fcm on SYS.ResourceID=fcm.ResourceID LEFT JOIN v_R_User USR on SYS.User_Name0 = USR.User_Name0 WHERE (arp.DisplayName0 like '%System Center%Configuration Manager Console%') and arp.Publisher0 like '%Microsoft%' GROUP BY sys.name0,sys.user_name0, arp.displayName0, arp.publisher0, arp.Version0 ORDER BY arp.displayName0, arp.publisher0

30. All PCs client assigned and installed site code details

Select v_R_System.Netbios_Name0 as 'NetBios Name', v_R_System.AD_Site_Name0 as 'AD Site', v_R_System.Active0 as 'isActive', v_R_System.Obsolete0 as 'isObsolete', v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 as 'Assigned Site', v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Installed Site' from v_R_System inner join v_RA_System_SMSAssignedSites on (v_R_System.ResourceID = v_RA_System_SMSAssignedSites.ResourceID) inner join v_RA_System_SMSInstalledSites on (v_R_System.ResourceID = v_RA_System_SMSInstalledSites.ResourceID) Where SMS_Assigned_Sites0 like '%' and SMS_Installed_Sites0 like '%'

31. All PCs with No Clients based on OS Category status

Declare @CollectionID as Varchar(8) Declare @TotalNoClientAgent as Numeric(8) Declare @NoClientAgentWindowsOS as Numeric(8) Declare @NoClientAgentWindowsOSLastLogonwithin7Days as Numeric(8) Declare @NoClientAgentWindowsOSNOtLastLogonwithin7Days as Numeric(8) Declare @NoClientAgentNonWindowsOS as Numeric(8)

Set @CollectionID = 'SMS00001' -- specify scope collection ID

select @TotalNoClientAgent = ( Select count(Vrs.ResourceID) as 'Count' from v_R_System Vrs inner join v_FullCollectionMembership Vf on Vrs.ResourceID = Vf.ResourceID where (Vrs.Client0 = 0 or Vrs.Client0 is null) and Vf.CollectionID = @CollectionID ) select @NoClientAgentWindowsOS = ( Select count(Vrs.ResourceID) as 'Count' from v_R_System Vrs inner join v_FullCollectionMembership Vf on Vrs.ResourceID = Vf.ResourceID where (Vrs.Client0 = 0 or Vrs.Client0 is null) and Vrs.Unknown0 is null and Vrs.Operating_System_Name_and0 like '%windows%' and Vf.CollectionID = @CollectionID ) select @NoClientAgentWindowsOSLastLogonwithin7Days = ( Select count(Vrs.ResourceID) as 'Count' from v_R_System Vrs

Internal Use Only

inner join v_FullCollectionMembership Vf on Vrs.ResourceID = Vf.ResourceID where (Vrs.Client0 = 0 or Vrs.Client0 is null) and Vrs.Unknown0 is null and Vrs.Operating_System_Name_and0 like '%windows%' and (DATEDIFF(day,Last_Logon_Timestamp0, GetDate())) < 7 and Vf.CollectionID = @CollectionID ) select @NoClientAgentWindowsOSNOtLastLogonwithin7Days = ( Select count(Vrs.ResourceID) as 'Count' from v_R_System Vrs inner join v_FullCollectionMembership Vf on Vrs.ResourceID = Vf.ResourceID where (Vrs.Client0 = 0 or Vrs.Client0 is null) and Vrs.Unknown0 is null and Vrs.Operating_System_Name_and0 like '%windows%' and (DATEDIFF(day,Last_Logon_Timestamp0, GetDate())) >= 7 and Vf.CollectionID = @CollectionID ) select @NoClientAgentNonWindowsOS = ( Select count(Vrs.ResourceID) as 'Count' from v_R_System Vrs inner join v_FullCollectionMembership Vf on Vrs.ResourceID = Vf.ResourceID where (Vrs.Client0 = 0 or Vrs.Client0 is null) and Vrs.Unknown0 is null and Vrs.Operating_System_Name_and0 Not like '%windows%' and Vf.CollectionID = @CollectionID )

select @TotalNoClientAgent as 'TotalNoClientAgent', @NoClientAgentWindowsOS as 'NoClientAgentWindowsOS', @NoClientAgentNonWindowsOS as 'NoClientAgentNonWindowsOS', @NoClientAgentWindowsOSLastLogonwithin7Days as'NoClientAgentWindowsOSLastLogonwithin7Days', @NoClientAgentWindowsOSNOtLastLogonwithin7Days as 'NoClientAgentWindowsOSNOtLastLogonwithin7Days', case when (@NoClientAgentWindowsOS = 0) or (@NoClientAgentWindowsOS is null) Then '100' Else (round(@NoClientAgentWindowsOSLastLogonwithin7Days/ convert (float,@NoClientAgentWindowsOS)*100,2)) End as 'NoClientAgentWindowsOSLastLogonwithin7Days%

32. All Workstations Client version status

Declare @CollectionID as Varchar(8)

Set @CollectionID = 'SMS00001' -- specify scope collection ID

Select sys.Client_Version0 as 'Client Agent Version', count (sys.ResourceID) as 'Count' from v_R_System sys inner join v_CH_ClientSummary ch on sys.ResourceID = ch.ResourceID inner join v_FullCollectionMembership Vf on sys.ResourceID = Vf.ResourceID where (Ch.ClientActiveStatus = 1 and Sys.Operating_System_Name_and0 like '%Workstation%') and Vf.CollectionID = @CollectionID Group by sys.Client_Version0 Order by sys.Client_Version0 desc

33. All PCs with chassis type information

Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' --Specify the collection ID

Select distinct (v_R_System.ResourceID), v_R_System.Name0 as 'Machine Name', AD_Site_Name0 as 'AD Site', v_R_System.Operating_System_Name_and0 as 'Operating System', v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Installed Site', 'Chassis Type' = CASE when ChassisTypes0 = 1 THEN 'Virtual Machine' when ChassisTypes0 = 2 THEN 'Unknown'

Internal Use Only

when ChassisTypes0 = 3 THEN 'Desktop' when ChassisTypes0 = 4 THEN 'Low-profile Desktop' when ChassisTypes0 = 5 THEN 'Pizza Box' when ChassisTypes0 = 6 THEN 'Mini Tower' when ChassisTypes0 = 7 THEN 'Tower' when ChassisTypes0 = 8 THEN 'Portable' when ChassisTypes0 = 9 THEN 'Laptop' when ChassisTypes0 = 10 THEN 'Notebook' when ChassisTypes0 = 11 THEN 'Handheld' when ChassisTypes0 = 12 THEN 'Docking Station' when ChassisTypes0 = 13 THEN 'All-in-One' when ChassisTypes0 = 14 THEN 'Subnotebook' when ChassisTypes0 = 15 THEN 'Space-Saving' when ChassisTypes0 = 16 THEN 'Lunch Box' when ChassisTypes0 = 17 THEN 'Main System chassis' when ChassisTypes0 = 18 THEN 'Expansion chassis' when ChassisTypes0 = 19 THEN 'Sub-Chassis' when ChassisTypes0 = 20 THEN 'Bus-expansion chassis' when ChassisTypes0 = 21 THEN 'Peripheral chassis' when ChassisTypes0 = 22 THEN 'Storage chassis' when ChassisTypes0 = 23 THEN 'Rack-mount chassis' when ChassisTypes0 = 24 THEN 'Sealed-case computer' END from v_R_System inner join v_GS_SYSTEM_ENCLOSURE on (v_GS_SYSTEM_ENCLOSURE.ResourceID = v_R_System.ResourceID) inner join v_RA_System_SMSInstalledSites on (v_RA_System_SMSInstalledSites.ResourceID = v_R_System.ResourceID) inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System.ResourceID) where v_FullCollectionMembership.CollectionID = @CollectionID

34. All Workstations Client Installation Failure status

select count(cdr.MachineID) as 'Count', cdr.CP_LastInstallationError as 'Error Code' from v_CombinedDeviceResources cdr where cdr.IsClient = 0 and cdr.DeviceOS like '%Windows%' group by cdr.CP_LastInstallationError

35. All Workstations with Last Boot up time status

Declare @CollectionID as Varchar(8)

Set @CollectionID = 'SMS00001' --Specify the collection ID

select 'Last Reboot within 7 days' as TimePeriod,Count(sys.Name0) as 'Count',1 SortOrder from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId inner join v_FullCollectionMembership Vf on sys.ResourceID = Vf.ResourceID inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where os.LastBootUpTime0 < DATEADD(day,-7, GETDATE()) and ch.ClientActiveStatus = 1 and sys.Operating_System_Name_and0 like '%workstation%' and Vf.CollectionID = @CollectionID UNION select 'Last Reboot within 14 days' as TimePeriod,Count(sys.Name0) as 'Count',2 from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId inner join v_FullCollectionMembership Vf on sys.ResourceID = Vf.ResourceID inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where os.LastBootUpTime0 < DATEADD(day,-14, GETDATE()) and ch.ClientActiveStatus = 1 and sys.Operating_System_Name_and0 like '%workstation%' and Vf.CollectionID = @CollectionID UNION select 'Last Reboot within 1 month' as TimePeriod,Count(sys.Name0) as 'Count',3 from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId

Internal Use Only

inner join v_FullCollectionMembership Vf on sys.ResourceID = Vf.ResourceID inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where os.LastBootUpTime0 < DATEADD(month,-1, GETDATE()) and ch.ClientActiveStatus = 1 and sys.Operating_System_Name_and0 like '%workstation%' and Vf.CollectionID = @CollectionID UNION select 'Last Reboot within 3 months' as TimePeriod,Count(sys.Name0) as 'Count',4 from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId inner join v_FullCollectionMembership Vf on sys.ResourceID = Vf.ResourceID inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where os.LastBootUpTime0 < DATEADD(month,-3, GETDATE()) and ch.ClientActiveStatus = 1 and sys.Operating_System_Name_and0 like '%workstation%' and Vf.CollectionID = @CollectionID UNION select 'Last Reboot within 6 months' as TimePeriod,Count(sys.Name0) as 'Count',5 from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId inner join v_FullCollectionMembership Vf on sys.ResourceID = Vf.ResourceID inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where os.LastBootUpTime0 < DATEADD(month,-6, GETDATE()) and ch.ClientActiveStatus = 1 and sys.Operating_System_Name_and0 like '%workstation%' and Vf.CollectionID = @CollectionID UNION select 'Last Reboot within 12 months' as TimePeriod,Count(sys.Name0) as 'Count',6 from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId inner join v_FullCollectionMembership Vf on sys.ResourceID = Vf.ResourceID inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where os.LastBootUpTime0 < DATEADD(month,-12, GETDATE()) and ch.ClientActiveStatus = 1 and sys.Operating_System_Name_and0 like '%workstation%' and Vf.CollectionID = @CollectionID UNION select 'Total Machines Count' as TimePeriod,Count(sys.Name0) as 'Count',7 from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId inner join v_FullCollectionMembership Vf on sys.ResourceID = Vf.ResourceID inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where ch.ClientActiveStatus = 1 and sys.Operating_System_Name_and0 like '%workstation%' and Vf.CollectionID = @CollectionID Order By SortOrder

36. All ConfigMgr Roles Status

select distinct (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Site System') as 'SiteSys', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Component Server') as 'CompSer', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Site Server') as 'SiteSer', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Management Point') as 'MP', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Distribution Point') as 'DP', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS SQL Server') as 'SQL', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Software Update Point') as 'SUP', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS SRS Reporting Point') as 'SSRS', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Reporting Point') as 'RPT', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Fallback Status Point') as 'FSP', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Server Locator Point') as 'SLP', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS PXE Service Point') as 'PXE', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS System Health Validator') as 'SysVal', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS State Migration Point') as 'SMP', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Notification Server') as 'NotiSer', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Provider') as 'SMSPro', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Application Web Service') as 'WebSer', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Portal Web Site') as 'WebSite', (select COUNT(*) from v_SystemResourceList where RoleName = 'SMS Branch distribution point') as 'BranDP' from v_SystemResourceList

Internal Use Only

37. All IE Version using Software Inventory based on OU

SELECT distinct b.Netbios_Name0, b.User_Name0, b.AD_Site_Name0, CASE WHEN a.FileVersion LIKE '5.%' THEN 'Internet Explorer 5' WHEN a.FileVersion LIKE '6.%' THEN 'Internet Explorer 6' WHEN a.FileVersion LIKE '7.%' THEN 'Internet Explorer 7' WHEN a.FileVersion LIKE '8.%' THEN 'Internet Explorer 8' WHEN a.FileVersion LIKE '9.%' THEN 'Internet Explorer 9' WHEN a.FileVersion LIKE '10.%' THEN 'Internet Explorer 10' WHEN a.FileVersion LIKE '11.%' THEN 'Internet Explorer 11' ELSE 'Other Version' END AS 'IE Version', a.FileName, a.FileVersion, c.System_OU_Name0 FROM v_GS_SoftwareFile a JOIN v_R_System b ON a.ResourceID = b.ResourceID JOIN v_RA_System_SystemOUName c ON a.ResourceID = c.ResourceID WHERE a.FileName = 'iexplore.exe' and a.FilePath like '_:\Program%Internet Explorer%' and c.System_OU_Name0 like 'LAB.COM/COMPUTERS' GROUP BY b.Netbios_Name0, b.User_Name0, b.AD_Site_Name0, a.FileName, a.FileVersion, c.System_OU_Name0 ORDER BY b.Netbios_Name0

38. All Packages which are waiting to distribute content to DPs

Select SubString(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath)+2,(CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath))-3) as ServerName, dp.SiteCode as 'SiteCode', dp.PackageID as 'PackageID', p.Name as 'PackageName', P.SourceVersion as 'SourceVersion', P.LastRefreshTime as 'LastRefreshTime', stat.InstallStatus as 'InstallStatus' from v_DistributionPoint dp left join v_PackageStatusDistPointsSumm stat on dp.ServerNALPath=stat.ServerNALPath and dp.PackageID=stat.PackageID left join v_PackageStatus pstat on dp.ServerNALPath=pstat.PkgServer and dp.PackageID=pstat.PackageID left outer join v_Package p on dp.packageid = p.packageid where stat.InstallStatus not in ('Package Installation complete') ORDER BY 1

39. All Packages Content Distribution Status

select dbo.v_SystemResourceList.ServerName as 'ServerName', dbo.v_SystemResourceList.SiteCode, (select count(*)from v_PackageStatusDistPointsSumm where servernalpath = nalpath) as 'Targetted', (select count(*)from v_PackageStatusDistPointsSumm where installstatus ='Package Installation complete'and servernalpath = nalpath) as 'Installed',

Internal Use Only

(select count(*)from v_PackageStatusDistPointsSumm where (installstatus ='Content updating' or installstatus ='Waiting to install package' or installstatus ='Content monitoring') and servernalpath = nalpath) as 'Waiting', (select count(*)from v_PackageStatusDistPointsSumm where installstatus like'%Retry%'and servernalpath = nalpath) as 'Retrying', (select count(*)from v_PackageStatusDistPointsSumm where installstatus ='Waiting to remove package' and servernalpath = nalpath) as 'Removing', (select count(*)from v_PackageStatusDistPointsSumm where installstatus like'%Fail%' and servernalpath = nalpath) as 'Failed', (select ROUND((100 * (select count(*)from v_PackageStatusDistPointsSumm where installstatus ='Package Installation complete' and servernalpath = nalpath)/(select count(*)from v_PackageStatusDistPointsSumm where servernalpath = nalpath)),2)) as 'Compliance %' from dbo.v_SystemResourceList join v_PackageStatusDistPointsSumm on dbo.v_SystemResourceList.nalpath = v_PackageStatusDistPointsSumm.servernalpath where dbo.v_SystemResourceList.RoleName = 'SMS Distribution Point' group by dbo.v_SystemResourceList.SiteCode, dbo.v_SystemResourceList.servername, dbo.v_SystemResourceList.nalpath order by 1

40. All ConfigMgr Issue Servers Status

Select SiteStatus.SiteCode, SiteInfo.ServerName, SiteInfo.SiteName, SiteStatus.Updated 'TimeStamp', Case SiteInfo.Status When 1 Then 'Active' When 2 Then 'Pending' When 3 Then 'Failed' When 4 Then 'Deleted' When 5 Then 'Upgrade' Else ' ' END AS 'SiteState', Case SiteStatus.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End AS 'Status' From V_SummarizerSiteStatus SiteStatus Join v_Site SiteInfo on SiteStatus.SiteCode = SiteInfo.SiteCode where SiteInfo.Status <> 1 or SiteStatus.Status = 2 Order By SiteCode

41. All Software applications required deployments status within 30 days

Declare @SoftwareAppDeploymentsReportNeededDays as integer Set @SoftwareAppDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AssignmentName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Vaa.ApplicationName as 'ApplicationName', CASE when Vaa.DesiredConfigType = 1 Then 'Install' when vaa.DesiredConfigType = 2 Then 'Uninstall' Else 'Others' End as 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays' from v_DeploymentSummary Ds left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 1 and Ds.DeploymentIntent = 1 and Ds.CreationTime > GETDATE()-@SoftwareAppDeploymentsReportNeededDays order by Ds.DeploymentTime desc

Internal Use Only

42. All Software applications available deployments status within 30 days

Declare @SoftwareAppDeploymentsReportNeededDays as integer Set @SoftwareAppDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AssignmentName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Vaa.ApplicationName as 'ApplicationName', CASE when Vaa.DesiredConfigType = 1 Then 'Install' when vaa.DesiredConfigType = 2 Then 'Uninstall' Else 'Others' End as 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays' from v_DeploymentSummary Ds left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 1 and Ds.DeploymentIntent = 2 and Ds.CreationTime > GETDATE()-@SoftwareAppDeploymentsReportNeededDays order by Ds.DeploymentTime desc

43. All Software applications simulate deployments status within 30 days

Declare @SoftwareAppDeploymentsReportNeededDays as integer Set @SoftwareAppDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AssignmentName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Vaa.ApplicationName as 'ApplicationName', CASE when Vaa.DesiredConfigType = 1 Then 'Install' when vaa.DesiredConfigType = 2 Then 'Uninstall' Else 'Others' End as 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' when Ds.DeploymentIntent = 3 Then 'Simulate' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays' from v_DeploymentSummary Ds

Internal Use Only

left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 1 and Ds.DeploymentIntent = 3 and Ds.CreationTime > GETDATE()-@SoftwareAppDeploymentsReportNeededDays order by Ds.DeploymentTime desc

44. All Software packages required deployments status within 30 days

Declare @SoftwarePkgDeploymentsReportNeededDays as integer Set @SoftwarePkgDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AdvertisementName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Left(Ds.SoftwareName, CharIndex('(',(Ds.SoftwareName))-1)as 'ApplicationName', Ds.ProgramName 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays' from v_DeploymentSummary Ds join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID Where Ds.FeatureType = 2 and Ds.DeploymentIntent = 1 and Ds.ModificationTime > GETDATE()-@SoftwarePkgDeploymentsReportNeededDays order by Ds.DeploymentTime desc

45. All Software packages available deployments status within 30 days

Declare @SoftwarePkgDeploymentsReportNeededDays as integer Set @SoftwarePkgDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AdvertisementName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Left(Ds.SoftwareName, CharIndex('(',(Ds.SoftwareName))-1)as 'ApplicationName', Ds.ProgramName 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays'

Internal Use Only

from v_DeploymentSummary Ds join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID Where Ds.FeatureType = 2 and Ds.DeploymentIntent = 2 and Ds.ModificationTime > GETDATE()-@SoftwarePkgDeploymentsReportNeededDays order by Ds.DeploymentTime desc

46. All Software updates required deployments status within 30 days

Declare @SoftwareUpdateDeploymentsReportNeededDays as integer Set @SoftwareUpdateDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AssignmentName as 'DeploymentName', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'Others', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberSuccess = 0) or (Ds.NumberSuccess is null) Then '0' Else (round(Ds.NumberSuccess/ convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays' from v_DeploymentSummary Ds left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 5 and Ds.DeploymentIntent = 1 and Vaa.LastModificationTime > GETDATE()-@SoftwareUpdateDeploymentsReportNeededDays order by Ds.DeploymentTime desc

47. All Software updates available deployments status within 30 days

Declare @SoftwareUpdateDeploymentsReportNeededDays as integer Set @SoftwareUpdateDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AssignmentName as 'DeploymentName', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'Others', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberSuccess = 0) or (Ds.NumberSuccess is null) Then '0' Else (round(Ds.NumberSuccess/ convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays' from v_DeploymentSummary Ds left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 5 and Ds.DeploymentIntent = 2 and Vaa.LastModificationTime > GETDATE()-@SoftwareUpdateDeploymentsReportNeededDays order by Ds.DeploymentTime desc

Internal Use Only

48. All OSD required deployments status within 30 days

Declare @SoftwareOSDeploymentsReportNeededDays as integer Set @SoftwareOSDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AdvertisementName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Ds.SoftwareName as 'TaskSequenceName', Ds.ProgramName as 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays' from v_DeploymentSummary Ds join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID Where Ds.FeatureType = 7 and Ds.DeploymentIntent = 1 and Ds.ModificationTime > GETDATE()-@SoftwareOSDeploymentsReportNeededDays order by Ds.DeploymentTime desc

49. All OSD available deployments status within 30 days

Declare @SoftwareOSDeploymentsReportNeededDays as integer Set @SoftwareOSDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AdvertisementName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Ds.SoftwareName as 'TaskSequenceName', Ds.ProgramName as 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays' from v_DeploymentSummary Ds join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID Where Ds.FeatureType = 7 and Ds.DeploymentIntent = 2 and Ds.ModificationTime > GETDATE()-@SoftwareOSDeploymentsReportNeededDays order by Ds.DeploymentTime desc

Internal Use Only

50. All ConfigMgr Roles Detailed status

SELECT srl.ServerName, srl.SiteCode, vs.SiteName, vrs.AD_Site_Name0 as ADSite, vs.ReportingSiteCode as Parent, vs.Installdir, MAX(CASE srl.rolename WHEN 'SMS Site System' THEN 'Yes' Else ' ' END) as SiteSys, MAX(CASE srl.rolename WHEN 'SMS Component Server' THEN 'Yes' Else ' ' END) as CompSer, MAX(CASE srl.rolename WHEN 'SMS Site Server' THEN 'Yes' Else ' ' END) as SiteSer, MAX(CASE srl.rolename WHEN 'SMS Management Point' THEN 'Yes' Else ' ' END) as MP, MAX(CASE srl.rolename WHEN 'SMS Distribution Point' THEN 'Yes' Else ' ' END) as DP, MAX(CASE srl.rolename WHEN 'SMS SQL Server' THEN 'Yes' Else ' ' END) as 'SQL', MAX(CASE srl.rolename WHEN 'SMS Software Update Point' THEN 'Yes' Else ' ' END) as SUP, MAX(CASE srl.rolename WHEN 'SMS SRS Reporting Point' THEN 'Yes' Else ' ' END) as SSRS, MAX(CASE srl.RoleName WHEN 'SMS Reporting Point' THEN 'Yes' Else ' ' END) as RPT, MAX(CASE srl.rolename WHEN 'SMS Fallback Status Point' THEN 'Yes' Else ' ' END) as FSP, MAX(CASE srl.rolename WHEN 'SMS ServerName Locator Point' THEN 'Yes' Else ' ' END) as SLP, MAX(CASE srl.rolename WHEN 'SMS PXE Service Point' THEN 'Yes' Else ' ' END) as PXE, MAX(CASE srl.rolename WHEN 'AI Update Service Point' THEN 'Yes' Else ' ' END) as AssI, MAX(CASE srl.rolename WHEN 'SMS State Migration Point' THEN 'Yes' Else ' ' END) as SMP, MAX(CASE srl.rolename WHEN 'SMS System Health Validator' THEN 'Yes' Else ' ' END) as SysVal, MAX(CASE srl.rolename WHEN 'SMS Notification Server' THEN 'Yes' Else ' ' END) as NotiSer, MAX(CASE srl.rolename WHEN 'SMS Provider' THEN 'Yes' Else ' ' END) as SMSPro, MAX(CASE srl.rolename WHEN 'SMS Application Web Service' THEN 'Yes' Else ' ' END) as WebSer, MAX(CASE srl.rolename WHEN 'SMS Portal Web Site' THEN 'Yes' Else ' ' END) as WebSite, MAX(CASE srl.rolename WHEN 'SMS Branch distribution point' THEN 'Yes' Else ' ' END) as BranDP FROM v_SystemResourceList as srl LEFT JOIN v_site vs on srl.ServerName = vs.ServerName LEFT JOIN v_R_System_Valid vrs on LEFT(srl.ServerName, CHARINDEX('.', srl.ServerName) - 1) = vrs.Netbios_Name0 GROUP BY srl.ServerName, srl.SiteCode, vs.SiteName, vs.ReportingSiteCode, vrs.AD_Site_Name0, vs.InstallDir ORDER BY srl.sitecode,srl.ServerName

51. All SCCM Server Software Update Sync status

SELECT US.SiteCode, S.ServerName, S.SiteName, US.ContentVersion, US.SyncTime, US.LastSyncState, US.LastSyncStateTime, US.LastErrorCode FROM update_syncstatus US, v_Site S where US.SiteCode=S.SiteCode ORDER BY SyncTime

52. All Software applications required deployments status within 5 days

Declare @CurrentDeploymentsReportNeededDays as integer Set @CurrentDeploymentsReportNeededDays = 5 --Specify the Days

Select CONVERT(VARCHAR(11),GETDATE(),106) as 'Date', Right(Ds.CollectionName,3) as 'Stage', Vaa.ApplicationName as 'ApplicationName',

Internal Use Only

CASE when Vaa.DesiredConfigType = 1 Then 'Install' when vaa.DesiredConfigType = 2 Then 'Uninstall' Else 'Others' End as 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'ReqDays' from v_DeploymentSummary Ds left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 1 and Ds.DeploymentIntent = 1 and DateDiff(D,Ds.EnforcementDeadline, GetDate()) between 0 and @CurrentDeploymentsReportNeededDays and Ds.NumberTotal > 0 order by Ds.EnforcementDeadline desc

53. All Software packages required deployments status within 5 days

Declare @CurrentDeploymentsReportNeededDays as integer Set @CurrentDeploymentsReportNeededDays = 5 --Specify the Days

Select CONVERT(VARCHAR(11),GETDATE(),106) as 'Date', Right(Ds.CollectionName,3) as 'Stage', Left(Ds.SoftwareName, CharIndex('(',(Ds.SoftwareName))-1)as 'ApplicationName', Ds.ProgramName 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailDays' from v_DeploymentSummary Ds join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID Where Ds.FeatureType = 2 and Ds.DeploymentIntent = 1 and DateDiff(D,Ds.DeploymentTime, GetDate()) between 0 and @CurrentDeploymentsReportNeededDays and Ds.NumberTotal > 0 order by Ds.DeploymentTime desc

54. All Software updates required deployments status within 5 days

Declare @CurrentDeploymentsReportNeededDays as integer Set @CurrentDeploymentsReportNeededDays = 5 --Specify the Days

Select CONVERT(VARCHAR(11),GETDATE(),106) as 'Date', Vaa.AssignmentName as 'DeploymentName',

Internal Use Only

Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'Others', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberSuccess = 0) or (Ds.NumberSuccess is null) Then '0' Else (round(Ds.NumberSuccess/ convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'ReqDays' from v_DeploymentSummary Ds left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 5 and Ds.DeploymentIntent = 1 and DateDiff(D,Ds.EnforcementDeadline, GetDate()) between 0 and @CurrentDeploymentsReportNeededDays and Ds.NumberTotal > 0 order by Ds.EnforcementDeadline desc

55. All Software applications deployments status within 30 days

Declare @AppDeploymentsReportNeededDays as integer Set @AppDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AssignmentName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Vaa.ApplicationName as 'ApplicationName', CASE when Vaa.DesiredConfigType = 1 Then 'Install' when vaa.DesiredConfigType = 2 Then 'Uninstall' Else 'Others' End as 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' when Ds.DeploymentIntent = 3 Then 'Simulate' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays', DateDiff(D,Ds.CreationTime, GetDate()) as 'CreatedDays', Vaa.CreationTime as 'CreationTime', Vaa.LastModificationTime as 'LastModifiedTime', Vaa.LastModifiedBy as 'LastModifiedBy' from v_DeploymentSummary Ds left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 1 and Ds.CreationTime > GETDATE()-@AppDeploymentsReportNeededDays order by Ds.DeploymentTime desc

56. All Software packages deployments status within 30 days

Declare @PKGDeploymentsReportNeededDays as integer Set @PKGDeploymentsReportNeededDays = 30 --Specify the Days

Internal Use Only

Select Vaa.AdvertisementName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Left(Ds.SoftwareName, CharIndex('(',(Ds.SoftwareName))-1)as 'ApplicationName', Ds.ProgramName 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays', DateDiff(D,Ds.ModificationTime, GetDate()) as 'CreatedDays', Ds.CreationTime as 'CreationTime', Ds.ModificationTime as 'LastModifiedTime', 'Administrator' as 'LastModifiedBy' from v_DeploymentSummary Ds join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID Where Ds.FeatureType = 2 and Ds.ModificationTime > GETDATE()-@PKGDeploymentsReportNeededDays order by Ds.DeploymentTime desc

57. All Software updates deployments status within 30 days

Declare @PatchDeploymentsReportNeededDays as integer Set @PatchDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AssignmentName as 'DeploymentName', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'Others', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberSuccess = 0) or (Ds.NumberSuccess is null) Then '0' Else (round(Ds.NumberSuccess/ convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays', DateDiff(D,Ds.CreationTime, GetDate()) as 'CreatedDays', Vaa.CreationTime as 'CreationTime', Vaa.LastModificationTime as 'LastModifiedTime', Vaa.LastModifiedBy as 'LastModifiedBy' from v_DeploymentSummary Ds left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 5 and Vaa.LastModificationTime > GETDATE()-@PatchDeploymentsReportNeededDays order by Ds.DeploymentTime desc

Internal Use Only

58. All OS deployments status within 30 days

Declare @OSDeploymentsReportNeededDays as integer Set @OSDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AdvertisementName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Ds.SoftwareName as 'TaskSequenceName', Ds.ProgramName 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays', DateDiff(D,Ds.ModificationTime, GetDate()) as 'CreatedDays', Ds.CreationTime as 'CreationTime', Ds.ModificationTime as 'LastModifiedTime', 'Administrator' as 'LastModifiedBy' from v_DeploymentSummary Ds join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID Where Ds.FeatureType = 7 and Ds.ModificationTime > GETDATE()-@OSDeploymentsReportNeededDays order by Ds.DeploymentTime desc

59. All Software updates deployments status within 30 days

Declare @PatchDeploymentsReportNeededDays as integer Set @PatchDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AssignmentName as 'DeploymentName', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'Others', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberSuccess = 0) or (Ds.NumberSuccess is null) Then '0' Else (round(Ds.NumberSuccess/ convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays', DateDiff(D,Ds.CreationTime, GetDate()) as 'CreatedDays', Vaa.CreationTime as 'CreationTime', Vaa.LastModificationTime as 'LastModifiedTime', Vaa.LastModifiedBy as 'LastModifiedBy' from v_DeploymentSummary Ds

Internal Use Only

left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 5 and Vaa.LastModificationTime > GETDATE()-@PatchDeploymentsReportNeededDays order by Ds.DeploymentTime desc

60. All OS deployments status within 30 days

Declare @OSDeploymentsReportNeededDays as integer Set @OSDeploymentsReportNeededDays = 30 --Specify the Days

Select Vaa.AdvertisementName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Ds.SoftwareName as 'TaskSequenceName', Ds.ProgramName 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays', DateDiff(D,Ds.ModificationTime, GetDate()) as 'CreatedDays', Ds.CreationTime as 'CreationTime', Ds.ModificationTime as 'LastModifiedTime', 'Administrator' as 'LastModifiedBy' from v_DeploymentSummary Ds join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID Where Ds.FeatureType = 7 and Ds.ModificationTime > GETDATE()-@OSDeploymentsReportNeededDays order by Ds.DeploymentTime desc

61. All Site Servers Issue MP components status

SELECT distinct SiteCode , MachineName 'ServerName', ComponentName , Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and ComponentName ='SMS_MP_CONTROL_MANAGER' and v_ComponentSummarizer.Status = 2 Order By SiteCode

62. All Site Servers Issue DP components status

SELECT distinct SiteCode ,

Internal Use Only

MachineName 'ServerName', ComponentName , Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and ComponentName ='SMS_DISTRIBUTION_MANAGER' and v_ComponentSummarizer.Status = 2 Order By SiteCode

63. All Site Servers Issue DDR components status

SELECT distinct SiteCode , MachineName 'ServerName', ComponentName , Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and ComponentName ='SMS_DISCOVERY_DATA_MANAGER' and v_ComponentSummarizer.Status = 2 Order By SiteCode

64. All Site Servers Issue CCR components status

SELECT distinct SiteCode , MachineName 'ServerName', ComponentName , Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and ComponentName ='SMS_CLIENT_CONFIG_MANAGER' and v_ComponentSummarizer.Status = 2 Order By SiteCode

65. All Site Servers Issue WSUS components status

SELECT distinct SiteCode , MachineName 'ServerName', ComponentName ,

Internal Use Only

Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and (ComponentName ='SMS_WSUS_CONFIGURATION_MANAGER' or ComponentName ='SMS_WSUS_SYNC_MANAGER') and v_ComponentSummarizer.Status = 2 Order By SiteCode

66. All Site Servers Issue Discovery components status

SELECT distinct SiteCode , MachineName 'ServerName', ComponentName , Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and (ComponentName ='SMS_AD_SYSTEM_GROUP_DISCOVERY_AGENT' or ComponentName ='SMS_AD_SYSTEM_DISCOVERY_AGENT' or ComponentName ='SMS_NETWORK_DISCOVERY' or ComponentName ='SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT') and v_ComponentSummarizer.Status = 2 Order By SiteCode

67. All Site Servers Issue Collection Evaluator components status

SELECT distinct SiteCode , MachineName 'ServerName', ComponentName , Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and ComponentName ='SMS_COLLECTION_EVALUATOR' and v_ComponentSummarizer.Status = 2 Order By SiteCode

68. All Site Servers Issue Hardware Inventory components status

SELECT distinct SiteCode , MachineName 'ServerName', ComponentName ,

Internal Use Only

Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and ComponentName ='SMS_INVENTORY_DATA_LOADER' and v_ComponentSummarizer.Status = 2 Order By SiteCode

69. All Site Servers Issue Despooler components status

SELECT distinct SiteCode , MachineName 'ServerName', ComponentName , Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and ComponentName ='SMS_DESPOOLER' and v_ComponentSummarizer.Status = 2 Order By SiteCode

70. All Site Servers Issue Inbox Monitor components status

SELECT distinct SiteCode , MachineName 'ServerName', ComponentName , Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and ComponentName ='SMS_INBOX_MONITOR' and v_ComponentSummarizer.Status = 2 Order By SiteCode

71. All Site Servers Issue Component Monitor components status

SELECT distinct SiteCode, MachineName 'ServerName', ComponentName ,

Internal Use Only

Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and ComponentName ='SMS_COMPONENT_MONITOR' and v_ComponentSummarizer.Status = 2 Order By SiteCode

72. All Site Servers Issue Others components status

SELECT distinct SiteCode , MachineName 'ServerName', ComponentName , Case v_componentSummarizer.State When 0 Then 'Stopped' When 1 Then 'Started' When 2 Then 'Paused' When 3 Then 'Installing' When 4 Then 'Re-Installing' When 5 Then 'De-Installing' Else ' ' END AS 'Thread State', Errors, Warnings, Infos, Case v_componentSummarizer.Type When 0 Then 'Autostarting' When 1 Then 'Scheduled' When 2 Then 'Manual' ELSE ' ' END AS 'StartupType', CASE AvailabilityState When 0 Then 'Online' When 3 Then 'Offline' ELSE ' ' END AS 'State', Case v_ComponentSummarizer.Status When 0 Then 'OK' When 1 Then 'Warning' When 2 Then 'Critical' Else ' ' End As 'Status' from v_ComponentSummarizer Where TallyInterval = '0001128000100008' and ComponentName Not in ('SMS_MP_CONTROL_MANAGER','SMS_DISTRIBUTION_MANAGER','SMS_DISCOVERY_DATA_MANAGER', 'SMS_CLIENT_CONFIG_MANAGER','SMS_WSUS_CONFIGURATION_MANAGER','SMS_WSUS_SYNC_MANAGER', 'SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT','SMS_AD_SYSTEM_GROUP_DISCOVERY_AGENT', 'SMS_AD_SYSTEM_DISCOVERY_AGENT','SMS_NETWORK_DISCOVERY','SMS_COLLECTION_EVALUATOR', 'SMS_INVENTORY_DATA_LOADER','SMS_DESPOOLER','SMS_INBOX_MONITOR','SMS_COMPONENT_MONITOR' ) and v_ComponentSummarizer.Status = 2 Order By SiteCode

73. All Workstations Not Assigned Clients detailed status

Declare @SCCMManagedWorkstationsScopeCollectionID as varchar(8) Set @SCCMManagedWorkstationsScopeCollectionID = 'SMS00001' -- specify scope collection ID

Select Distinct (VRS.Netbios_Name0) as 'Name', Case when VRS.Client0 = 1 Then 'Yes' Else 'No' End 'Client', Case when VRS.Active0 = 1 Then 'Yes' Else 'No' End 'Active', Case when v_CH_ClientSummary.ClientActiveStatus = 1 Then 'Yes' Else 'No' End 'ClientHealthActive', v_CH_ClientSummary.ClientStateDescription as 'ClientHealthDescription', System_Disc.AD_Site_Name0 as 'ADSiteName', Vrs.Operating_System_Name_and0 as 'OSType', v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 as 'AssignedSite', v_CH_ClientSummary.LastActiveTime as 'LastCommunicated', DateDiff(D, v_CH_ClientSummary.LastActiveTime, GetDate()) 'LastCommunicatedDays', Vrs.Creation_Date0 as 'ClientCreation', DateDiff(D, Vrs.Creation_Date0, GetDate()) 'ClientCreationDays', System_Disc.Last_Logon_Timestamp0 as 'LastLogon', DateDiff(D, System_Disc.Last_Logon_Timestamp0, GetDate()) 'LastLogonDays', System_Disc.Distinguished_Name0 as 'OUName'

Internal Use Only

from V_R_System Vrs Left Outer join v_RA_System_SMSAssignedSites on v_RA_System_SMSAssignedSites.ResourceID = VRS.ResourceId Left Outer join v_Gs_Operating_System on v_Gs_Operating_System .ResourceID = VRS.ResourceId left outer join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId left outer join v_CH_ClientSummary on v_CH_ClientSummary.ResourceID = VRS.ResourceId left outer join System_Disc on System_Disc.ItemKey = VRS.ResourceId where (Vrs.Client0 = 0 or Vrs.Client0 is null ) and (VRS.Obsolete0 = 0 or VRS.Obsolete0 is null) and v_FullCollectionMembership.CollectionID = @SCCMManagedWorkstationsScopeCollectionID and v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 is null and Vrs.Operating_System_Name_and0 like '%Workstation%' order by System_Disc.Last_Logon_Timestamp0 desc

74. All Workstations Unhealthy Clients detailed status

Declare @SCCMManagedWorkstationsScopeCollectionID as varchar(8) Set @SCCMManagedWorkstationsScopeCollectionID = 'SMS00001' -- specify scope collection ID

Select Distinct (VRS.Netbios_Name0) as 'Name', Case when VRS.Client0 = 1 Then 'Yes' Else 'No' End 'Client', Case when VRS.Active0 = 1 Then 'Yes' Else 'No' End 'Active', Case when v_CH_ClientSummary.ClientActiveStatus = 1 Then 'Yes' Else 'No' End 'ClientHealthActive', v_CH_ClientSummary.ClientStateDescription as 'ClientHealthDescription', System_Disc.AD_Site_Name0 as 'ADSiteName', Vrs.Operating_System_Name_and0 as 'OSType', v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 as 'AssignedSite', v_CH_ClientSummary.LastActiveTime as 'LastCommunicated', DateDiff(D, v_CH_ClientSummary.LastActiveTime, GetDate()) 'LastCommunicatedDays', Vrs.Creation_Date0 as 'ClientCreation', DateDiff(D, Vrs.Creation_Date0, GetDate()) 'ClientCreationDays', System_Disc.Last_Logon_Timestamp0 as 'LastLogon', DateDiff(D, System_Disc.Last_Logon_Timestamp0, GetDate()) 'LastLogonDays', System_Disc.Distinguished_Name0 as 'OUName' from V_R_System Vrs Left Outer join v_RA_System_SMSAssignedSites on v_RA_System_SMSAssignedSites.ResourceID = VRS.ResourceId Left Outer join v_Gs_Operating_System on v_Gs_Operating_System .ResourceID = VRS.ResourceId left outer join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId left outer join v_CH_ClientSummary on v_CH_ClientSummary.ResourceID = VRS.ResourceId left outer join System_Disc on System_Disc.ItemKey = VRS.ResourceId where (Vrs.Client0 = 0 or Vrs.Client0 is null ) and (VRS.Obsolete0 = 0 or VRS.Obsolete0 is null) and v_FullCollectionMembership.CollectionID = @SCCMManagedWorkstationsScopeCollectionID and v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 is not null and Vrs.Operating_System_Name_and0 like '%Workstation%' order by System_Disc.Last_Logon_Timestamp0 desc 

75. All Workstations Inactive Clients detailed status

Declare @SCCMManagedWorkstationsScopeCollectionID as varchar(8) Set @SCCMManagedWorkstationsScopeCollectionID = 'SMS00001' -- specify scope collection ID

Select Distinct (VRS.Netbios_Name0) as 'Name', Case when VRS.Client0 = 1 Then 'Yes' Else 'No' End 'Client', Case when VRS.Active0 = 1 Then 'Yes' Else 'No' End 'Active', Case when v_CH_ClientSummary.ClientActiveStatus = 1 Then 'Yes' Else 'No' End 'ClientHealthActive', v_CH_ClientSummary.ClientStateDescription as 'ClientHealthDescription', System_Disc.AD_Site_Name0 as 'ADSiteName', Vrs.Operating_System_Name_and0 as 'OSType',

Internal Use Only

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 as 'AssignedSite', v_CH_ClientSummary.LastActiveTime as 'LastCommunicated', DateDiff(D, v_CH_ClientSummary.LastActiveTime, GetDate()) 'LastCommunicatedDays', Vrs.Creation_Date0 as 'ClientCreation', DateDiff(D, Vrs.Creation_Date0, GetDate()) 'ClientCreationDays', System_Disc.Last_Logon_Timestamp0 as 'LastLogon', DateDiff(D, System_Disc.Last_Logon_Timestamp0, GetDate()) 'LastLogonDays', System_Disc.Distinguished_Name0 as 'OUName' from V_R_System Vrs Left Outer join v_RA_System_SMSAssignedSites on v_RA_System_SMSAssignedSites.ResourceID = VRS.ResourceId Left Outer join v_Gs_Operating_System on v_Gs_Operating_System .ResourceID = VRS.ResourceId left outer join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId left outer join v_CH_ClientSummary on v_CH_ClientSummary.ResourceID = VRS.ResourceId left outer join System_Disc on System_Disc.ItemKey = VRS.ResourceId where (Vrs.Client0 = 1 and v_CH_ClientSummary.ClientActiveStatus <> 1 ) and (VRS.Obsolete0 = 0 or VRS.Obsolete0 is null) and v_FullCollectionMembership.CollectionID = @SCCMManagedWorkstationsScopeCollectionID and Vrs.Operating_System_Name_and0 like '%Workstation%' order by System_Disc.Last_Logon_Timestamp0 desc

76. All Obsolete Clients detailed status

Select ResourceID as 'ResourceID', Name0 as 'MachineName', User_Name0 as 'LogonUserName', Case When Client0 = 1 Then 'Yes' Else 'No' End as 'Client', Case When Obsolete0 = 1 Then 'Yes' Else 'No' End as 'Obsolete', Case When Active0 = 1 Then 'Yes' Else 'No' End as 'Active', AD_Site_Name0 as 'ADSiteName', Operating_System_Name_and0 as 'OSType', Client_Version0 as 'ClientVersion', Creation_Date0 as 'CreationDateinSCCM' from v_R_system where (Obsolete0 =1 and Active0 = 0) and Name0 not like '%Unknown%'

77. All Packages available in SCCM

SELECT DISTINCT p.PackageID, p.Name,p.Version,p.Language,p.Manufacturer, PackageType = Case Packagetype When 0 Then 'Software Distribution Package' When 3 Then 'Driver Package' When 4 Then 'Task Sequence Package' When 5 Then 'Software Update Package' When 6 Then 'Device Settings Package' When 7 Then 'Virtual Package' When 8 Then 'Software Distribution Application' When 257 Then 'Image Package' When 258 Then 'Boot Image Package' When 259 Then 'OS Install Package' End, p.PkgSourcePath,p.SourceDate,p.LastRefreshTime ,p.SourceVersion, p.SourceSite,n.Targeted,n.Installed,n.Retrying,n.Failed,(n.SourceSize/1024)as 'SourceSize(MB)', (n.SourceCompressedSize/1024) as 'SourceCompressedSize(MB)' FROM v_Package p LEFT JOIN v_PackageStatusRootSummarizer n ON p.PackageID = n.PackageID WHERE p.PackageType <> '4' and p.PackageType <> '258' order by 1 desc

Internal Use Only

78. All Collections available in SCCM

SELECT dbo.v_Collection.CollectionID as 'CollectionID', dbo.v_Collections_G.LimitToCollectionID as 'LimitToCollectionID', dbo.v_Collection.Name as 'CollectionName', dbo.Collections.LimitToCollectionName as 'LimitingCollectionName', Case when dbo.v_Collections_G.CollectionType = 1 Then 'User' when dbo.v_Collections_G.CollectionType = 2 Then 'Device' Else 'Others' End as 'CollectionType', dbo.v_Collection.MemberCount as 'MembersCount', dbo.v_Collections_G.CollectionComment as 'CollectionComment', dbo.Collections.BeginDate as 'CreatedDate', dbo.Collections.LastMemberChangeTime as 'LastMemberChangeDate', dbo.v_CollectionRuleQuery.RuleName as 'CollectionRuleName', dbo.v_CollectionRuleQuery.QueryID as 'QueryID', dbo.v_CollectionRuleQuery.QueryExpression as 'QueryExpression' FROM dbo.v_Collection left join dbo.v_CollectionRuleQuery ON dbo.v_Collection.CollectionID = dbo.v_CollectionRuleQuery.CollectionID left join dbo.Collections on dbo.v_Collection.CollectionID = dbo.Collections.SiteID left join dbo.v_Collections_G on dbo.v_Collection.CollectionID = dbo.v_Collections_G.SiteID ORDER BY dbo.v_Collection.Name, dbo.v_Collections_G.CollectionType

79. All Managed Workstations details status

Declare @ProjectName as varchar(25) Set @ProjectName = 'LAB' -- specify Project Name

SELECT DISTINCT Vrs.resourceid as 'ResourceID', Vrs.Name0 as 'MachineName', 'SCCM' as 'SourceName', getdate() as 'ReportingDate', bios.SerialNumber0 as 'SerialNumber', cs.Manufacturer0 as 'Manufacturer', encl.PartNumber0 as 'ManufacturerPartNumber', cs.model0 as 'DeviceModel', case when p.caption0 is null then p.name0 else p.caption0 end as 'ProcessorType', (mem.TotalPhysicalMemory0 / 1024/1000) as 'MemorySize(GB)', disk.Size0 / 1024 as 'HDDSize', bios.Version0 as 'BIOSVersion', @ProjectName as 'AccountCode', encl.chassistypes0 as 'EquipmentType', os.caption0 as 'OperatingSystem', os.csdversion0 as 'ServicePackVersion', os.InstallDate0 as 'InstallationDate', Vrs.Creation_Date0 as 'CreationDateinSCCM' FROM v_r_system Vrs LEFT OUTER JOIN v_gs_pc_bios bios on Vrs.resourceid = bios.resourceid LEFT OUTER JOIN v_gs_computer_system cs on Vrs.resourceid = cs.resourceid LEFT OUTER JOIN v_gs_system_enclosure encl on Vrs.resourceid = encl.resourceid LEFT OUTER JOIN v_gs_processor p on Vrs.resourceid = p.resourceid LEFT OUTER JOIN v_gs_x86_pc_memory mem on Vrs.resourceid = mem.resourceid LEFT OUTER JOIN v_gs_logical_disk disk on Vrs.resourceid = disk.resourceid LEFT OUTER JOIN v_gs_operating_system os on Vrs.resourceid = os.resourceid WHERE disk.name0 = 'C:' AND encl.tag0 = 'System Enclosure 0' AND os.caption0 not like '%server%' AND Vrs.Active0 = 1 AND Vrs.Client0 = 1 AND Vrs.Obsolete0 = 0

Internal Use Only

80. All Workstations Assets Inventory details status
Declare @CollectionID as varchar(8) Declare @ProjectName as varchar(25)

Set @CollectionID = 'SMS00001' -- specify scope collection ID Set @ProjectName = 'LAB' -- specify Project Name

Select Distinct (VRS.Netbios_Name0) as 'Name', Case when VRS.Client0 = 1 Then 'Yes' Else 'No' End 'Client', Case when VRS.Active0 = 1 Then 'Yes' Else 'No' End 'Active', Case when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 1 Then 'VMWare' when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN('3','4')Then 'Desktop' when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN('8','9','10','11','12','14') Then 'Laptop' when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 Then 'Mini Tower' when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 7 Then 'Tower' when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 13 Then 'All in One' when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 Then 'Space-Saving' when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 17 Then 'Main System Chassis' when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 21 Then 'Peripheral Chassis' when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 22 Then 'Storage Chassis' when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 23 Then 'Rack Mount Chassis' when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 24 Then 'Sealed-Case PC' Else 'Others' End 'CaseType', LEFT(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0), ISNULL(NULLIF(CHARINDEX(',',MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0)) - 1, - 1),LEN(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0))))as 'IPAddress', MAX (v_GS_NETWORK_ADAPTER_CONFIGUR.MACAddress0) as 'MACAddress', v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 as 'AssignedSite', VRS.Client_Version0 as 'ClientVersion', VRS.Creation_Date0 as 'ClientCreationDate', VRS.AD_Site_Name0 as 'ADSiteName', dbo.v_GS_OPERATING_SYSTEM.InstallDate0 AS 'OSInstallDate', DateDiff(D, dbo.v_GS_OPERATING_SYSTEM.InstallDate0, GetDate()) 'OSInstallDateAge', Convert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100) as 'LastBootDate', DateDiff(D, Convert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100), GetDate()) as 'LastBootDateAge', PC_BIOS_DATA.SerialNumber00 as 'SerialNumber', v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 as 'AssetTag', PC_BIOS_DATA.ReleaseDate00 as 'ReleaseDate', PC_BIOS_DATA.Name00 as 'BiosName', PC_BIOS_DATA.SMBIOSBIOSVersion00 as 'BiosVersion', v_GS_PROCESSOR.Name0 as 'ProcessorName', case when Computer_System_DATA.Manufacturer00 like 'VMware%' Then 'VMWare' when Computer_System_DATA.Manufacturer00 like 'Gigabyte%' Then 'Gigabyte' when Computer_System_DATA.Manufacturer00 like 'VIA Technologies%' Then 'VIA Technologies' when Computer_System_DATA.Manufacturer00 like 'MICRO-STAR%' Then 'MICRO-STAR' Else Computer_System_DATA.Manufacturer00 End 'Manufacturer', Computer_System_DATA.Model00 as 'Model', Computer_System_DATA.SystemType00 as 'OSType', v_GS_COMPUTER_SYSTEM.Domain0 as 'DomainName', VRS.User_Domain0+'\'+ VRS.User_Name0 as 'UserName', v_R_User.Mail0 as 'EMailID', Case when v_GS_COMPUTER_SYSTEM.domainrole0 = 0 then 'Standalone Workstation' when v_GS_COMPUTER_SYSTEM.domainrole0 = 1 Then 'Member Workstation' when v_GS_COMPUTER_SYSTEM.domainrole0 = 2 Then 'Standalone Server' when v_GS_COMPUTER_SYSTEM.domainrole0 = 3 Then 'Member Server' when v_GS_COMPUTER_SYSTEM.domainrole0 = 4 Then 'Backup Domain Controller' when v_GS_COMPUTER_SYSTEM.domainrole0 = 5 Then 'Primary Domain Controller' End 'Role',

Internal Use Only

case when Operating_System_DATA.Caption00 = 'Microsoft(R) Windows(R) Server 2003, Enterprise Edition' Then 'Microsoft(R) Windows(R) Server 2003 Enterprise Edition' when Operating_System_DATA.Caption00 = 'Microsoft(R) Windows(R) Server 2003, Standard Edition' Then 'Microsoft(R) Windows(R) Server 2003 Standard Edition' when Operating_System_DATA.Caption00 = 'Microsoft(R) Windows(R) Server 2003, Web Edition' Then 'Microsoft(R) Windows(R) Server 2003 Web Edition' Else Operating_System_DATA.Caption00 End 'OSName', Operating_System_DATA.CSDVersion00 as 'ServicePack', Operating_System_DATA.Version00 as 'Version', ((v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024)/1000) as 'TotalRAMSize(GB)', max(v_GS_LOGICAL_DISK.Size0 / 1024) AS 'TotalHDDSize(GB)', v_GS_WORKSTATION_STATUS.LastHWScan as 'LastHWScan', DateDiff(D, v_GS_WORKSTATION_STATUS.LastHwScan, GetDate()) as 'LastHWScanAge', @ProjectName as 'AccountName' from V_R_System VRS Left Outer join PC_BIOS_DATA on PC_BIOS_DATA.MachineID = VRS.ResourceId Left Outer join Operating_System_DATA on Operating_System_DATA.MachineID = VRS.ResourceId Left Outer join v_GS_WORKSTATION_STATUS on v_GS_WORKSTATION_STATUS.ResourceID = VRS.ResourceId Left Outer join Computer_System_DATA on Computer_System_DATA.MachineID = VRS.ResourceId Left Outer join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID = VRS.ResourceId Left Outer join v_GS_PROCESSOR on v_GS_PROCESSOR.ResourceID = VRS.ResourceId Left Outer join v_GS_SYSTEM_ENCLOSURE on v_GS_SYSTEM_ENCLOSURE.ResourceID = VRS.ResourceId Left Outer join v_Gs_Operating_System on v_Gs_Operating_System .ResourceID = VRS.ResourceId Left Outer join v_RA_System_SMSAssignedSites on v_RA_System_SMSAssignedSites.ResourceID = VRS.ResourceId left outer join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = VRS.ResourceId left outer join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId left outer join v_GS_NETWORK_ADAPTER_CONFIGUR on v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = VRS.ResourceId left outer join v_GS_LOGICAL_DISK on v_GS_LOGICAL_DISK.ResourceID = Vrs.ResourceId AND v_GS_LOGICAL_DISK.DriveType0 = 3 Left Outer join v_R_User on VRS.User_Name0 = v_R_User.User_Name0 where VRS.Operating_System_Name_and0 like '%Workstation%' and (VRS.Obsolete0 = 0 or VRS.Obsolete0 is null) and VRS.Client0 = 1 and v_FullCollectionMembership.CollectionID = @CollectionID --and VRS.Netbios_Name0 = 'CLIENT01' GROUP BY VRS.Netbios_Name0,VRS.Client0,VRS.Active0,v_GS_SYSTEM_ENCLOSURE.ChassisTypes0, v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0,VRS.Client_Version0,Vrs.Creation_Date0, Vrs.AD_Site_Name0,v_Gs_Operating_System.InstallDate0,v_Gs_Operating_System.LastBootUpTime0, PC_BIOS_DATA.SerialNumber00,v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0,PC_BIOS_DATA.ReleaseDate00, PC_BIOS_DATA.Name00,PC_BIOS_DATA.SMBIOSBIOSVersion00,v_GS_PROCESSOR.Name0,Computer_System_DATA.Manufact urer00, Computer_System_DATA.Model00,Computer_System_DATA.SystemType00,v_GS_COMPUTER_SYSTEM.Domain0, Vrs.User_Domain0,Vrs.User_Name0,v_R_User.Mail0,v_GS_COMPUTER_SYSTEM.DomainRole0,Operating_System_DATA.C aption00,Operating_System_DATA.CSDVersion00, Operating_System_DATA.Version00, v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, v_GS_WORKSTATION_STATUS.LastHWScan order by VRS.Netbios_Name0

81. All PCs with Office 365 Installed Machines Report Based on Installed Software

Declare @Collection varchar(8) Set @Collection = 'SMS00001' -- specify scope collection ID

Select Distinct VRS.Name0 as 'MachineName', Os.Caption0 as 'OperatingSystem', St.SystemType00 as 'OSType', VRS.AD_Site_Name0 as 'ADSite', VRS.Full_Domain_Name0 as 'Domain', VRS.User_Name0 as 'UserName', v_R_User.Mail0 as 'EMailID',

Internal Use Only

App.ARPDisplayName0 as 'DisplayName', App.InstallDate0 as 'InstalledDate', App.ProductVersion0 as 'Version' from V_R_System VRS Left JOIN v_GS_INSTALLED_SOFTWARE App ON VRS.ResourceID = App.ResourceID LEFT JOIN Computer_System_DATA St on VRS.ResourceID = st.MachineID LEFT JOIN v_GS_OPERATING_SYSTEM Os on VRS.ResourceID = Os.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on VRS.ResourceID = HWSCAN.ResourceID LEFT Join v_FullCollectionMembership as Col on VRS.ResourceID = Col.ResourceID LEFT JOIN v_R_User on VRS.User_Name0 = v_R_User.User_Name0 Where App.ARPDisplayName0 like 'Microsoft Office 365%' and App.ProductVersion0 like '15.%' and VRS.Operating_System_Name_and0 like '%Workstation%' and Col.CollectionID = @Collection and VRS.Client0 = 1 and VRS.Obsolete0 = 0 order by VRS.Name0, App.ProductVersion0

82. All PCs without Office 365 Installed Machines Report Based on Installed Software 
 Declare @Collection varchar(8) Set @Collection = 'SMS00001' -- specify scope collection ID

Select distinct(vs.Name0) as 'MachineName', Os.Caption0 as 'OperatingSystem', St.SystemType00 as 'OSType', Vs.AD_Site_Name0 as 'ADSite', vs.Full_Domain_Name0 as 'Domain', vs.User_Name0 as 'UserName', v_R_User.Mail0 as 'EMailID', HWSCAN.LastHWScan as 'LastHWScan', DateDiff(D, HWSCAN.LastHwScan, GetDate()) as 'LastHWScanAge' from v_R_System vs LEFT JOIN v_GS_SYSTEM_ENCLOSURE on v_GS_SYSTEM_ENCLOSURE.ResourceID = vs.ResourceID LEFT JOIN v_GS_OPERATING_SYSTEM Os on Vs.ResourceID = Os.ResourceID LEFT JOIN v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = vs.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on vs.ResourceID = HWSCAN.ResourceID LEFT JOIN Computer_System_DATA St on vs.ResourceID = st.MachineID LEFT JOIN v_R_User on vs.User_Name0 = v_R_User.User_Name0 LEFT JOIN v_GS_INSTALLED_SOFTWARE App ON vs.ResourceID = App.ResourceID where Vs.Operating_System_Name_and0 like '%Workstation%' and v_FullCollectionMembership.CollectionID = @Collection and Vs.Client0 = 1 and Vs.Obsolete0 = 0 and vs.ResourceID Not in ( Select Vrs.ResourceID from V_R_System VRS Left JOIN v_GS_INSTALLED_SOFTWARE App ON VRS.ResourceID = App.ResourceID LEFT JOIN Computer_System_DATA St on VRS.ResourceID = st.MachineID LEFT JOIN v_GS_OPERATING_SYSTEM Os on VRS.ResourceID = Os.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on VRS.ResourceID = HWSCAN.ResourceID Where App.ARPDisplayName0 like 'Microsoft Office 365%' and App.ProductVersion0 like '15.%' )

83. All PCs with SEP Antivirus Installed Machines Report Based on Installed Software 
 Declare @Collection varchar(8) Set @Collection = 'SMS00001' -- specify scope collection ID

Select Distinct VRS.Name0 as 'MachineName', Os.Caption0 as 'OperatingSystem', St.SystemType00 as 'OSType', VRS.AD_Site_Name0 as 'ADSite', VRS.Full_Domain_Name0 as 'Domain',

Internal Use Only

VRS.User_Name0 as 'UserName', v_R_User.Mail0 as 'EMailID', App.ARPDisplayName0 as 'DisplayName', App.InstallDate0 as 'InstalledDate', App.ProductVersion0 as 'Version' from V_R_System VRS Left JOIN v_GS_INSTALLED_SOFTWARE App ON VRS.ResourceID = App.ResourceID LEFT JOIN Computer_System_DATA St on VRS.ResourceID = st.MachineID LEFT JOIN v_GS_OPERATING_SYSTEM Os on VRS.ResourceID = Os.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on VRS.ResourceID = HWSCAN.ResourceID LEFT Join v_FullCollectionMembership as Col on VRS.ResourceID = Col.ResourceID LEFT JOIN v_R_User on VRS.User_Name0 = v_R_User.User_Name0 Where App.ARPDisplayName0 like 'Symantec%Endpoint%Protection%' and VRS.Operating_System_Name_and0 like '%Workstation%' and Col.CollectionID = @Collection and VRS.Client0 = 1 and VRS.Obsolete0 = 0 order by VRS.Name0, App.ProductVersion0

84. All PCs without SEP Antivirus Installed Machines Report Based on Installed Software 
 Declare @Collection varchar(8) Set @Collection = 'SMS00001' -- specify scope collection ID

Select distinct(vs.Name0) as 'MachineName', Os.Caption0 as 'OperatingSystem', St.SystemType00 as 'OSType', Vs.AD_Site_Name0 as 'ADSite', vs.Full_Domain_Name0 as 'Domain', vs.User_Name0 as 'UserName', v_R_User.Mail0 as 'EMailID', HWSCAN.LastHWScan as 'LastHWScan', DateDiff(D, HWSCAN.LastHwScan, GetDate()) as 'LastHWScanAge' from v_R_System vs LEFT JOIN v_GS_SYSTEM_ENCLOSURE on v_GS_SYSTEM_ENCLOSURE.ResourceID = vs.ResourceID LEFT JOIN v_GS_OPERATING_SYSTEM Os on Vs.ResourceID = Os.ResourceID LEFT JOIN v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = vs.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on vs.ResourceID = HWSCAN.ResourceID LEFT JOIN Computer_System_DATA St on vs.ResourceID = st.MachineID LEFT JOIN v_R_User on vs.User_Name0 = v_R_User.User_Name0 LEFT JOIN v_GS_INSTALLED_SOFTWARE App ON vs.ResourceID = App.ResourceID where Vs.Operating_System_Name_and0 like '%Workstation%' and v_FullCollectionMembership.CollectionID = @Collection and Vs.Client0 = 1 and Vs.Obsolete0 = 0 and vs.ResourceID Not in ( Select Vrs.ResourceID from V_R_System VRS Left JOIN v_GS_INSTALLED_SOFTWARE App ON VRS.ResourceID = App.ResourceID LEFT JOIN Computer_System_DATA St on VRS.ResourceID = st.MachineID LEFT JOIN v_GS_OPERATING_SYSTEM Os on VRS.ResourceID = Os.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on VRS.ResourceID = HWSCAN.ResourceID Where App.ARPDisplayName0 like 'Symantec%Endpoint%Protection%' )

85. All Workstations Client Agent Detailed Report

Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- specify scope collection ID

select distinct(Name),Case when IsClient= 1 then 'Healthy' else 'UnHealthy' end as 'AgentStatus', (select case when count (v_GS_WORKSTATION_STATUS.ResourceID)=1 then 'Healthy' else 'UnHealthy' end from v_GS_WORKSTATION_STATUS where DATEDIFF (day,LastHWScan,GetDate())<30 and ResourceID=v_FullCollectionMembership.ResourceID)

Internal Use Only

as 'HWScanStatus', (select case when count (v_GS_LastSoftwareScan.ResourceID)=1 then 'Healthy' else 'UnHealthy' end from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<30 and ResourceID=v_FullCollectionMembership.ResourceID) as 'SWScanStatus', (select case when count (v_UpdateScanStatus.ResourceID)=1 then 'Healthy' else 'UnHealthy' end from v_UpdateScanStatus where DATEDIFF (day,LastScanTime,GetDate())<30 and LastErrorCode = 0 and ResourceID=v_FullCollectionMembership.ResourceID) as 'WSUSScanStatus', (select DATEDIFF (day,LastHWScan,GetDate()) from v_GS_WORKSTATION_STATUS where ResourceID=v_FullCollectionMembership.ResourceID) as 'LastHWScanDays', (select DATEDIFF (day,LastScanDate,GetDate()) from v_GS_LastSoftwareScan where ResourceID=v_FullCollectionMembership.ResourceID) as 'LastSWScanDays', (select DATEDIFF (day,LastScanTime,GetDate()) from v_UpdateScanStatus where LastErrorCode = 0 and ResourceID=v_FullCollectionMembership.ResourceID) as 'LastWSUSScanDays' from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like '%Workstation%') order by 2 desc

86. All Workstations Low Free Disk Space Report Declare @CollectionID as Varchar(8) 
 Declare @FreeSpace as Integer Set @CollectionID = 'SMS00001' -- specify scope collection ID Set @FreeSpace = '5000' -- specify MB Size

Select distinct (Vrs.Name0) as 'Machine', Vrs.AD_Site_Name0 as 'ADSiteName', Vrs.User_Name0 as 'UserName', USR.Mail0 as 'EMailID', Os.Caption00 as 'OSName', Csd.SystemType00 as 'OSType', LD.DeviceID00 as 'Drive', LD.FileSystem00 as 'FileSystem', LD.Size00 / 1024 as 'TotalSpace (GB)', LD.FreeSpace00 / 1024 as 'FreeSpace (GB)', Ws.LastHWScan as 'LastHWScan', DateDiff(D, Ws.LastHwScan, GetDate()) as 'LastHWScanAge' FROM v_R_System Vrs Join v_R_User USR on USR.User_Name0 = Vrs.User_Name0 Join v_FullCollectionMembership Fc on Fc.ResourceID = Vrs.ResourceID Join Operating_System_DATA Os on Os.MachineID = Vrs.ResourceID Join Computer_System_DATA Csd on Csd.MachineID = Vrs.ResourceID Join Logical_Disk_Data Ld on Ld.MachineID = Vrs.ResourceID Join v_GS_WORKSTATION_STATUS Ws on Ws.ResourceID = Vrs.ResourceId where CollectionID = @CollectionID and LD.Description00 = 'Local Fixed Disk' and LD.FreeSpace00 < @FreeSpace and Vrs.Operating_System_Name_and0 like '%Workstation%' Order By Vrs.Name0 asc

87. All Workstations Machines Names Last Logon with Serial No Report

Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- specify scope collection ID

Select

Internal Use Only

Distinct (VRS.Netbios_Name0) as 'Name', PC_BIOS_DATA.SerialNumber00 as 'SerialNumber', VRS.User_Domain0+'\'+ VRS.User_Name0 as 'UserName', v_R_User.Mail0 as 'EMailID' from V_R_System VRS Left Outer join PC_BIOS_DATA on PC_BIOS_DATA.MachineID = VRS.ResourceId Left Outer join Operating_System_DATA on Operating_System_DATA.MachineID = VRS.ResourceId Left Outer join v_Gs_Operating_System on v_Gs_Operating_System .ResourceID = VRS.ResourceId left outer join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId Left Outer join v_R_User on VRS.User_Name0 = v_R_User.User_Name0 where VRS.Operating_System_Name_and0 like '%Workstation%' and (VRS.Obsolete0 = 0 or VRS.Obsolete0 is null) and VRS.Client0 = 1 and v_FullCollectionMembership.CollectionID = @CollectionID GROUP BY VRS.Netbios_Name0, VRS.Client0, PC_BIOS_DATA.SerialNumber00, Vrs.User_Domain0, Vrs.User_Name0,v_R_User.Mail0 Order by VRS.Netbios_Name0

88. All Workstations with Adobe Acrobat Reader Installed Machines Report

  Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- specify scope collection ID

Select Distinct VRS.Name0 as 'Name', Os.Caption0 as 'OperatingSystem', St.SystemType00 as 'OSType', VRS.Full_Domain_Name0 as 'Domain', VRS.User_Name0 as 'UserName', VRS.AD_Site_Name0 as 'ADSite', ARP.Publisher0 as 'Publisher', ARP.DisplayName0 as 'DisplayName', ARP.ProdID0 as 'ProductID', ARP.InstallDate0 as 'InstalledDate', ARP.Version0 as 'Version' from V_R_System VRS LEFT JOIN v_GS_ADD_REMOVE_PROGRAMS ARP ON VRS.ResourceID = ARP.ResourceID LEFT JOIN Computer_System_DATA St on VRS.ResourceID = st.MachineID LEFT JOIN v_GS_OPERATING_SYSTEM Os on VRS.ResourceID = Os.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on VRS.ResourceID = HWSCAN.ResourceID LEFT JOIN v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId where ARP.Publisher0 like '%Adobe%' and ARP.DisplayName0 like '%Acrobat%Reader%' and VRS.Operating_System_Name_and0 like '%Workstation%' and v_FullCollectionMembership.CollectionID = @CollectionID and VRS.Obsolete0 = 0 Order by VRS.Name0

89. All Workstations with Adobe Acrobat Reader Last Usage Machines Report

Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- specify scope collection ID

Declare @days float Declare @__timezoneoffset int Select @__timezoneoffset = DateDiff (ss,GetUTCDate(),Getdate()) Select @days=DATEDIFF(day,IntervalStart,DATEADD(month,1,IntervalStart))from v_SummarizationInterval If IsNULL(@days,0) > 0 Select Distinct VRS.Name0 as 'Name', Os.Caption0 as 'OperatingSystem', St.SystemType00 as 'OSType',

Internal Use Only

Vrs.Full_Domain_Name0 as 'Domain', Vrs.User_Name0 as 'UserName', Vrs.AD_Site_Name0 as 'ADSite', ARP.Publisher0 as 'Publisher', ARP.DisplayName0 as 'DisplayName', ARP.ProdID0 as 'ProductID', ARP.InstallDate0 as 'InstalledDate', ARP.Version0 as 'Version', DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)) as 'LastUsage', DateDiff(D, DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)), GetDate()) as 'LastUsageDays' from V_R_System Vrs LEFT JOIN v_GS_ADD_REMOVE_PROGRAMS ARP ON VRS.ResourceID = ARP.ResourceID LEFT JOIN Computer_System_DATA St on VRS.ResourceID = st.MachineID LEFT JOIN v_GS_OPERATING_SYSTEM Os on VRS.ResourceID = Os.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on VRS.ResourceID = HWSCAN.ResourceID LEFT JOIN v_MonthlyUsageSummary mus on Vrs.ResourceID=mus.ResourceID LEFT JOIN v_MeteredFiles mf on mus.FileID=mf.MeteredFileID LEFT JOIN v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId Where ARP.Publisher0 like '%Adobe%' and ARP.DisplayName0 like '%Acrobat%Reader%' and VRS.Operating_System_Name_and0 like '%Workstation%' and v_FullCollectionMembership.CollectionID = @CollectionID and Vrs.Obsolete0 = 0 and Vrs.Client0 = 1 Group by Vrs.Name0,Os.Caption0,St.SystemType00,Vrs.Full_Domain_Name0,Vrs.User_Name0,Vrs.AD_Site_Name0,ARP.Publis her0,ARP.InstallDate0,ARP.DisplayName0,ARP.ProdID0,ARP.Version0 Having SUM (UsageCount) + SUM (TSUsageCount) > 0 Order by Vrs.Name0

90. All Workstations with Adobe Products Not Used More Than 90 Days Machines Report

Declare @RequirdDays as Integer Declare @days float Declare @__timezoneoffset int Select @__timezoneoffset = DateDiff (ss,GetUTCDate(),Getdate()) Select @days=DATEDIFF(day,IntervalStart,DATEADD(month,1,IntervalStart))from v_SummarizationInterval

Set @RequirdDays = 90 -- specify Days

If IsNULL(@days,0) > 0 Select Distinct VRS.Name0 as 'Name', Os.Caption0 as 'OperatingSystem', St.SystemType00 as 'OSType', Vrs.Full_Domain_Name0 as 'Domain', Vrs.User_Name0 as 'UserName', Vrs.AD_Site_Name0 as 'ADSite', ARP.Publisher0 as 'Publisher', ARP.DisplayName0 as 'DisplayName', ARP.ProdID0 as 'ProductID', ARP.InstallDate0 as 'InstalledDate', ARP.Version0 as 'Version', DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)) as 'LastUsage', DateDiff(D, DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)), GetDate()) as 'LastUsageDays' from V_R_System Vrs LEFT JOIN v_GS_ADD_REMOVE_PROGRAMS ARP ON VRS.ResourceID = ARP.ResourceID LEFT JOIN Computer_System_DATA St on VRS.ResourceID = st.MachineID LEFT JOIN v_GS_OPERATING_SYSTEM Os on VRS.ResourceID = Os.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on VRS.ResourceID = HWSCAN.ResourceID LEFT JOIN v_MonthlyUsageSummary mus on Vrs.ResourceID=mus.ResourceID LEFT JOIN v_MeteredFiles mf on mus.FileID=mf.MeteredFileID where

Internal Use Only

ARP.Publisher0 like '%Adobe%' and (DATEDIFF(d,LastUsage, GETDATE()) > @RequirdDays) and ( ARP.DisplayName0 like '%Adobe%Acrobat%Pro%' or ARP.DisplayName0 like '%Adobe%InDesign%' or ARP.DisplayName0 like '%Adobe%Illustrator%' or ARP.DisplayName0 like '%Adobe%Illustrator%' or ARP.DisplayName0 like '%Adobe%Photoshop%' ) and VRS.Operating_System_Name_and0 like '%Workstation%' and Vrs.Obsolete0=0 and Vrs.Client0=1 Group by Vrs.Name0,Os.Caption0,St.SystemType00,Vrs.Full_Domain_Name0,Vrs.User_Name0,Vrs.AD_Site_Name0,ARP.Publis her0,ARP.InstallDate0,ARP.DisplayName0,ARP.ProdID0,ARP.Version0 having SUM(UsageCount) + SUM(TSUsageCount) > 0 order by DateDiff(D, DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)), GetDate())

91. All Client and Inventory Health Report

Declare @CollectionID as Varchar(8) Declare @TotalMachines as Numeric(5) Declare @Healthy as Numeric(5) Declare @UnHealthy as Numeric(5) Declare @HWInventorySuccess as Numeric(5) Declare @HWInventoryNotRun as Numeric(5) Declare @SWInventorySuccess as Numeric(5) Declare @SWInventoryNotRun as Numeric(5) Declare @WSUSScanSuccess as Numeric(5) Declare @WSUSScanNotRun as Numeric(5)

Set @CollectionID = 'SMS00001' -- Specify Scope Collection ID

select @TotalMachines = (select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID)

select @Healthy = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete != 1 and IsClient = 1 )

select @UnHealthy = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID Not in (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete != 1 and IsClient = 1 ) )

select @HWInventorySuccess = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where (DATEDIFF (day,LastHWScan,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) ) select @HWInventoryNotRun = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

Internal Use Only

and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where (DATEDIFF (day,LastHWScan,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) ) )

select @SWInventorySuccess = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where (DATEDIFF (day,LastScanDate,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) ) select @SWInventoryNotRun = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where (DATEDIFF (day,LastScanDate,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) ) ) select @WSUSScanSuccess = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and (DATEDIFF (day,LastScanTime,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) )

select @WSUSScanNotRun = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and (DATEDIFF (day,LastScanTime,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) ) )

select @TotalMachines as 'TotalMachines', @Healthy as 'Healthy', @UnHealthy as 'UnHealthy', (Select(@Healthy/@TotalMachines)*100) as 'Healthy%', @HWInventorySuccess as 'HWInventorySuccess', @HWInventoryNotRun as 'HWInventoryNotRun', (Select(@HWInventorySuccess/@Healthy)*100) as 'HWInventorySuccess%', @SWInventorySuccess as 'SWInventorySuccess', @SWInventoryNotRun as 'SWInventoryNotRun', (Select(@SWInventorySuccess/@Healthy)*100) as 'SWInventorySuccess%', @WSUSScanSuccess as 'WSUSScanSuccess', @WSUSScanNotRun as 'WSUSScanNotRun', (Select(@WSUSScanSuccess/@Healthy)*100) as 'WSUSScanSuccess%'

92. All Total Scope machines details 
Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- Specify Scope Collection ID

Internal Use Only

select Name0 as 'MachineName' from v_R_system where V_R_System.ResourceID in (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID)

93. All Total Healthy machines details 
Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- Specify Scope Collection ID

select Name0 as 'MachineName' from v_R_system where V_R_System.ResourceID in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete != 1 and IsClient = 1)

94. All Total Unhealthy machines details 
Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- Specify Scope Collection ID

select Name0 as 'MachineName' from v_R_system where V_R_System.ResourceID in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID Not in (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsActive = 1 and IsObsolete != 1 and IsClient = 1))

95. All Total Hardware Inventory within 30 Days machines details 
Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- Specify Scope Collection ID

select Name0 as 'MachineName' from v_R_system where V_R_System.ResourceID in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where (DATEDIFF (day,LastHWScan,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) )

96. All Total Hardware Inventory not within 30 Days machines details
 Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- Specify Scope Collection ID

select Name0 as 'MachineName' from v_R_system where V_R_System.ResourceID in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where (DATEDIFF (day,LastHWScan,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) ))

97. All Total Software Inventory within 30 Days machines details
 Declare @CollectionID as Varchar(8)

Internal Use Only

Set @CollectionID = 'SMS00001' -- Specify Scope Collection ID

select Name0 as 'MachineName' from v_R_system where V_R_System.ResourceID in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where (DATEDIFF (day, LastScanDate,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) )

98. All Total Software Inventory not within 30 Days machines details  
Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- Specify Scope Collection ID

select Name0 as 'MachineName' from v_R_system where V_R_System.ResourceID in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where (DATEDIFF (day,LastScanDate,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) ))

99. All Total WSUS Scan within 30 Days machines details 
 Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- Specify Scope Collection ID

select Name0 as 'MachineName' from v_R_system where V_R_System.ResourceID in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and (DATEDIFF (day,LastScanTime,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) )

100. All Total WSUS Scan not within 30 Days machines details
 Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001' -- Specify Scope Collection ID

select Name0 as 'MachineName' from v_R_system where V_R_System.ResourceID in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1 and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and (DATEDIFF (day,LastScanTime,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in ('Heartbeat Discovery') and DATEDIFF (day,AgentTime,GetDate())<30)) ))

Internal Use Only

101. All Deployments status for Specific Application

Declare @ApplicationName as Varchar(255) Set @ApplicationName = 'Adobe DIO Illustrator Extension 3.0.13.2' --Specify Application Name

Select Vaa.AssignmentName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Vaa.ApplicationName as 'ApplicationName', CASE when Vaa.DesiredConfigType = 1 Then 'Install' when vaa.DesiredConfigType = 2 Then 'Uninstall' Else 'Others' End as 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' when Ds.DeploymentIntent = 3 Then 'Simulate' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays', DateDiff(D,Ds.CreationTime, GetDate()) as 'CreatedDays', Vaa.CreationTime as 'CreationTime', Vaa.LastModificationTime as 'LastModifiedTime', Vaa.LastModifiedBy as 'LastModifiedBy' from v_DeploymentSummary Ds left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 1 and Vaa.ApplicationName = @ApplicationName order by Ds.DeploymentTime desc

102. All Deployments status for Specific Package

Declare @PackageName as Varchar(255) Set @PackageName = 'SAP Client GUI 7.30' --Specify Package Name

Select Vaa.AdvertisementName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Left(Ds.SoftwareName, CharIndex('(',(Ds.SoftwareName))-1) as 'PackageName', Ds.ProgramName 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%',

Internal Use Only

DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays', DateDiff(D,Ds.ModificationTime, GetDate()) as 'CreatedDays', Ds.CreationTime as 'CreationTime', Ds.ModificationTime as 'LastModifiedTime', 'Administrator' as 'LastModifiedBy' from v_DeploymentSummary Ds join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID Where Ds.FeatureType = 2 and Ds.SoftwareName like @PackageName + '%' order by Ds.DeploymentTime desc

103. All Deployments status for Specific Software Update Group 
Declare @DeploymentName as varchar(255) Set @DeploymentName = 'SU WKS-UAT Computers' --Specify Deployment Name

Select Vaa.AssignmentName as 'DeploymentName', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'Others', Ds.NumberUnknown as 'Unknown', case when (Ds.NumberSuccess = 0) or (Ds.NumberSuccess is null) Then '0' Else (round(Ds.NumberSuccess/ convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays', DateDiff(D,Ds.CreationTime, GetDate()) as 'CreatedDays', Vaa.CreationTime as 'CreationTime', Vaa.LastModificationTime as 'LastModifiedTime', Vaa.LastModifiedBy as 'LastModifiedBy' from v_DeploymentSummary Ds left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID Where Ds.FeatureType = 5 and Vaa.AssignmentName = @DeploymentName order by Ds.DeploymentTime desc

104. All Deployments status for Specific Task Sequence
 Declare @TaskSequenceName as varchar(255) Set @TaskSequenceName = 'Windows 7 x64-CoreImage' --Specify TaskSequenceName

Select Vaa.AdvertisementName as 'DeploymentName', Right(Ds.CollectionName,3) as 'Stage', Ds.SoftwareName as 'TaskSequenceName', Ds.ProgramName 'DepType', Ds.CollectionName as 'CollectionName', CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose', Ds.DeploymentTime as 'AvailableTime', Ds.EnforcementDeadline as 'RequiredTime', Ds.NumberTotal as 'Target', Ds.NumberSuccess as 'Success', Ds.NumberInProgress as 'Progress', Ds.NumberErrors as 'Errors', Ds.NumberOther as 'ReqNotMet',

Internal Use Only

Ds.NumberUnknown as 'Unknown', case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%', DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays', DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays', DateDiff(D,Ds.ModificationTime, GetDate()) as 'CreatedDays', Ds.CreationTime as 'CreationTime', Ds.ModificationTime as 'LastModifiedTime', 'Administrator' as 'LastModifiedBy' from v_DeploymentSummary Ds join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID Where Ds.FeatureType = 7 and Ds.SoftwareName = @TaskSequenceName order by Ds.DeploymentTime desc

105. Deployment status for specific application with specific collection

Declare @ApplicationName as Varchar(255) Declare @CollectionName as Varchar(255) Set @ApplicationName = 'Adobe Creative-Cloud Design-Standard 2015 (64-bit)' -- Specify Application name Set @CollectionName = '%Adobe Creative-Cloud Design-Standard 2015%' -- Specify Application name

select aa.ApplicationName as 'Application Name', aa.CollectionName as 'Target Collection', ae.descript as 'DeploymentTypeName', s1.netbios_name0 as 'ComputerName', s1.AD_Site_Name0 as 'ADSiteName', case when ae.AppEnforcementState = 1000 then 'Success' when ae.AppEnforcementState = 1001 then 'Already Compliant' when ae.AppEnforcementState = 1002 then 'Simulate Success' when ae.AppEnforcementState = 2000 then 'In Progress' when ae.AppEnforcementState = 2001 then 'Waiting for Content' when ae.AppEnforcementState = 2002 then 'Installing' when ae.AppEnforcementState = 2003 then 'Restart to Continue' when ae.AppEnforcementState = 2004 then 'Waiting for maintenance window' when ae.AppEnforcementState = 2005 then 'Waiting for schedule' when ae.AppEnforcementState = 2006 then 'Downloading dependent content' when ae.AppEnforcementState = 2007 then 'Installing dependent content' when ae.AppEnforcementState = 2008 then 'Restart to complete' when ae.AppEnforcementState = 2009 then 'Content downloaded' when ae.AppEnforcementState = 2010 then 'Waiting for update' when ae.AppEnforcementState = 2011 then 'Waiting for user session reconnect' when ae.AppEnforcementState = 2012 then 'Waiting for user logoff' when ae.AppEnforcementState = 2013 then 'Waiting for user logon' when ae.AppEnforcementState = 2014 then 'Waiting to install' when ae.AppEnforcementState = 2015 then 'Waiting retry' when ae.AppEnforcementState = 2016 then 'Waiting for presentation mode' when ae.AppEnforcementState = 2017 then 'Waiting for Orchestration' when ae.AppEnforcementState = 2018 then 'Waiting for network' when ae.AppEnforcementState = 2019 then 'Pending App-V Virtual Environment' when ae.AppEnforcementState = 2020 then 'Updating App-V Virtual Environment' when ae.AppEnforcementState = 3000 then 'Requirements not met' when ae.AppEnforcementState = 3001 then 'Host platform not applicable' when ae.AppEnforcementState = 4000 then 'Unknown' when ae.AppEnforcementState = 5000 then 'Deployment failed' when ae.AppEnforcementState = 5001 then 'Evaluation failed' when ae.AppEnforcementState = 5002 then 'Deployment failed' when ae.AppEnforcementState = 5003 then 'Failed to locate content' when ae.AppEnforcementState = 5004 then 'Dependency installation failed' when ae.AppEnforcementState = 5005 then 'Failed to download dependent content'

Internal Use Only

when ae.AppEnforcementState = 5006 then 'Conflicts with another application deployment' when ae.AppEnforcementState = 5007 then 'Waiting retry' when ae.AppEnforcementState = 5008 then 'Failed to uninstall superseded deployment type' when ae.AppEnforcementState = 5009 then 'Failed to download superseded deployment type' when ae.AppEnforcementState = 5010 then 'Failed to updating App-V Virtual Environment' End as 'State Message', case when ae.AppEnforcementState like '10%' then 'Success' when ae.AppEnforcementState like '20%' then 'Progress' when ae.AppEnforcementState like '30%' then 'ReqNotMet' when ae.AppEnforcementState like '40%' then 'Unknown' when ae.AppEnforcementState like '50%' then 'Failed' End as 'Status', LastComplianceMessageTime as 'LastMessageTime' from v_R_System_Valid s1 join vAppDTDeploymentResultsPerClient ae on ae.ResourceID = s1.ResourceID join v_CICurrentComplianceStatus ci2 on ci2.CI_ID = ae.CI_ID AND ci2.ResourceID=s1.ResourceID join v_ApplicationAssignment aa on ae.AssignmentID = aa.AssignmentID where ae.AppEnforcementState is not null and (aa.ApplicationName = @ApplicationName and aa.CollectionName like @CollectionName) order by ae.AppEnforcementState, LastComplianceMessageTime Desc

106. Deployment status for specific package with specific collection 
Declare @PackageName as Varchar(255) Declare @CollectionName as Varchar(255)

Set @PackageName = 'Lync 2013' -- Specify Application name Set @CollectionName = '%Lync%' -- Specify Collection name

select distinct pack.Name as 'Package Name', COLL.CollectionName as 'Target Collection', adv.ProgramName as 'DeploymentTypeName', Vrs.Name0 as 'ComputerName', Vrs.AD_Site_Name0 as 'ADSiteName', ADV.AdvertisementID as 'AdvertisementID', ADVS.LastStateName as 'Status', ADVS.LastStatusTime as 'Status Time' from v_R_System Vrs INNER JOIN vSMS_ClientAdvertisementStatus ADVS ON Vrs.ResourceID = ADVS.ResourceID INNER JOIN v_Advertisement ADV ON ADV.AdvertisementID = ADVS.AdvertisementID INNER JOIN v_FullCollectionMembership CM ON Vrs.ResourceID = CM.ResourceID LEFT JOIN v_Package Pack on adv.PackageID = pack.PackageID LEFT JOIN v_Collections COLL ON ADV.CollectionID = COLL.SiteID where pack.Name = @PackageName and COLL.CollectionName like @CollectionName Order by Vrs.Name0

107. Deployment status for specific software Update deployment 
 Declare @DeploymentName as Varchar(255) Set @DeploymentName = 'SU WKS-All Production Computers' -- Specify Software update deployment name

select vrs.Name0, vrs.Active0, vrs.AD_Site_Name0, vrs.User_Name0, vrs.Operating_System_Name_and0, a.Assignment_UniqueID as DeploymentID, a.AssignmentName as DeploymentName, a.StartTime as Available,

Internal Use Only

a.EnforcementDeadline as Deadline, sn.StateName as LastEnforcementState, wsus.LastErrorCode as 'LasErrorCode', wsus.LastScanTime as 'LastWSUSScan', DateDiff(D, wsus.LastScanTime, GetDate()) as 'LastWSUSScan Age', wks.LastHWScan, DateDiff(D, wks.LastHwScan, GetDate()) as 'LastHWScan Age' from v_CIAssignment a join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0) join v_R_System vrs on vrs.ResourceID = assc.ResourceID join v_GS_WORKSTATION_STATUS wks on wks.ResourceID = assc.ResourceID join v_UpdateScanStatus wsus on wsus.ResourceID = assc.ResourceID where a.AssignmentName = @DeploymentName and assc.StateType in (300,301) order by 11 desc

108. All Collections with RefreshType 
Select CollectionID as 'ColletionID', Name as 'ColletionName', case Refreshtype when 1 then 'NoScheduled Update' when 2 then 'Full Scheduled Update' when 4 then 'Incremental Update Update(Only)' when 6 then 'Incremental and Full Scheduled Update' End as 'Refreshtype' from v_Collection order by refreshtype

109. All Software Inventory Report for Specific Computer Based on Installed Software Class 

Select sys.Netbios_Name0, vos.Caption0 AS [Operating System], varp.ARPDisplayName0 AS [Software Name], varp.Publisher0, varp.ProductVersion0, varp.TimeStamp, varp.InstallDate0, Varp.ProductID0, fcm.SiteCode, sys.User_Name0, sys.User_Domain0 FROM v_R_System sys JOIN v_GS_INSTALLED_SOFTWARE varp ON sys.ResourceID = varp.ResourceID JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID JOIN v_GS_OPERATING_SYSTEM vos on sys.ResourceID=vos.ResourceID WHERE ARPDisplayName0 LIKE '%' and sys.Name0 in ('Client01')

Read More