User Table Cleanup and New User Query

[dbeckner]

Looking to understand any potential consequences for changing our user import query from MECM. 

First, our counts for users are listed below

WD_User table: 76582 records

Licenses used: 62383

I know there is a post somewhere that discusses how some cleanup procedures for the WD_User table I am interested in that process and i just cant find it right now.

Additionally, I want to make sure that if we update our user import query it wont impact the current users in the tool i.e. drop users that dont exist in that query anymore or something to that effect.

TIA

@CharlesW any thoughts on this?

[CharlesW · Accepted solution]

The following is an adequate KB on how App Broker determines the license count:

https://community.flexera.com/t5/App-Broker-Knowledge-Base/Understanding-How-App-Portal-Accounts-It-s-Licenses/ta-p/5998

You can cleanup old users (that are no longer in configMgr) by running the following query:

delete from wd_user

where LastUpdate <= DATEADD(day,-7,GETDATE())

AND UniqueName not in (select distinct UniqueUserName from WD_PackageRequests)

This will delete users who have not been updated in the last 7 days, but not delete users who are targets of old requests (so you do not lose any historical data)

If you change your user sync query, it will not touch the users who are already in WD_User.  App Broker does not clean up either WD_Computer or WD_User.