Large Amount of Disconnected Users in Windchill 12.0.2.12

[MR_10518716]

Before I became the Systems Admin for Windchill it was connected to an Active Directory without a filter and started creating junk accounts. I am not sure why or how, but we're no longer using that Domain so that is no longer happening. This is causing issues with the disconnected users search (it crashes) and some I can't find even using wildcards. I know they're still in there because I can run a command on the SQL Database to find them, but the search doesn't find them.

How can I remove these users who do not show up in the Disconnected Users Search? I have some that are clearly meeting rooms in our AD and Windchill created an account for them such as this {wt.org.WTUser:29111431}UKNEW_Room_04. This is how it actually shows up in the SQL Database in the WTUser table.

Also why is the disconnected users search so clunky? The SQL command pulls over 1600 in a second. I realize it will be slower through the UI, but it's so slow it breaks. I know I can increase the amount but it feels like it can barely handle the 50 that is default.

[mmeadows-3]

Removing disconnected participants won't help because the 'filter group' is missing. Every time you do an open or wildcard search for users or disconnected participants, it is searching the entire corporate LDAP and populating Windchill with everyone it finds.

Have IT re-create the 'Windchill filter group' in your corporate LDAP and add your Windchill users to it.  Re-implement the filter group in adapterservice.json and app-Windchill-AuthProvider.xml.  That will stop the insanity of cleanup, search, and cleanup again.

Identify if any of the disconnected participants have content to be cleaned up.  Given the number of disconnected participants, I would use QML reports to find disconnected or deleted users with workspaces, checked out objects, and open tasks.  For these users, clean up their workspaces, undo check outs, and reassign open tasks.

Once the accounts are clean, you can delete them in bulk.  Search for disconnected participants and delete them.  That should get you to a clean system.

[MR_10518716]

We already have the filter in place after we switched domains. I am trying to clean up the mess that was made. These accounts have never been assigned groups or roles or tasks. I have meeting rooms that have accounts and are disconnected. I also can't search for those because Windchill breaks when searching weird characters.

[mmeadows-3]

Ah, then you are down to the SQL level.  This can be dangerous territory, so proceed with caution and backups.

The primary risk is a mix of legitimately disabled users and the 1600 bogus users.  Meeting rooms, computers, etc. can be cleaned easily.  Use this article to recognize the relationships for user account entries.  https://www.ptc.com/en/support/article/CS94037

The relationships must be deleted in this order from these tables: OwningRepositoryLocalObject, RemoteObjectId, RemoteObjectInfo, and finally from WTUser. Not fun, but doable.  If you can track down the objects that cause the UI to fail, you can delete them first, then use the UI to delete the other accounts.

Notes:

Windchill does not delete the WTUser entry.  It alters the entry to keep a record of the deleted user.  If any of these were legitimate users, then update their WTUser entry instead of deleting it.

A disconnected participant has a repairNeeded value of 1.

A deleted participant has a disabled value of 1.

This is not the prettiest, but it works.

-- Validate there are entries to clean.

select idA3B5 from OwningRepositoryLocalObject where idA3B5=(select idA2A2 from WTUser where repairNeeded=1 and name not like '{%');
select idA2A2 from RemoteObjectId where idA2A2=(select remoteId from RemoteObjectInfo where idA3A3=(select idA2A2 from WTUser where repairNeeded=1 and name not like '{%'));
select idA3A3 from RemoteObjectInfo where idA3A3=(select idA2A2 from WTUser where repairNeeded=1 and name not like '{%');



-- Backup tables first

select * into WTUser_bak fromWTUser;
select * into OwningReposReRet_bak from OwningRepositoryLocalObject;
select * into RemoteObjectInfo_bak from RemoteObjectInfo;
select * into RemoteObjectId_bak from RemoteObjectId;


-- Bulk delete commands

delete from OwningRepositoryLocalObject where idA3B5=(select idA2A2 from WTUser where repairNeeded=1 and name not like '{%');
delete from RemoteObjectId where idA2A2=(select remoteId from RemoteObjectInfo where idA3A3=(select idA2A2 from WTUser where repairNeeded=1 and name not like '{%'));
delete from RemoteObjectInfo where idA3A3=(select idA2A2 from WTUser where repairNeeded=1 and name not like '{%');
update WTUser set name='{' + classnameA2A2 + ':' + idA2A2 + '}' + name, disabled=1, repairNeeded=0 where repairNeeded=1 and name not like '{%';

[mmeadows-3]

Did I mention to use caution?  The above queries will blow out everything, including legitimate participants that are currently disconnected.

Really, be careful, backup entries, and do it to a non-production environment first.

[Marco_Tosin]

To connect Windchill to the Active Directory we use two filters.

The first is the AD membership of users in the organization that corresponds to Windchill's organization name.

The second is the presence within a group in which only users who can access Windchill are placed.

This way when Windchill searches for a user to add to a license group, or other type of group, it is not searched through the entire tree of users in AD.

Second, and much more important, in this way it is also constrained who can log in and it is also impossible to attempt "inadvertent" access, for example, in the event that a user accidentally receives an e-mail containing a link requesting access to Windchill even though he or she should not.