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

 

CMCB, SQL

  • Created on .

Windows 10 Inplace Update History Inventory

We were tasked at our company to get some statistics around machines which went through inplace upgrades, vs. machines which were on an 'original image' (or bare metal image, or whatever phrase you would like to give that).

With the assistance of --> Gary Blok <-- he suggested using the subkeys in the registry under 'HKLM:\System\Setup', which start with "Source OS ..."  Of course the problem was each and every computer which would go through an upgrade, would have a different key name.  That meant that in order to inventory that information, it would need to be a script.

Sample output; for example...what does it look like to see 'history' of machines which have gone through an upgrade? (and reported back the info from this script + mof edit)




select s1.netbios_name0 as 'ComputerName',
so.CurrentBuild0 as 'CurrentBuild'
,so.EditionID0 as 'EditionID'
,so.InstallDate0 as 'InstallDate'
,so.LatestOS0 as 'LatestOS'
,so.PathName0 as 'PathName'
,so.ReleaseID0 as 'ReleaseID'
,so.UBR0 as 'UBR'
from v_GS_SourceOS0 so
join v_r_system s1 on s1.resourceid=so.resourceid
Where so.resourceid in (
       select so.ResourceID
       from v_GS_SourceOS0 so
       Group by so.resourceid
       HAVING count(so.resourceid) > 1
       )
order by s1.netbios_name0, so.LatestOS0 desc, so.InstallDate0 desc

Sample output; for example...if someone were to ask you... Despite machines going to several upgrades, when was the machine originally imaged with a base image?



select s1.netbios_name0 as 'ComputerName', Min(InstallDate0) as 'Install Date'
from v_GS_SourceOS0 so
join v_r_system s1 on s1.resourceid=so.resourceid
Where s1.netbios_name0 = 'Computer1'
group by s1.netbios_name0


Attached --> Here <-- is a .zip file, containing three things.
- a .cab file if you just want to import the Configuration Item into your SCCM console
- 'SourceOS-IntoWMI.RenametoPS1.txt', the powershell script which is inside that CI.  Sometimes for some unknown reason, people are unable to import a .cab file into their environment.  You could create a new CI in your environment, using that .ps1 information, and the "what means compliant" would simply be existential, that any value is returned at all.
- 'SourceOS.mof' (for inventory import)

If you think this might be interesting to implement in your environment; here's the steps.

1) Either import the .cab file into your Configuration Items in your CM console
2) If importing of the .cab doesn't work, instead create a new Configuration Item, call it whatever you like, and the Setting will be a script type, string.  Paste in the contents of the 'RenametoPS1...' file.  the CI "test for compliance' will be Existential, that any value is returned at all.  
3) Add that CI to a Baseline of your choosing, and deploy the baseline to the machines you want to report on this information.  Perhaps it's only your Windows 10 devices.  I'd suggest having the baseline re-run 'every 7 days'--it really doesn't need to be more frequently, unless you have a lot of people hovering over your shoulder needing this info 'yesterday, and then every day forever'.  Frequency is up to you, but certainly not more frequently than daily.  More frequently than daily for this is overkill.
4) In your Console, Administration, Client Settings, Default Client Settings, right-click properties, Hardware Inventory, import the "SourceOS.mof".  Monitor your server's dataldr.log to confirm all is well.
5)  Wait.  Wait some more.  Wait a bit longer than that, up to a week.  <grin>  what you're waiting for is

a) the clients who got the baseline to run the baseline, and locally populate the completely-custom-wmi class of root\cimv2\cm_SourceOS. 

b) Then you're waiting for machines which have done that, to submit hardware inventory with that new, custom information.  Depending upon your environment, this could be hours... to days/over a week.  It all depends upon your environment, there is no one size fits all answer for how long you need to wait for most targets to report this information. 

c) After you've waited a bit, try out one or both of the sql queries above, to see if you have information.

Notes: in the 'SourceOS.mof'; I only set some of the values to TRUE for reporting.  If you think some of the other values which could be reported would be interesting for you to have, simply enable them in your console, so the clients start reporting those additional values.

CMCB, ConfigMgr

  • Created on .

ConfigMgr MaxExecutionTime Guesses for Updates

There is a situation which MIGHT happen for you.  The default for Cumulative Updates is, I believe 60 minutes now.  But many updates are still defaulting to 10 minutes.  I don't personally think that default should change, however, occasionally there are large updates (think Microsoft Office updates) which might be several hundred GB in size, and might take more than 10 minutes to install.  In your reporting, and when looking at local logs, the CM client says the install "Failed", but all you do is a re-scan for updates, and CM says it's installed.  So what gives, you wonder?  Well, this could be a possible reason.  It's not that the install 'failed' per se.  But after 10 minutes, the CM client stopped 'watching for' the successful install.  It timed out kind of.  Since I noticed a pattern that "it's usually when those updates are ginormous, they take longer to install", below is a POSSIBLE sql query to perhaps help you find and adjust the "Max Execution Timeout" on any individual updates.

A couple of pre-requisites.  Naturally, the content has to be downloaded. So if you run this 5 minutes after a "hotfix Tuesday" sync, it might not have much to say.  Because the content hasn't been downloaded to calculate "how big" any particular update is.  So you do have to wait until your content is downloaded to track these down.

Also note that I haven't created any kind of "powershell script" to automatically adjust the Max Execution Timeout.  This is just a report, and the admin would either posh-script changing each individual update, or use the console, find each update, right-click on it and in properties for that update, adjust up the max Execution Timeout to fit.

