select distinct aa.ApplicationName, ae.AssignmentID, aa.CollectionNameas'Target Collection', ae.descriptas'Deployment Type Name', s1.netbios_name0as'Computer Name', ci2.LastComplianceMessageTime, ae.AppEnforcementState, casewhenae.AppEnforcementState= 1000 then'Success' whenae.AppEnforcementState= 1001 then'Already Compliant' whenae.AppEnforcementState= 1002 then'Simulate Success' whenae.AppEnforcementState= 2000 then'In Progress' whenae.AppEnforcementState= 2001 then'Waiting for Content' whenae.AppEnforcementState= 2002 then'Installing' whenae.AppEnforcementState= 2003 then'Restart to Continue' whenae.AppEnforcementState= 2004 then'Waiting for maintenance window' whenae.AppEnforcementState= 2005 then'Waiting for schedule' whenae.AppEnforcementState= 2006 then'Downloading dependent content' whenae.AppEnforcementState= 2007 then'Installing dependent content' whenae.AppEnforcementState= 2008 then'Restart to complete' whenae.AppEnforcementState= 2009 then'Content downloaded' whenae.AppEnforcementState= 2010 then'Waiting for update' whenae.AppEnforcementState= 2011 then'Waiting for user session reconnect' whenae.AppEnforcementState= 2012 then'Waiting for user logoff' whenae.AppEnforcementState= 2013 then'Waiting for user logon' whenae.AppEnforcementState= 2014 then'Waiting to install' whenae.AppEnforcementState= 2015 then'Waiting retry' whenae.AppEnforcementState= 2016 then'Waiting for presentation mode' whenae.AppEnforcementState= 2017 then'Waiting for Orchestration' whenae.AppEnforcementState= 2018 then'Waiting for network' whenae.AppEnforcementState= 2019 then'Pending App-V Virtual Environment' whenae.AppEnforcementState= 2020 then'Updating App-V Virtual Environment' whenae.AppEnforcementState= 3000 then'Requirements not met' whenae.AppEnforcementState= 3001 then'Host platform not applicable' whenae.AppEnforcementState= 4000 then'Unknown' whenae.AppEnforcementState= 5000 then'Deployment failed' whenae.AppEnforcementState= 5001 then'Evaluation failed' whenae.AppEnforcementState= 5002 then'Deployment failed' whenae.AppEnforcementState= 5003 then'Failed to locate content' whenae.AppEnforcementState= 5004 then'Dependency installation failed' whenae.AppEnforcementState= 5005 then'Failed to download dependent content' whenae.AppEnforcementState= 5006 then'Conflicts with another application deployment' whenae.AppEnforcementState= 5007 then'Waiting retry' whenae.AppEnforcementState= 5008 then'Failed to uninstall superseded deployment type' whenae.AppEnforcementState= 5009 then'Failed to download superseded deployment type' whenae.AppEnforcementState= 5010 then'Failed to updating App-V Virtual Environment' Endas'State Message' fromv_R_System_Valids1 joinvAppDTDeploymentResultsPerClientaeonae.ResourceID=s1.ResourceID joinv_CICurrentComplianceStatusci2onci2.CI_ID=ae.CI_IDAND ci2.ResourceID=s1.ResourceID joinv_ApplicationAssignmentaaonae.AssignmentID=aa.AssignmentID whereae.AppEnforcementStateisnotnullandaa.ApplicationName='Adobe Reader XI 11.0.13' orderbyLastComplianceMessageTimeDesc
Boundaries With Count of Devices
SELECT
IP_Subnets0 AS ‘Subnets Detected’,
vSMS_Boundary.Value AS ‘Sorted:Boundary Values’,
COUNT(v_RA_System_IPSubnets.ResourceID)AS ‘Count of Devices’,
vSMS_Boundary.DisplayName,
vSMS_Boundary.CreatedOn,
vSMS_Boundary.ModifiedOn
FROM v_RA_System_IPSubnets
Full Join vSMS_Boundary on vSMS_Boundary.Value =
v_RA_System_IPSubnets.IP_Subnets0
Where (v_RA_System_IPSubnets.IP_Subnets0 like ‘172.1[6-9].%’ or
v_RA_System_IPSubnets.IP_Subnets0 like ‘172.2[0-9].%’ or
v_RA_System_IPSubnets.IP_Subnets0 like ‘172.3[0-1].%’) or
(vSMS_Boundary.Value like ‘172.1[6-9].%’ or
vSMS_Boundary.Value like ‘172.2[0-9].%’ or
vSMS_Boundary.Value like ‘172.3[0-1].%’)or
v_RA_System_IPSubnets.IP_Subnets0 like ’10.%’ or
v_RA_System_IPSubnets.IP_Subnets0 like ‘192.168.%’ or
vSMS_Boundary.Value like ’10.%’ or
vSMS_Boundary.Value like ‘192.168.%’
Group By vSMS_Boundary.Value,
v_RA_System_IPSubnets.IP_Subnets0,vSMS_Boundary.DisplayName,vSMS_Boundary.CreatedOn,vSMS_Boundary.ModifiedOn
Order By [Sorted:Boundary Values]
Specific application From specific Collection ID
SELECT DISTINCT c.Name0 AS [Machine Name] , a.DisplayName0 AS [software name], a.Version0 AS [Version], dbo.v_FullCollectionMembership.CollectionID
FROM dbo.v_Add_Remove_Programs AS a INNER JOIN
dbo.v_R_System AS c ON a.ResourceID = c.ResourceID INNER JOIN
dbo.v_FullCollectionMembership ON c.ResourceID = dbo.v_FullCollectionMembership.ResourceID
WHERE (a.DisplayName0 LIKE ‘JAVA%’) AND (dbo.v_FullCollectionMembership.CollectionID IN (‘XXXXX’))
Software Updates Installed using SCCM or Manually
SELECT sys.Name0,ui.BulletinID, ui.ArticleID,ui.Title,
CASE when (ucs.Status=2 and ui.IsDeployed=0 )then ‘Required_General’
when (ucs.Status=2 and ui.IsDeployed=1 )then ‘Required_ITICSDeploy’
WHEN (UCS.Status=3 and ui.IsDeployed=1 ) then ‘Installed_SCCM’
WHEN (UCS.Status=3 and ui.IsDeployed=0 ) then ‘Installed_Manual’
when UCS.Status=0 then ‘Unknown’ end as ‘Status’, case WHEN ui.severity=10 THEN ‘Critical’
WHEN ui.severity=8 THEN ‘Important’
WHEN ui.severity=6 THEN ‘Moderate’
WHEN ui.severity=2 THEN ‘Low’ WHEN ui.severity=0 THEN ‘AddOn’ end as ‘Severity’
FROM v_R_System sys
INNER JOIN v_UpdateComplianceStatus UCS ON sys.ResourceID = ucs.ResourceID
INNER JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
WHERE –UI.IsDeployed=1 and
sys.Netbios_Name0=’computer1′
ORDER BY Status
Pull Distribution Points with Source Distribution Points SQL Query
SELECT DISTINCT
dbo.v_DistributionPoints.ServerName AS [Source DP for Pull], dbo.v_DistributionPoints.IsPeerDP, dbo.v_DistributionPoints.IsPullDP,
dbo.vPullDPFullMap.PullDPNALPath AS [All Types of DP List], dbo.v_DistributionPoints.IsPXE, dbo.v_DistributionPoints.Description
FROM dbo.vPullDPFullMap INNER JOIN
dbo.v_DistributionPoints ON dbo.vPullDPFullMap.SourceDPNALPath = dbo.v_DistributionPoints.NALPath
//////////******* List of Views /Tables with fields**********////////////
////////////////****************************** List of Tables with fields************************///////////////////////////
SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE], CAST(P.PRECISION AS VARCHAR) +’/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE] FROM SYS.OBJECTS AS T JOIN SYS.COLUMNS AS C ON T.OBJECT_ID=C.OBJECT_ID JOIN SYS.TYPES AS P ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID WHERE T.TYPE_DESC=’USER_TABLE’;
////////////////****************************** List of Views with fields************************///////////////////////////
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS
Report to list of all users laptops
SELECT distinct dbo.v_R_System.Name0 AS [Computer Name], dbo.v_R_System.User_Name0 AS [User Name], dbo.v_R_System.User_Domain0 AS [Domain Name],
dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number], dbo.v_GS_SYSTEM.SystemRole0 AS [System OS Type],
dbo.v_GS_SYSTEM.SystemType0 AS [System Type]
FROM dbo.v_GS_SYSTEM_ENCLOSURE INNER JOIN
dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ‘8’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ‘9’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’10’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’11’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’12’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’14’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’18’) OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = ’21’)
SCCM Collections WQL Query—–Include or Exclude other collections
select distinct c.name as [Collection Name],
c.collectionid,
cdepend.SourceCollectionID as ‘Collection Dependency’,
cc.Name as ‘Collection Dependency Name’,
Case When
cdepend.relationshiptype = 1 then ‘Limited To ‘ + cc.name + ‘ (‘ + cdepend.SourceCollectionID + ‘)’
when cdepend.relationshiptype = 2 then ‘Include ‘ + cc.name + ‘ (‘ + cdepend.SourceCollectionID + ‘)’
when cdepend.relationshiptype = 3 then ‘Exclude ‘ + cc.name + ‘ (‘ + cdepend.SourceCollectionID + ‘)’
end as ‘Type of Relationship’
from v_Collection c
join vSMS_CollectionDependencies cdepend on cdepend.DependentCollectionID=c.CollectionID
join v_Collection cc on cc.CollectionID=cdepend.SourceCollectionID
where c.CollectionID = ‘XXX0077A’
All application list
select * from fn_ListLatestApplicationCIs(1033)
select DateCreated, DateLastModified, DisplayName, Manufacturer, SoftwareVersion, CreatedBy, LastModifiedBy
from fn_ListLatestApplicationCIs(1033)
GROUP BY DateCreated,DateLastModified,DisplayName,Manufacturer,SoftwareVersion,CreatedBy, LastModifiedBy
ORDER BY DateCreated DESC
For deployment types ::: fn_ListDeploymentTypeCIs(1033)
All OS with Versions
SELECT DISTINCT
dbo.v_R_System.Netbios_Name0 as [Machine Name],dbo.v_R_System.User_Name0 as [User Name],dbo.v_R_System.AD_Site_Name0 as [AD Site],
dbo.v_R_System.User_Domain0 as [Domain]
, dbo.v_GS_OPERATING_SYSTEM.Caption0 as [OS Name],
dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 as [SP Name], dbo.v_R_System.Operating_System_Name_and0 as [OS NT Version],dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 as [Build Number]
FROM dbo.v_R_System INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID