| SMA Version | Compatible |
| 10.0.290 | Yes |
Hi Guys,
just want to share a simple AddOn for our newest KACE SMA release 7.2 from last week. One of the new features is the UserArchival. Wouldn't it be nice to have this automated? I imagine that if you disable an account in you directory service the same account in KACE should be archived. And here is how it works:
First of all - this works only with Users imported from Windows Active Directory.
Step 1 - Prepering the Usertable
First we have to add a custom field to the SMA usertable. To do that please open a random user and click on "Customize Additional Fields".
Rename an existing custom field or add a new one. I renamed mine to UserAccountControl.
Step 2 - Getting the Data into the KACE
Now we have to edit our userimport to get the information whether we can archive a user or not. This will be decided by the attribute "userAccountControl".
These values are necessary to know:
- 512 - Enabled
- 514 - Disabled
- 66048 = Enabled, password never expires
- 66050 = Disabled, password never expires
Expand the imported attributes like shown below:
After you (or the system) have run the user import again you will see the userAccountControl values added to the users.
Step 3 - Create a ticket rule
This is the last step to get the automatic user archival feature running.
Create a new ticket rule directly with SQL and call it "automated user archival" (or something like this). Choose a priority that fits your environment. I would recommend to let this rule run at least once a day (a few minutes after your last userimport).
Select SQL Statement:
SELECT 'USER_ACHIVAL' as REASON;
Then only select the checkbox for "Run update query"
Update SQL:
UPDATE USER AS USR1, (SELECT uID.ID FROM USER uID WHERE uID.USER_NAME = 'admin') AS USR2, (SELECT uID2.ID
FROM (USER_FIELD_VALUE USER_FIELD_VALUE
INNER JOIN USER uID2 ON (USER_FIELD_VALUE.USER_ID = uID2.ID))
INNER JOIN USER_FIELD_DEFINITION USER_FIELD_DEFINITION
ON (USER_FIELD_VALUE.FIELD_ID = USER_FIELD_DEFINITION.ID)
WHERE ( USER_FIELD_VALUE.FIELD_VALUE = '514'
OR USER_FIELD_VALUE.FIELD_VALUE = '66050')) AS USR3
SET USR1.MODIFIED = now(), USR1.IS_ARCHIVED = '1', USR1.ARCHIVED_DATE = now(), USR1.ARCHIVED_BY = USR2.ID
WHERE USR1.ID = USR3.ID AND USR1.IS_ARCHIVED = '0';
Please note that you can change the Username in line 1 to a username you want to archive these users.
That's it! You now have an automated user archival based on the AD account status. Enjoy :)
Regards
Timo
sorry for the delay. I have tested it with 10.0 and it worked like a charme :)
Kind Regards
Timo - Timokirch 5 years ago
OR USER_FIELD_VALUE.FIELD_VALUE <> '66048')) AS USR3, but that archived all users. Any suggestions? Also, do we need to change the USER_FIELD_VALUE.FIELD or ID's in the SQL? - dhaysley 4 years ago