Automatic archival of users based on LDAP/AD disabled status
I saw this article here: https://www.itninja.com/blog/view/kace-sma-automatation-of-userarchiva
And that MySQL code looks like a mess. I don't want to run it on our server. Quite a bit of it seems completely superfluous, so I wanted to rewrite it.
FIELD_ID is custom field 4. This is supposed to check field 4 for a trailing 2 in hex, which should indicate the UserAccountControl imported from LDAP is set to disabled. LDAP Custom 4 mapped to LDAP UserAccountControl
UPDATE USER AS USR1,
(SELECT uID.ID FROM USER uID WHERE uID.USER_NAME = 'admin') AS USR2,
(SELECT uFV.USER_ID FROM USER_FIELD_VALUE uFV WHERE uFV.FIELD_ID = '4' AND RIGHT(HEX(uFV.FIELD_VALUE), 1) = '2') AS USR3
SET USR1.MODIFIED = now(), USR1.IS_ARCHIVED = '1', USR1.ARCHIVED_DATE = now(), USR1.ARCHIVED_BY = USR2.ID
WHERE USR1.ID = USR3.USER_ID AND USR1.IS_ARCHIVED = '0';
I was hoping someone would be willing to take a look and see if there are any obvious problems with it. I plan on running it against a copy of the database first.
SQL Workbench won't even simulate commands against the server because the R1 account doesn't have read access.
I'm thinking archiving might not be a good idea. Docs say that importing a user who is archived will create a new user. I'm thinking that having it revoke all permissions to the user instead would be the better way to go.