Packages missing Binary Delta Replication

A report to show which packages is missing Binary Delta Replication checkbox.

SELECT PackageID, Manufacturer, Name, Version,
CASE
WHEN PkgFlags & 0x04000000 = 0x04000000 THEN 'Yes'
ELSE 'No'
END AS 'Use Binary Delta Rep'
FROM v_Package
WHERE PkgFlags & 0x04000000 != 0x04000000
AND PackageType = 0

PackageType

• 0 Regular software distribution package.
• 3 Driver package.
• 4 Task sequence package.
• 5 Software update package.
• 6 Device setting package.
• 257 Image package.
• 258 Boot image package.
• 259 Operating system install package.

PkgFlags

• 0x01000000 (24) DO_NOT_DOWNLOAD. Do not download the package to branch distribution points, as it will be pre-staged.
• 0x02000000 (25) PERSIST_IN_CACHE. Persist the package in the cache.
• 0x04000000 (26) USE_BINARY_DELTA_REP. Marks the package to be replicated by distribution manager using binary delta replication.
• 0x10000000 (28) NO_PACKAGE. The package does not require distribution points.
• 0x20000000 (29) USE_SPECIAL_MIF. This value determines if Configuration Manager uses MIFName, MIFPublisher, and MIFVersion for MIF file status matching. Otherwise, Configuration Manager uses Name, Manufacturer, and Version for status matching. For more information, see the Remarks section later in this topic.
• 0x40000000 (30) DISTRIBUTE_ON_DEMAND. The package is allowed to be distributed on demand to branch distribution points.

http://msdn.microsoft.com/en-us/library/cc146062.aspx

Collection finder

A report to find the parents to a collection to be able to find the collection if you only have the name or ID.

SELECT
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT5ID.parentCollectionID) AS 'Parent 5',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT4ID.parentCollectionID) AS 'Parent 4',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT3ID.parentCollectionID) AS 'Parent 3',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT2ID.parentCollectionID) AS 'Parent 2',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT1ID.parentCollectionID) AS 'Parent 1',
COLL.Name AS 'Collection Name',
COLL.CollectionID AS 'CollectionID'

FROM v_Collection COLL
LEFT JOIN v_CollectToSubCollect COLLISPARENT ON COLL.CollectionID = COLLISPARENT.parentCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT1ID ON COLL.CollectionID = COLLPARENT1ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT2ID ON COLLPARENT1ID.parentCollectionID = COLLPARENT2ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT3ID ON COLLPARENT2ID.parentCollectionID = COLLPARENT3ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT4ID ON COLLPARENT3ID.parentCollectionID = COLLPARENT4ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT5ID ON COLLPARENT4ID.parentCollectionID = COLLPARENT5ID.subCollectionID

WHERE CollectionID=@CollName

Prompt:

begin
   if (@__filterwildcard = '')
      SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
   else
      SELECT DISTINCT CollectionID, Name FROM v_Collection
      WHERE Name like @__filterwildcard
      ORDER BY Name
end

Collection history

A report to show who created, modified or deleted a collection, and when.

SELECT SM.Time, SMIS.InsStrValue AS 'Collection',
CASE SM.MessageID
   WHEN 30015 THEN 'Created'
   WHEN 30016 THEN 'Modified'
   WHEN 30017 THEN 'Deleted'
END AS 'Message', SMA.AttributeValue AS 'By User', SM.MachineName AS 'From Computer'
FROM v_StatMsgInsStrings SMIS
LEFT JOIN v_StatMsgAttributes SMA on SMA.RecordID=SMIS.RecordID
LEFT JOIN v_StatusMessage SM on SM.RecordID=SMIS.RecordID
WHERE SMIS.RecordID in (SELECT RecordID FROM v_StatMsgAttributes WHERE AttributeValue=@CollectionID)
      AND SMA.AttributeID=403 AND SMIS.InsStrIndex > 1 AND SM.MessageID in (30015, 30016, 30017)
ORDER BY SM.Time desc

Prompt:

begin
   if (@__filterwildcard = '')
      SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
   else
      SELECT DISTINCT CollectionID, Name FROM v_Collection
      WHERE Name like @__filterwildcard
      ORDER BY Name
end

Packages missing on DP and referenced by TS

A report to show all packages that are targeted to a distribution point bur aren't there yet, for some reason, and are referenced by a specific Task Sequence.

SELECT ps.PackageID, ps.SiteCode,
SUBSTRING(ps.ServerNALPath, CHARINDEX('\\', ps.ServerNALPath), CHARINDEX('"]',    ps.ServerNALPath) - CHARINDEX('\\',
     ps.ServerNALPath)) AS ServerNALPath,
ps.SourceVersion, ps.State, ps.LastCopied, ps.SummaryDate,
ps.InstallStatus, p.Manufacturer, p.Name
FROM
     (
         SELECT DISTINCT v_TaskSequenceReferencesInfo.ReferencePackageID AS PackageID
         FROM v_TaskSequenceReferencesInfo
         WHERE v_TaskSequenceReferencesInfo.PackageID=@TaskSequenceID
     ) RefPkgs
JOIN v_PackageStatusDistPointsSumm ps ON ps.PackageID=RefPkgs.PackageID
JOIN v_Package p ON p.PackageID=ps.PackageID
WHERE ps.InstallStatus!='Package Installation complete'
ORDER BY ps.PackageID

Prompts:

BEGIN
 IF (@__filterwildcard = '')
  SELECT PackageID AS TaskSequenceID, Name, Description FROM v_TaskSequencePackage
  ORDER BY Name
 ELSE
  SELECT PackageID AS TaskSequenceID, Name, Description FROM v_TaskSequencePackage
  WHERE PackageID LIKE @__filterwildcard
  ORDER BY Name
END