/build/static/layout/Breadcrumb_cap_w.png

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.


0 Comments   [ + ] Show comments

Answers (2)

Posted by: Hobbsy 2 years ago
Red Belt
0

Why don’t you copy the code into sql workbench and run it to see what results you get? That would confirm your SQL is correct

Posted by: BNewland 2 years ago
Yellow Belt
0

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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