Packages and Source Path of the Packages

Below is the SQL query for a Task sequence ID” 'CE100053'” with referenced packages and Source path of the packages

SELECT     TOP (100) PERCENT ps.Name AS C062, ps.SourceVersion, ps.SourceDate, ps.Targeted AS NumberOfDPsTargeted0, ps.Installed AS NumberOfDPsInstalled0,

                      ps.Retrying AS NumberOfDPsRetrying0, ps.Failed AS NumberOfDPsFailed0, ps.SourceSite, ps.SourceSize, ps.SourceCompressedSize, ps.PackageID,

                      dbo.v_Package.PkgSourcePath AS [Pkg Source Path]

FROM         (SELECT DISTINCT ReferencePackageID AS PackageID

                       FROM          dbo.v_TaskSequenceReferencesInfo

                       WHERE      (PackageID = 'CE100053')) AS RefPkgs INNER JOIN

                      dbo.v_PackageStatusRootSummarizer AS ps ON ps.PackageID = RefPkgs.PackageID INNER JOIN

                      dbo.v_Package ON ps.PackageID = dbo.v_Package.PackageID

ORDER BY C062

If you are migrating from sccm 2007 to sccm 2012.. you might need this query to identify the packages UNC path or Drive letters based packages

Selet package ID, name, PkgSourcepath from v_ackage where (PkgSourcepath like '\\') or pkgsourcepath like '%c:%')

order by PackageID

SCCM collections for Monthly activities

Collections used for systems clean-up: Few collections for Monthly activities

All decomessioned systems: -SCCM Collections
select
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Clie
nt from SMS_R_System where SMS_R_System.Decommissioned = 1

Computers in AD with no recent AD discovery - SCCM Collections
select
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Clie
nt from SMS_R_System where (ResourceDomainORWorkgroup = "MyDomainName3" or ResourceDomainORWorkgroup = "MyDomainName2" or ResourceDomainORWorkgroup =
"MyDomainName1") and ResourceId not in (select ResourceId from  SMS_R_System where AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT" and DATEDIFF
(day,AgentTime,GetDate())<=7) and ResourceId not in (select ResourceId from  SMS_R_System where AgentName = "Heartbeat Discovery" and DATEDIFF
(day,AgentTime,GetDate())<=31) and Client is not null

Computers no Heartbeat Discovery with SCCM - SCCM Collections
select
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Clie
nt from SMS_R_System where ResourceId not in (select ResourceId from  SMS_R_System where AgentName = "Heartbeat Discovery") and ResourceId not in (select
ResourceId from  SMS_R_System where AgentName = "Manual Machine Entry")

Computers with only AD Group Discovery - SCCM Collections
select
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Clie
nt from SMS_R_System where ResourceId not in (select ResourceId from  SMS_R_System where AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT") and ResourceId not in
(select ResourceId from  SMS_R_System where AgentName = "Heartbeat Discovery") and ResourceId not in (select ResourceId from  SMS_R_System where AgentName =
"Manual Machine Entry")and ResourceId in (select ResourceId from  SMS_R_System where AgentName = "SMS_AD_SYSTEM_GROUP_DISCOVERY_AGENT" )

Devices With Incorrect Names (Where the device name is not the same as the inventory name (could be duplicate guids)) - SCCM Collections

select
SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Clie
nt from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Name  <>
SMS_G_System_COMPUTER_SYSTEM.Name

Duplicate Devices Without Client -SCCM Collections
select
SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Clie
nt from SMS_R_System where Client is NULL
Manual Entry Reference
select
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Clie
nt from SMS_R_System where SMS_R_System.AgentName = "Manual Machine Entry"

All Obsolete Clients from SCCM Collections
select
SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Clie
nt from SMS_R_System where Obsolete = 1