MEMCM IIS Settings you may want for your Management Points and Distribution Points

Over the years we've uncovered various iis settings for our Management Points and Distribution Points, which we've found needed tweaking (for a company our size and complexity). Perhaps none of these settings will be relevant in your environment. If you have some issues with your clients' ability to communicate to IIS these settings may be a starting point for your troubleshooting or remediation of your MPs and DPs.

These are all Configuration Items.
1 Test and Remediation for any ManagementPoint role servers
4 Test and Remediation for any DistributionPoint role servers

Since I've been told that trying to import an exported .cab of these CIs often fails, I'm instead going to list out every setting and script inside, instead of trying to make it "easy" by offering a .cab for import.

You'll want to make all of these CIs "Application" Type CIs. That is so that you can add all 5 rules to a baseline, and target the baseline to a collection of "all your CM Servers", without having to break up and maintain collections for "these are the MP server", and "these are the DP servers". Let the CI do the "should I bother" check, using the application detection logic.


Management Point ones--you only want your servers with the MEMCM Management Point role to deserve this CI. This is what I currently have as the application detection logic:


<#
.SYNOPSIS
This is to check if the server has a MP role
#>

Param (
$VerbosePreference = 'SilentlyContinue',
$ErrorActionPreference = 'SilentlyContinue'
)
$Value = (get-itemproperty 'HKLM:\software\Microsoft\sms\mp' | Select IISPortsList).IISPortsList
if (-not ([string]::IsNullOrEmpty($Value))) {
write-host $Value
}

Distribution Point Ones--you only want your servers with the MEMCM DP role and IIS to deserve these 4 CIs. This is what I currently have as the application detection logic for the 4 CIs for the DP ones:

 


<#
.SYNOPSIS
This is to check if the server has a DP role
#>

Param (
$VerbosePreference = 'SilentlyContinue',
$ErrorActionPreference = 'SilentlyContinue',
$WebServerInstalled = (Get-WindowsFeature -Name Web-Server).InstallState
)
$Value = (get-itemproperty 'HKLM:\software\Microsoft\sms\dp' | Select ContentLibraryPath).ContentLibraryPath
if (-not ([string]::IsNullOrEmpty($Value)) -and ($WebServerInstalled -eq 'Installed')) {
write-host $Value
}


