Application Deployment options for all App Deployments

Have you ever wondered if you could get a report of all your Application Deployments' options?  The ones which are in the GUI for things like "User Experience, Show a dialog window instead of a toast", or "Deployment Settings, Send wake-up packets".  No?  Well, I did.  So with the help of my good friend John Nelson, attached is the SQL to accomplish that.  Below is a (very badly displayed, sorry) results of a query in my lab--where I only have two fake test deployments, where I was testing that all the values were getting reported properly.  "It works in my lab".

Attached --> here <-- is the .sql itself, or it's below.  In theory, I thought this would be helpful for finding if you wanted to be sure everything was designed to "show the popup diag instead of toast"--you could easily filter and sort and see what deployments might not have a setting you wanted it to have.

--###############################################
--Cleanup any accidentally left behind Temp Tables
--###############################################

If(OBJECT_ID('tempdb..#TempDeplInfoBase') Is Not Null)
Begin
 Drop Table #TempDeplInfoBase
End

create table #TempDeplInfoBase(
AssignmentID int,
Assignment_UniqueID nvarchar(max),
AssignmentEnabled int,
AssignmentName nvarchar(max),
CollectionName nvarchar(max),
CollectionID nvarchar(8),
InstallorUninstall nvarchar(25),
OptionalOrRequired nvarchar(25),
WOLEnabled int,
DPLocality int,
StartTime DateTime,
EnforcementDeadline DateTime,
TimeType nvarchar(25),
SoftDeadline int,
OverrideServiceWindows int,
RebootOutsideOfServiceWindows int,
WriteFilter int,
RandomizationEnabled int,
RandomizationMinutes int,
UseBranchCache int,
EnableMomAlerts int,
RaiseMomAlertsOnFailure int,
NotifyUser nvarchar(100),
PreDeploy int,
CloseDefinedRunningExes int,
AllowRepair int,
UseDialogNotToast int
)

INSERT INTO #TempDeplInfoBase (assignmentid,Assignment_UniqueID,AssignmentEnabled,AssignmentName,CollectionName,CollectionID,InstallorUninstall,OptionalOrRequired,WOLEnabled,
DPLocality,StartTime,EnforcementDeadline,TimeType,SoftDeadline,OverrideServiceWindows,RebootOutsideOfServiceWindows,WriteFilter,RandomizationEnabled,
RandomizationMinutes,UseBranchCache,EnableMomAlerts,RaiseMomAlertsOnFailure,NotifyUser,Predeploy,CloseDefinedRunningExes,AllowRepair,UseDialogNotToast)

Select
c.AssignmentID,
c.Assignment_UniqueID,
c.AssignmentEnabled,
c.AssignmentName,
c.CollectionName,
c.collectionid,
Case when c.DesiredConfigType = 1 then 'Install'
  when c.DesiredConfigType = 2 then 'Uninstall'
 else cast(c.DesiredConfigType as nvarchar)
end as 'InstallOrUninstall',
case when c.OfferTypeID = 2 then 'Available'
 when c.OfferTypeID = 0 then 'Required'
 else cast(C.OfferTypeID as nvarchar)
end as 'OptionalOrRequired',
c.WOLEnabled as 'Send Wake-up Packets',
Case when c.DPLocality > 80 then 1 else 0 end as 'Allow clients on a metered connection to dl content after deadline',
c.StartTime,
c.EnforcementDeadline,
case when c.UseGMTTimes=0 then 'Client Local Time' Else 'UTC Time' end as 'TimeType',
c.SoftDeadlineEnabled as 'Delay enforcement per user preferences, up to the grace period',
c.OverrideServiceWindows as 'Override Maintenance Window, for Installation',
c.RebootOutsideOfServiceWindows as 'Override Maintenance Window, for System Restart',
c.PersistOnWriteFilterDevices as 'write-filter handling, Commit Changes at deadline for Windows Embedded devices',
c.RandomizationEnabled,
c.RandomizationMinutes,
c.UseBranchCache,
c.DisableMomAlerts as 'Enable SCOM MM',
c.RaiseMomAlertsOnFailure as 'Generate SCOM Alert when failure',
case
 when c.NotifyUser=1 and c.UserUIExperience=1 and (32 & c.OfferFlags) = 32 then 'Use Dialog to NotifyUser at Available, and notify for reboot post-install'
 when c.NotifyUser=1 and c.UserUIExperience=1 and (32 & c.OfferFlags) <> 32 then 'Use Toast to NotifyUser at Available, and notify for reboot post-install'
 when c.NotifyUser=0 and c.UserUIExperience=1 then 'Suppress User at Available, notify if reboot post-install'
 when c.NotifyUser=0 and c.UserUIExperience=0 then 'Suppress all User notifications'