Also note these "suggestions" are just that, suggestions.  There is no right or wrong answer for how long Max Execution Timeout should be.  Leaving it all alone as-is with no changes from what you have will still work just fine.  One of the problems you may encounter might discourage you from touching or doing anything with this at all could be this following scenario...  Here's the scenario where following these suggestions would be a big bad horrible idea.  Let's say you allow your devices to have a service window every night for 4 hours.  Then you follow these suggestions, and for whatever reason, there were 8 different Office updates, and you changed them all from 10 minutes to 60 minutes each... for a total of 8 hours estimated time to install.  A client, when it gets the Software Update deployment, when it used to think "ok, these 8 will take me 80 minutes, I can do that in my 4 hour window, let's start!".  It'll start installing, and maybe it only gets 3 done... but it does get 3 done.  If you set them to 60 minutes each, the client might decide "wow, 8 hours... I can't do that in my service window... I'll just wait until I have 8+ hours to get this done".  and of course... it may never install any of them.  So be careful in deciding whether or not this is a potentially BAD idea, for your environment.  Or at least be aware of the potential repercussions, so you know what to un-do.

What this sql does, is list for Updates released in the last 30 days, and content has been downloaded, kind of look at the maxexecutiontime set, vs. how big the content is.  and if, for example, the content size is between 50 and 100mb, but it's maxexecutiontime isn't 20 minutes or more, then maybe you the admin might want to think about making MaxExecutionTime on that specific update to be 20 minutes--so you don't get false "I failed to install" reports which a re-scan will address.

Again... this isn't perfect.  It's just a possible suggestion, if you maybe have seen this behavior in your Software Updates deployments, and were wondering if there was a way to be pro-active about increasing the MaxExecutionTime without waiting for your reports to tell you the next day.

DECLARE @StartDate datetime = DateADD(Day, -30, GETDATE())
DECLARE @EndDate datetime = GetDate()

;with cte as (select ui.MaxExecutionTime/60 [Max ExecutionTime in Minutes], ui.articleid, ui.title, ui.DateLastModified, ui.DatePosted
,ui.IsSuperseded, ui.IsExpired
,(SUM(files.FileSize)/1024)/1 as [Size in KB]
,(SUM(files.FileSize)/1024/1024)/1 as [Size in MB]
from v_updateinfo ui
join v_UpdateContents content on content.CI_ID=ui.CI_ID
join vCI_ContentFiles files on files.Content_ID=content.Content_ID
where severity is not null
and content.ContentProvisioned = 1
and ui.dateposted between @StartDate and @EndDate
and ui.IsExpired = 0
group by ui.MaxExecutionTime, ui.articleid, ui.title, ui.DateLastModified, ui.dateposted, ui.IsSuperseded, ui.IsExpired
)

select
Case when cte.[Size in MB] < 50 and cte.[Max ExecutionTime in Minutes] >= 10 then 0
when cte.[Size in MB] BETWEEN 50 and 100 and cte.[Max ExecutionTime in Minutes] >= 20 then 0
when cte.[Size in MB] between 100 and 150 and cte.[Max ExecutionTime in Minutes] >= 30 then 0
when cte.[Size in MB] between 150 and 200 and cte.[Max ExecutionTime in Minutes] >= 40 then 0
when cte.[Size in MB] between 200 and 250 and cte.[Max ExecutionTime in Minutes] >= 50 then 0
when cte.[Size in MB] between 250 and 300 and cte.[Max ExecutionTime in Minutes] >= 60 then 0
when cte.[Size in MB] > 300 and cte.[Max ExecutionTime in Minutes] >=90 then 0
else 1
End as [Could use MaxExecutionTime Adjustment],
case when cte.[Size in MB] < 50 then '10 minutes'
when cte.[Size in MB] BETWEEN 50 and 100 then '20 minutes'
when cte.[Size in MB] between 100 and 150 then '30 minutes'
when cte.[Size in MB] between 150 and 200 then '40 minutes'
when cte.[Size in MB] between 200 and 250 then '50 minutes'
when cte.[Size in MB] between 250 and 300 then '60 minutes'
when cte.[Size in MB] > 300 then '90 minutes'
end as 'time to set'
, cte.*

from cte
order by [Could use MaxExecutionTime Adjustment] desc, [Time to set] desc

CMCB, SQL

  • Created on .

Create ConfigMgr Powershell Configuration Items using Powershell

As part of a presentation for the 2019 Midwest Management Summit in Minneapolis, one of the sessions I'm presenting with Jeff Bolduan is Configuration Items.  As part of that session, we'll be demoing using a PowerShell Script to create PowerShell-based Configuration Item.
 
If you want to see how that works (at least it works in my lab) --> Here <-- is the script for creating a Configuration Item with multiple tests inside, where the CIs are posh-based detection, applicability, and remediation scripts.  For demo purposes, I grabbed the scripts from the blog --> about WSUS Administration/WSUSPool <-- settings enforcement via Configuration Items, and got them all working to be made into 1 Configuration Item, with multiple rules.
 
Hopefully for those of you who are looking to create your own re-producible PowerShell code for creating posh-based CIs, the attached example posh will give you an idea of how you might want to get that done.

CMCB, PowerShell

  • Created on .

ConfigMgr Truncate History Tables

Thanks very much to Umair Khan, Twitter @TheFrankUK, for the assist!  One of the hiccups recently was making sure to exclude "globaldata" type HIST tables, so that DRS replication doesn't want to go into MAINTENANCE_MODE and re-initialize global data.

Read more: ConfigMgr Truncate History Tables

  • Created on .
Copyright © 2019 - The Twin Cities Systems Management User Group