For each individual CI...
The single Management Point role CI is this:

  1. applicationPoolDefaults queueLength should be 4000
    Script, Integer
    Why is this needed? IIS default out of the box is 1000. MEMCM supports 4000. the reason you want the max is if you have a lot of clients (more than 1000) all trying to communicate to the server, the machines over 1000 may get communication failures. This can result in clients not able to download policy, nor able to transmit information to the Management Point.
    1. Discovery Script:
      import-Module webadministration
      (Get-WebConfiguration /system.applicationHost/applicationPools/applicationPoolDefaults).queueLength
    2. Remediation Script
      import-Module webadministration
      Set-WebConfigurationProperty /system.applicationHost/applicationPools/applicationPoolDefaults -Name queueLength -value 4000
    3. Compliance Rule is that this will be an Integer of 4000
      1. Make sure you check that box about 'Run the specified remediation script when this setting is noncompliant' (if you forget, then even if you deploy the baseline w/remediation, it still won't remediate)

So... that was the easy one; just the MP role one; to allow for more clients to chat. Distribution Point IIS settings; we've had to tweak multiple things over the years. The following 4 things are for your DP Role Servers.  The next 4 CIs would be ones you create using "application detection logic" of a DP role (mentioned above)

  1. applicationPoolDefaults queueLength should be 4000
    Script, Integer
    Why is this needed? IIS default out of the box is 1000. MEMCM supports 4000. the reason you want the max is if you have a lot of clients (more than 1000) all trying to communicate to the server, the machines over 1000 may get communication failures. This can result in clients not able to download content.
    1. Discovery Script:
      import-Module webadministration
      (Get-WebConfiguration /system.applicationHost/applicationPools/applicationPoolDefaults).queueLength
    2. Remediation Script
      import-Module webadministration
      Set-WebConfigurationProperty /system.applicationHost/applicationPools/applicationPoolDefaults -Name queueLength -value 4000
    3. Compliance Rule is that this will be an Integer of 4000
      1. Make sure you check that box about 'Run the specified remediation script when this setting is noncompliant' (if you forget, then even if you deploy the baseline w/remediation, it still won't remediate)
  2. SMS Distribution Points Pool appConcurrentRequestLimit should be 65535
    Script, Integer
    Why is this needed? If it's not max allowed, what could happen is 503.2 IIS errors on the Distribution Points, this alleviates those errors.
    1. Discovery Script
      <#
      .SYNOPSIS
      Query applicationHost.config, <configuration> , <system.webServer>,
      change <serverRuntime /> for appConcurrentRequestLimit
      .DESCRIPTION
      Query applicationhost.config, <configuration> <system.webServer>, <serverRuntime />
      .NOTES
      Why: Part of alleviate the 503.2 IIS errors on the Management Points
      2019-12-05 Sherry Kissinger
      .EXAMPLES
      #>
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      Import-Module WebAdministration
      (Get-WebConfigurationProperty -pspath 'MACHINE/WEBROOT/APPHOST' -filter "system.webServer/serverRuntime" -name "appConcurrentRequestLimit").Value
    2. Remediation Script
      <#
      .SYNOPSIS
      Edit applicationHost.config, <configuration> , <system.webServer>,
      change <serverRuntime />
      to <serverRuntime appConcurrentRequestLimit="65535" />
      .DESCRIPTION
      Modify applicationhost.config, <configuration> <system.webServer>, <serverRuntime />
      .NOTES
      Why: alleviate the 503.2 IIS errors on the Management Points
      2019-12-05 Sherry Kissinger
      .EXAMPLES
      #>
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      Import-Module WebAdministration
      Set-WebConfigurationProperty -pspath 'MACHINE/WEBROOT/APPHOST' -filter "system.webServer/serverRuntime" -name "appConcurrentRequestLimit" -value 65535
    3. what means compliant: 65535
      1. Make sure you check that box about 'Run the specified remediation script when this setting is noncompliant' (if you forget, then even if you deploy the baseline w/remediation, it still won't remediate)
  3. SMS Distribution Points Pool RapidFail Should be Disabled
    Script, String
    why is this needed? iis defaults to Stopping (and not restarting) Application Pools if "too many" errors are encountered. Well, in an environment our size... we get errors all the time. We'd rather client keep trying to communicate, even if it generates iiserrors. We certainly don't want the application pools to stop.
    1. Discovery Script
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      import-Module webadministration
      (get-itemproperty 'IIS:\AppPools\SMS Distribution Points Pool' -name failure.rapidFailProtection).Value
    2. Remediation Script
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      import-Module webadministration
      set-Itemproperty 'IIS:\AppPools\SMS Distribution Points Pool' -name failure.rapidFailProtection False
    3. what means compliant, the returned value = False
      1. Make sure you check that box about 'Run the specified remediation script when this setting is noncompliant' (if you forget, then even if you deploy the baseline w/remediation, it still won't remediate)
  4. SMS Distribution Points No FileExtensionFilters
    Script, Integer
    why is this needed? by default, IIS will filter some file extensions. For us, occasionally files within content attempting to be downloaded would include files with those exact extensions, like a .mdb or .vb or .config, etc. etc. This would result in the client claiming "hash mismatch", because quite correctly IIS had a Request Filtering rule denying the ability to download a file from IIS ending in .mdb / .vb / whatever. But... we *DO* need the ability for files of that type to be downloaded into cache; if that is what is in the source files for an application, that is what we need to support. This will remove all fileextension filters, if there is a DP role.
    1. Discovery Script
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      import-Module webadministration
      $CountFileExtensionFilters = (Get-WebConfigurationProperty -Filter 'System.WebServer/Security/requestFiltering/fileExtensions' -PSPath 'IIS:\Sites\Default Web Site' -Name 'Collection' | Measure-Object).Count
      Write-Host $CountFileExtensionFilters
    2. Remediation Script
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      import-Module webadministration
      Remove-WebConfigurationProperty -Filter 'System.WebServer/Security/requestFiltering/fileExtensions' -PSPath 'IIS:\Sites\Default Web Site' -Name 'Collection'
    3. what means compliant, equals  0
      1. Make sure you check that box about 'Run the specified remediation script when this setting is noncompliant' (if you forget, then even if you deploy the baseline w/remediation, it still won't remediate)

 

Then of course.. TEST TEST TEST.

Add these 5 new CIs to a Baseline, and deploy to a single server with one of the roles; and see "what would happen if...".  If you are satisfied it might be helpful, you can then delete the deployment, and redeploy "with remediation", and test again.

 

CMCB

  • Created on .

Reporting on Attached Monitor info as available in WMIMonitorID

I thought this information was already blogged by someone else--I certainly know I stole it from someone else years ago. But now I can't find that blog. If this is your work; please accept my apologies for not crediting you correctly.

Reporting on "Attached Monitors" is occasionally something which your business requests. The best solution in my humble opinion is from Enhansoft.com, part of their Reporting suite includes a client-deployed utility for exhaustively being able to report on attached monitor information https://www.enhansoft.com/products-services/enhansoft-reporting/ 

However, if you don't have any budget at all; but are still tasked with getting "attached monitor information", although it's a poor second, you can get 'some information' out of a built-in WMI class. https://docs.microsoft.com/en-us/windows/win32/wmicoreprov/wmimonitorid 

Step 1: In your CM Console, Administration, Client Settings, right-click on Default Client Settings, select Properties. Go to the "hardware inventory" on the left, then "Set Classes..." on the right. Choose "Add..." and you want to connect to <some computer you have admin rights on> root\wmi (not root\cimv2), and find "WMIMonitoID". Add that. hit OK til out. Monitor <your server>\logs\dataldr.log; to see it being created, and the view created. Take note of the view which was created in your environment.

Step 2: Wait. You are waiting for clients to get this new policy, and then report hardware inventory using this new policy. Depending upon your environment, this could be minutes to hours to even a week--only you know your own environment and timings.

Step 3: Below is sql code to pull out the information from the 'not too friendly' info in that wmi class. Just make sure you replace the v_gs_wmimonitorID0 view with what it REALLY is for your environment. Your environment might not have called the view v_gs_wmimonitorID0; again; that could be unique to your environment.

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

--###############################################
--Create #Temp Table, and insert specific data
--Data will be used later in the report
--##############################################

create table #TempMonInfo(
ResourceID int,
UserFriendlyName0 nvarchar(255),
UserFriendlyNameLength0 int,
UserFriendlyNameConv varchar(255),
ManufacturerName0 nvarchar(255),
ProductCodeID0 nvarchar(255),
SerialNumberID0 nvarchar(255),
WeekOfManufacture0 int,
YearOfManufacture0 int
)

insert Into #TempMonInfo
(ResourceID, ManufacturerName0, ProductCodeID0, SerialNumberID0, WeekOfManufacture0, YearOfManufacture0,
UserFriendlyName0, UserFriendlyNameLength0)
select
ResourceID, ManufacturerName0, ProductCodeID0, SerialNumberID0, WeekOfManufacture0, YearOfManufacture0,
UserFriendlyName0, UserFriendlyNameLength0
from v_GS_WMIMonitorID0

;WITH n AS
(
SELECT
NUMBER = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1, sys.all_objects AS s2
)
, final as (
SELECT
MON.ResourceID,
MON.UserFriendlyName0,
CONV_FN.VAL AS UserFriendlyNameConverted,
MON.ManufacturerName0 AS [Make],
CONV_MAKE.VAL AS MakeConverted,
MON.ProductCodeID0 AS [ProductCode],
MON.SerialNumberID0 AS [SerNum],
CONV_SN.VAL AS SerNumConverted,
MON.YearOfManufacture0 AS [YearOfManufacture],
MON.WeekOfManufacture0 AS [WeekOfManufacture]
FROM #TempMonInfo MON
CROSS APPLY ( SELECT
CASE
WHEN UserFriendlyName0 LIKE '%,%'
THEN (SELECT CHAR([value]) FROM (SELECT [Value] = SUBSTRING(MON.UserFriendlyName0, [Number],CHARINDEX(',', MON.UserFriendlyName0 + ',', [Number]) - [Number]) FROM n WHERE Number <= LEN(MON.UserFriendlyName0) AND SUBSTRING(',' + MON.UserFriendlyName0, [Number], 1) = ',') SPLT WHERE [value] > 20 FOR XML PATH(''),TYPE).value('.','varchar(max)')
ELSE UserFriendlyName0
END AS VAL) CONV_FN
CROSS APPLY ( SELECT
CASE
WHEN MON.ManufacturerName0 LIKE '%,%'
THEN (SELECT CHAR([value]) FROM (SELECT [Value] = SUBSTRING(MON.ManufacturerName0, [Number] ,CHARINDEX(',', MON.ManufacturerName0 + ',', [Number]) - [Number]) FROM n WHERE Number <= LEN(MON.ManufacturerName0) AND SUBSTRING(',' + MON.ManufacturerName0, [Number], 1) = ',') SPLT WHERE [value] > 20 FOR XML PATH(''),TYPE).value('.','varchar(max)')
ELSE MON.ManufacturerName0
END AS VAL) CONV_MAKE
CROSS APPLY ( SELECT
CASE
WHEN MON.SerialNumberID0 LIKE '%,%'
THEN (SELECT CHAR([value]) FROM (SELECT [Value] = SUBSTRING(MON.SerialNumberID0, [Number],CHARINDEX(',', MON.SerialNumberID0 + ',', [Number]) - [Number]) FROM n WHERE Number <= LEN(MON.SerialNumberID0) AND SUBSTRING(',' + MON.SerialNumberID0, [Number], 1) = ',') SPLT WHERE [value] > 20 FOR XML PATH(''),TYPE).value('.','varchar(max)')
ELSE MON.SerialNumberID0
END AS VAL) CONV_SN
)

Select
s1.Netbios_Name0 as 'Computername',
final.resourceid,
final.makeconverted
, UserFriendlyNameConverted
, final.ProductCode,
final.SerNumConverted, final.WeekOfManufacture, final.YearOfManufacture
,case when makeconverted = 'aaa' then 'Asus'
when makeconverted= 'ACI' then 'Asus'
when makeconverted= 'ACR' then 'Acer'
when makeconverted= 'APP' then 'Apple'
when makeconverted= 'ATL' then 'Atlona'
when makeconverted= 'BBY' then 'Insignia'
when makeconverted= 'BNQ' then 'Benq'
when makeconverted= 'CPQ' then 'Compaq'
when makeconverted= 'DCL' then 'DCLCD'
when makeconverted= 'DEL' then 'Dell'
when makeconverted= 'ELE' then 'Element'
when makeconverted= 'ELM' then 'Doublesight'
when makeconverted= 'EMA' then 'eMachines'
when makeconverted= 'ENC' then 'Eizo'
when makeconverted= 'EPI' then 'Envision'
WHEN makeconverted= 'FNI' then 'FUNAI/SYLVANIA'
when makeconverted= 'GSM' then 'LG'
when makeconverted= 'GWY' then 'Gateway'
when makeconverted= 'HKC' then 'V7'
when makeconverted= 'HPN' then 'HP'
when makeconverted= 'HRE' then 'Haier'
when makeconverted= 'ACR' then 'Acer'
when makeconverted= 'HSD' then 'Hanns.G'
when makeconverted= 'ACR' then 'Acer'
when makeconverted= 'HSP' then 'Hannspree'
when makeconverted= 'HTC' then 'Hitachi'
when makeconverted= 'HWP' then 'HP'
when makeconverted= 'IFS' then 'Infocus'
when makeconverted= 'IZI' then 'Vizio'
when makeconverted= 'LEN' then 'Lenovo'
when makeconverted= 'MED' then 'Medion'
when makeconverted= 'MEL' then 'NEC/Mitsubishi'
when makeconverted= 'NOK' then 'Nokia'
when makeconverted= 'PGS' then 'Princeton'
when makeconverted= 'PHL' then 'Philips'
when makeconverted= 'PLN' then 'Planar'
when makeconverted= 'PNR' then 'Planar'
when makeconverted= 'PTS' then 'Proview'
when makeconverted= 'SAM' then 'Samsung'
when makeconverted= 'SEK' then 'Seiki'
when makeconverted= 'SHP' then 'Sharp'
when makeconverted= 'SNY' then 'Sony'
when makeconverted= 'SPT' then 'Sceptre'
when makeconverted= 'SYN' then 'Olevia'
when makeconverted= 'TSB' then 'Toshiba'
when makeconverted= 'UPS' then 'Upstar'
when makeconverted= 'VIZ' then 'Vizio'
when makeconverted= 'VSC' then 'ViewSonic'
when makeconverted= 'WDE' then 'Westinghouse'
when makeconverted= 'WDT' then 'Westinghouse'
when makeconverted= 'WET' then 'Westinghouse'
else MakeConverted
end as 'BestGuessMake'
from Final
join v_R_System_Valid s1 on s1.resourceid=final.resourceid
-- Filtering out some Makes known to not be relevant ... at least when this report was created years ago.
-- Comment out the next line if you want these things anyway.
and Final.Make not in ('AUO','BOE','SEC','SDC','LGD','CMN','64, 64, 64, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0','77, 83, 95, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0','88, 72, 64, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0')

If(OBJECT_ID('tempdb..#TempMonInfo') Is Not Null)
Begin
Drop Table #TempMonInfo
END

CMCB, SQL

  • Created on .

ConfigMgr Inventory of Powershell Versions

If you happen to be curious about what versions of Powershell are installed/available on your clients, here's one way to pull out the information.  Note that the regkey locations for some of this information has changed from version 2 to higher versions, so it's completely possible that a future update to Powershell and the regkey location will change again; so if that happens a modification to these .mof files will be necessary.  As of Windows 8.1; these worked to report versions of Powershell installed.

Take the --> attached <-- and inside are two .mof files.  If you are ConfigMgr 2012, place the contents of the 'posh-configuration.mof.txt' at the bottom of your <inbox location>\clifiles.src \hinv\configuration.mof file.  In your configMgr 2012 console, in Client Settings, Default Agent Settings, Hardware Inventory, Classes... Import the 'posh-to-be-imported.mof'

Wait for clients to start reporting, once you get some clients reporting, the below sql query should get you started:

;with CTE as (
  select distinct resourceid
   ,RTRIM(substring(ISNULL((select ','+PSCompatibleVersion0  
        from v_GS_PowerShell0 p1
        where p1.ResourceID=t2.resourceid for XML path ('')),' '),2,2000)) as PSCompatibleVersions0
   ,RTRIM(substring(ISNULL((select ','+PowerShellVersion0
        from v_GS_PowerShell0 p1  where p1.ResourceID=t2.resourceid for XML path ('')),' '),2,2000)) as PowerShellVersions0
   ,RTRIM(substring(ISNULL((select ','+RuntimeVersion0
        from v_GS_PowerShell0 p1  where p1.ResourceID=t2.resourceid for XML path ('')),' '),2,2000)) as RunTimeVersions0
 from v_R_System t2
)
   select distinct sys1.netbios_name0 [ComputerName]
 ,cte.RunTimeVersions0 [RunTime Versions]
 ,cte.PSCompatibleVersions0 [PS Compatible Versions]
 ,cte.PowerShellVersions0 [PowerShell Versions]
 from v_R_System sys1
 left join CTE on cte.ResourceID=sys1.ResourceID

 

 

  • Created on .

MEMCM Keep a System Group active without re- Group Discovery

Background for context:

I happen to work at a large company, which has more than 300,000 employees. Using Enterprise Client Management (MEMCM), we often deploy 'free' Software to the majority of users (think something like Adobe Reader, or Google Chrome). This is so that as soon as <new employee> logs into a workstation, they can go to Software Center, and install software they might need to perform their job.

How we accomplish this is all new users are added to a group called (for purposes of this blog) "SC_All_Employees".
That domain group is (used to be, until this workaround) discovered using Group Discovery. If you are unfamiliar with group discovery, in your MEMCM console, Administration, Hierachy Configuration, Discovery Methods, Active Directory Group Discovery, and in Discovery Scopes, is a single rule for this: Group, I had browsed for the group name, and it resolved to
Distinguished Name: CN=SC_All_Employees,CN=CompanyGroups,DC=MyCompany,DC=ORG
GroupName = SC_All_Employees
GroupType = Security Group - Global

The collection query (WQL) is this (selecting Usergroups, not users, when creating the collection query)
Select SMS_R_UserGroup.ResourceID
from SMS_R_UserGroup
Where
SMS_R_UserGroup.Unique_UserGroup_Name0 = "MyCompany\\SC_All_Employees"

This results in there being ONE and only ONE resourceid in the collection, the resourceid for the Group, not the resource ids for the users who might be in that group. (This is important)

Why do we like use / use this? Because it's all then based on one single thing being updated--Active Directory. Add a user to the group, that user authenticates to Active Directory, and the token for that ad group membership is attached to that login, and CM can tell and use that group SID to check if they deserve any policies...Policies that for us, result in things being available in Software Center. It can literally be a minute between adding a user in AD, the users locks/unlocks their workstations, the user launches Software Center, and voila, the stuff is visible. It's wicked fast--to the end user.



The Dilemma:

As of ECM current branch 2006 (and it has been this way for decades), when one discovers groups which happen to be Security Groups, it is impossible to NOT discover the users inside the group. If you watch your ADsgdis.log on your primary site, you'll see it discover the group...and then within a few minutes discover all the users in that group. That's fine if your strategy for collection creation is to have a collection query like this:

Select SMS_R_User.Resourceid from SMS_R_User
where SMS_R_user.UserGroupName = "MyCompany\\SC_All_Employees"

That's limiting to USERS, not USERGROUPS.

However, that isn't what we at this large company need or desire. Having to do delta discovery and have CM create the user to group relationships is not ideal at our size. So we don't even WANT to record the User-to-Group relationships in CM. We want just and only the group, group SID, and that one, single resourceid.

When CM has to discover all 300,000+ users in that group, and create those relationships, it causes replication delays, and backlogs in ddr processing. It's a strain on the system for literally no reason we want to have.

So you think; so what; just have it discover the group once, and then turn it off, it'll be there forever right? Nope; by design there is a task for "Delete Aged Discovery Records". So let's say you have that set to 90 days. If you turn off discovery of the group "SC_All_Employees", in 91 days that resourceid will be removed (by design, and in general that is a good thing), and you have to re-discover it again.

There is a uservoice for this; so until they fix it, if this is happening to you, please vote it up.

https://configurationmanager.uservoice.com/forums/300492-ideas/suggestions/11096859-ad-group-discovery-discovering-group-members


The Totally Unsupported and Do Not Do It Workaround (so if you do this, it's not my fault, I told you not to do this).

In 2 labs, and then production, this worked, to "keep alive" a Group...once it was discovered once; and NOT have it be automatically removed after the period you have defined for "Delete Aged Discovery Records".

If you have ANY hesitation about this at ALL, don't do it. Don't even think about doing it. If you think you might want to do this anyway, do this in your LAB environment first. Don't have a lab? Make one. There are several guides on making a CM lab using virtual machines.

SO... you decided to do this anyway, even though I said it's unsupported, and <insert deity here> help you if you mess something up... you have a backup of your environment, right?


1) *do* take the replication hit, and DDR processing hit once, for the group "SC_All_Employees" (insert your own group here, whatever it is).
2) remove that rule from Group Discovery.

3) Query to look at what the values are "now" (before you do any testing).

DECLARE @RID BIGINT = (Select Resourceid from v_r_userGroup ug where ug.Unique_Usergroup_Name0 like '%SC_All_Employees')
Select U.ResourceID, U.Name0, U.Creation_Date, U.Windows_NT_Domain0 from v_r_usergroup u where [email protected]
Select * from DiscItemAgents dia where [email protected]
Select * from DiscItemAgents_Local dial where [email protected]
Select count(fcm.collectionid) as 'Count of Collections where this group is a member'
from v_fullCollectionMembership fcm where [email protected]

4) Set up a SQL job to "keep alive" that specific group. You see... deep in sql is where CM records which discovered resourceids should be marked for deletion at the next Delete Aged Discovery Records routine. This circumvents that process.... by cheating SQL into thinking it *has* been recently discovered; and not to cull it.

The SQL Job runs on your primary site Server (that has the SQL database CM_..., and did the Group Discovery in step 1 above).
We currently have it run twice daily (likely only needs to run maybe weekly, but I was testing this routine)
and run it in the cm_ database (when you set up the job, you have to say which database)

The sql inside that job is below; note the DECLARE @RID; make sure you put in your correct group.  This blog might also put 'smart quotes' around things, or have line breaks where I didn't mean to have line breaks.  Remember the above warning where I said don't do this if you have any reservations?  Yeah... be careful what you do. Also note the double single quotes ( ' ' ) ; that's because of the sql job needing the double single quotes. If you are going to run this interactively for testing, you may need to remove one of the single quotes in each instance.
You may want to run this interactively against your cm_... database, for testing before making it a recurring sql agent job.


--Get ResourceID, current utc time, groupname for the log, and the current value of DueForAgeOut
DECLARE @RID BIGINT = (Select Resourceid from v_r_userGroup ug where ug.Unique_Usergroup_Name0 like ''%SC_All_Employees'')
DECLARE @NOW DATETIME = GETUTCDATE()
DECLARE @SiteCode nvarchar(3) = (Select Right(db_name(),3))
DECLARE @GroupToUpdate nvarchar(80) = (Select Unique_UserGroup_Name0 from v_r_usergroup where [email protected])
DECLARE @CurrentDueForAgeOut int = (Select DueForAgeOut from DiscItemAgents where [email protected] and [email protected])

--Update the _local with current utc date, and log
UPDATE [DiscItemAgents_Local]
SET AgentTime = @NOW
Where ITEMKEY = @RID
DECLARE @VALUE nvarchar(max) = (@GroupToUpdate + '' has been updated to '' + CAST(@NOW as varchar) + '' in the DiscItemAgents_Local Table.'')
RAISERROR (@VALUE,1,1) with LOG

--Depending upon if it''s currently Null or not, set DiscItemAgents to either Null, or 0 if already not-0. Values possible
--are Null, 0, or 1. 1 is the value which triggers deleting the record when the task for Delete Aged DDR records runs.

IF @CurrentDueForAgeOut IS Null
BEGIN
UPDATE [DiscItemAgents]
Set DueForAgeOut = NULL
, AgentTime = @NOW
Where ITEMKEY = @RID and [email protected]

DECLARE @VALUE2 nvarchar(max) = (@GroupToUpdate + '' has been updated in the DiscItemAgents Table with these values ''+ CAST(@NOW as varchar) + '', DueForAgeOut to NULL.'')
RAISERROR (@VALUE2,1,1) with LOG
END
ELSE
BEGIN
UPDATE [DiscItemAgents]
Set DueForAgeOut = 0
, AgentTime = @NOW
Where ITEMKEY = @RID and [email protected]

DECLARE @VALUE3 nvarchar(max) = (@GroupToUpdate + '' has been updated in the DiscItemAgents Table with these values ''+ CAST(@NOW as varchar) + '', DueForAgeOut to 0.'')
RAISERROR (@VALUE3,1,1) with LOG
END

 


5) Monitor the job's success by looking at your SQL logs (Using SQL Server Management Studio (SSMS), connect to your primary site server that houses your cm_ database, go to +Management, +SQL Server Logs, then double-click "Current", if you have the above running successfully, you'll see entries similar to this (the group name, and time will be different for your environment:

MyDomain\SC_All_Employees has been updated in the discItemAgents Table with these values Jan 25 2021 5:35PM, DueForAgeOut to Null
MyDomain\SC_All_Employees has been updated to Jan 25 2021 5:35PM in the DiscItemAgents_Local Table


6) PARANOIA STEPS