end as 'NotifyUser',
Case when (1 & c.OfferFlags) = 1 then 1 else 0 end as 'PreDeploy' --'Pre-Deploy Software to the User Primary Device',
Case when (4 & c.OfferFlags) = 4 then 1 else 0 end as 'CloseDefinedRunningExes' --Automatically close any running executables you specified on the install behavior tab of the deployment type properties,
Case when (8 & c.OfferFlags) = 8 then 1 else 0 end as 'AllowRepair' --Allow End users to Attempt to repair the application,
Case when (32 & c.OfferFlags) = 32 then 1 else 0 end as 'UseDialogNotToast' --When software changes are required, show a dialog window to the user instead of a toast notification

from

v_CIAssignment c where c.AssignmentType=2

;WITH
PCT9 AS (
  SELECT
  RawTypeID,
  TypeInstanceID,
  SkipUntil,
  ParameterValues.value('(/Parameters/Parameter[@index=3])[1]','integer') AS PCT
FROM
  v_Alert
WHERE
RawTypeID = 9
),

PCT10 AS (
  SELECT
  RawTypeID,
  TypeInstanceID,
  SkipUntil,
  ParameterValues.value('(/Parameters/Parameter[@index=3])[1]','integer') AS PCT
FROM
  v_Alert
WHERE
RawTypeID = 10
)

select Distinct
t1.AssignmentID,t1.AssignmentEnabled,t1.CollectionName,t1.CollectionID,t1.InstallOrUninstall,
t1.AssignmentName,t1.OptionalOrRequired,t1.WOLEnabled as 'Send Wake-up Packets',
t1.DPLocality as 'Allow clients on a metered connection to Download content after deadline',
t1.StartTime as 'DeploymentAvailableTime',t1.EnforcementDeadline,t1.TimeType,
t1.SoftDeadline as 'Delay enforcement per user preferences, up to the grace period',
t1.OverrideServiceWindows as 'Override Maintenance Window, for Installation',
t1.RebootOutsideOfServiceWindows as 'Override Maintenance Window, for System Restart',
t1.WriteFilter as 'write-filter handling, Commit Changes at deadline for Windows Embedded devices',
t1.RandomizationEnabled,t1.RandomizationMinutes,t1.UseBranchCache,
t1.EnableMomAlerts,t1.RaiseMomAlertsOnFailure,t1.NotifyUser,
t1.PreDeploy as 'Pre-Deploy Software to the User Primary Device',
t1.CloseDefinedRunningExes as 'Automatically close any running executables you specified on the install behavior tab of the deployment type properties',
t1.AllowRepair as 'Allow End users to Attempt to repair the application',
t1.UseDialogNotToast as 'When software changes are required, show a dialog window to the user instead of a toast notification',
COALESCE(PCT9.SkipUntil,PCT10.SkipUntil) AS 'CM Alert if Success SkipUntil Date',
PCT9.PCT AS 'CM Alert if Success Rate Percentage Less than this after the SkipUntil Date',
PCT10.PCT AS 'CM Alert if Failure Rate Higher than this percentage'
from #TempDeplInfoBase t1
LEFT JOIN PCT9 ON t1.Assignment_UniqueID = PCT9.TypeInstanceID
LEFT JOIN PCT10 ON t1.Assignment_UniqueID = PCT10.TypeInstanceID

--###############################################
--Cleanup any accidentally left behind Temp Tables
--###############################################

If(OBJECT_ID('tempdb..#TempDeplInfoBase') Is Not Null)
Begin
  Drop Table #TempDeplInfoBase
End