System Center Configuration Manager sccm 2012 , sccm 2007 , ConfigMGR 2012 , ConfigMGR 2007 , System Center Configuration Manager

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

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]

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’))

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

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 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

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’)

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’

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)

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