Make yourself reminders to check these; and confirm it's keeping it alive:

DECLARE @RID BIGINT = (Select Resourceid from v_r_userGroup ug where ug.Unique_Usergroup_Name0 like '%SC_All_Employees')
Select U.ResourceID, U.Name0, U.Creation_Date, U.Windows_NT_Domain0 from v_r_usergroup u where [email protected]
Select * from DiscItemAgents dia where [email protected]
Select * from DiscItemAgents_Local dial where [email protected]
Select count(fcm.collectionid) as 'Count of Collections where this group is a member'
from v_fullCollectionMembership fcm where [email protected]

What means a problem has happened?
If the group is just plain gone, and the 'Count of Collections where this group is a member' = 0

That means something deleted that group--whether it was a human literally going into the console, right-click and delete the group (oops!!!) or the Delete Aged Discovery Records cleared it out, you then have to decide... do you still need that group; or was it retired on purpose? If not retired on purpose, most likely you'll have to re-take the DDR hit, by re-discovering the group again in Group Discovery, and wait for your DDR backlog and/or replication backlog to clear after that; and check this routine works.

7) What if the Uservoice is addressed in a future version, and there is a way to NOT discover the members inside a security group?
- If so, create the Group Discovery for this group, and do whatever the guidance is to say "just the group please, not the members inside the group"
- Disable this sql Agent job--you don't need to run it ever again, if ECM Current Branch has it natively.  Could probably also just delete the sql job completely, if the product has it natively.

 

CMCB

  • Created on .

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 .
Copyright © 2021 - The Twin Cities Systems Management User Group