SQL for a report of all package/task sequence advertisements and their flags

select

c.SiteID CollectionID

, c.CollectionName

, c.LimitToCollectionID

, c.LimitToCollectionName

, c.MemberCount

, a.PresentTime Available

, CM_Supplemental.dbo.ParseScheduleTokenString (a.MandatorySched) Deadline

, p.Name PackageName

, case when OfferFlags & 0x00000020 = 0x00000020 then ‘TRUE’ end IMMEDIATE — Announce the advertisement to the user immediately. AS SOON AS POSSIBLE assignment

, case when OfferFlags & 0x00000100 = 0x00000100 then ‘TRUE’ end ONSYSTEMSTARTUP — Announce the advertisement to the user on system startup.

, case when OfferFlags & 0x00000200 = 0x00000200 then ‘TRUE’ end ONUSERLOGON — Announce the advertisement to the user on logon. LOG ON assignment

, case when OfferFlags & 0x00000400 = 0x00000400 then ‘TRUE’ end ONUSERLOGOFF — Announce the advertisement to the user on logoff. LOG OFF assignment

, case when OfferFlags & 0x00008000 = 0x00008000 then ‘TRUE’ end WINDOWS_CE — The advertisement is for a device client.

, case when OfferFlags & 0x00020000 = 0x00020000 then ‘TRUE’ end DONOT_FALLBACK — Do not fall back to unprotected distribution points.

, case when OfferFlags & 0x00040000 = 0x00040000 then ‘TRUE’ end ENABLE_TS_FROM_CD_AND_PXE — The task sequence is available to removable media and the pre-boot execution environment (PXE) service point.

, case when OfferFlags & 0x00100000 = 0x00100000 then ‘TRUE’ end OVERRIDE_SERVICE_WINDOWS — Override maintenance windows in announcing the advertisement to the user. SOFTWARE INSTALLATION checkbox

, case when OfferFlags & 0x00200000 = 0x00200000 then ‘TRUE’ end REBOOT_OUTSIDE_OF_SERVICE_WINDOWS — Reboot outside of maintenance windows. SYSTEM RESTART checkbox

, case when OfferFlags & 0x00400000 = 0x00400000 then ‘TRUE’ end WAKE_ON_LAN_ENABLED — Announce the advertisement to the user with Wake On LAN enabled.

, case when OfferFlags & 0x00800000 = 0x00800000 then ‘TRUE’ end SHOW_PROGRESS — Announce the advertisement to the user showing task sequence progress. SHOW TASK SEQUENCE PROGRESS checkbox

, case when OfferFlags & 0x02000000 = 0x02000000 then ‘TRUE’ end NO_DISPLAY — The user should not run programs independently of the assignment. ALLOW USERS TO RUN THE PROGRAM INDEPENDENTLY OF ASSIGNMENTS checkbox TRUE means checkbox is cleared

, case when OfferFlags & 0x04000000 = 0x04000000 then ‘TRUE’ end ONSLOWNET — Assignments are mandatory over a slow network connection.

from vFolderMembers f

inner join vCollections c on f.InstanceKey = c.SiteID

left join vAdvertisement a on a.CollectionID = c.SiteID

left join v_Package p on p.PackageID = a.PkgID

Advertisements

2 thoughts on “SQL for a report of all package/task sequence advertisements and their flags

  1. There’s an error in this one, I realized this yesterday that the ExpirationTime contains the deadline unless an expiration time is specified. This got me into deconstructing the SMS_ScheduleToken class. I will post my deconstruction in a different blog post.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s