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 u.resourceid=@RID
Select * from DiscItemAgents dia where dia.ItemKey=@RID
Select * from DiscItemAgents_Local dial where dial.ItemKey=@RID
Select count(fcm.collectionid) as 'Count of Collections where this group is a member'
from v_fullCollectionMembership fcm where fcm.ResourceID=@RID

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 resourceid=@RID)
DECLARE @CurrentDueForAgeOut int = (Select DueForAgeOut from DiscItemAgents where itemKey=@RID and AgentSite=@SiteCode)

--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 AgentSite=@SiteCode

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 AgentSite=@SiteCode

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 u.resourceid=@RID
Select * from DiscItemAgents dia where dia.ItemKey=@RID
Select * from DiscItemAgents_Local dial where dial.ItemKey=@RID
Select count(fcm.collectionid) as 'Count of Collections where this group is a member'
from v_fullCollectionMembership fcm where fcm.ResourceID=@RID

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.