widsnet.com
24Jan/130

Count number of client with specified program in a collection

Enter a program name, ID or publisher and a collectionID and the report will list the number of clients with the specified program installed.

SELECT arp.Publisher0, arp.DisplayName0, arp.ProdID0, Count(DISTINCT sys.Name0) AS 'Count'
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID 
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE (arp.DisplayName0 LIKE @SearchKey OR arp.Publisher0 LIKE @SearchKey OR arp.ProdID0 LIKE @SearchKey) 
AND fcm.CollectionID=@CollID
GROUP BY arp.Publisher0, arp.DisplayName0, arp.ProdID0
ORDER BY arp.DisplayName0 ASC

Prompts
Name: SearchKey
Prompt text: Software / Publisher / Product ID

begin
 if (@__filterwildcard = '')
  Select DISTINCT DisplayName0 FROM v_Add_Remove_Programs order by DisplayName0
 else
  Select DISTINCT DisplayName0 FROM v_Add_Remove_Programs
  WHERE DisplayName0 like @__filterwildcard
  order by DisplayName0
end

Name: CollID
Prompt text: CollectionID

begin
 if (@__filterwildcard = '')
  select CollectionID, Name from v_Collection order by Name
 else
  select CollectionID, Name from v_Collection
  WHERE Name like @__filterwildcard
  order by Name
end
4Jul/110

All hidden advertisements

A report to show all advertisements that are hidden from the users.

SELECT AdvertisementID, AdvertisementName,
CASE
  WHEN AdvertFlags & 0x02000000 = 0x02000000 Then 'Yes'
  ELSE 'No'
END AS 'No Display'
FROM v_Advertisement
WHERE AdvertFlags & 0x02000000 = 0x02000000

AdvertFlags

  • 0x00000020 (5)
    IMMEDIATE. Announce the advertisement to the user immediately.
  • 0x00000100 (8)
    ONSYSTEMSTARTUP. Announce the advertisement to the user on system startup.
  • 0x00000200 (9)
    ONUSERLOGON. Announce the advertisement to the user on logon.
  • 0x00000400 (10)
    ONUSERLOGOFF. Announce the advertisement to the user on logoff.
  • 0x00008000 (15)
    WINDOWS_CE. The advertisement is for a device client.
  • 0x00020000 (17)
    DONOT_FALLBACK. Do not fall back to unprotected distribution points.
  • 0x00040000 (18)
    ENABLE_TS_FROM_CD_AND_PXE. The task sequence is available to removable media and the pre-boot execution environment (PXE) service point.
  • 0x00100000 (20)
    OVERRIDE_SERVICE_WINDOWS. Override maintenance windows in announcing the advertisement to the user.
  • 0x00200000 (21)
    REBOOT_OUTSIDE_OF_SERVICE_WINDOWS. Reboot outside of maintenance windows.
  • 0x00400000 (22)
    WAKE_ON_LAN_ENABLED. Announce the advertisement to the user with Wake On LAN enabled.
  • 0x00800000 (23)
    SHOW_PROGRESS. Announce the advertisement to the user showing task sequence progress.
  • 0x02000000 (25)
    NO_DISPLAY. The user should not run programs independently of the assignment.
  • 0x04000000 (26)
    ONSLOWNET. Assignments are mandatory over a slow network connection.

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

4Jul/110

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

1Jul/110

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
1Jul/110

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
1Jul/110

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