How can we clean up stale users on our K1000?
I'm hoping someone has a SQL statement to find stale user accounts in Kace.
Kace keeps the AD import time stamp and the last modified time stamp for the user's in the Kace DB, but I haven't figured out what the SQL code should look like to find them.
I'd like to start with finding users that have a last modified date greater than 3 months ago. If I can build a label or report to find them, I can manually delete them. But if there's also a method to include in the statement that would auto delete them after 3 months, that's great also.
We do not use the Service Desk module. I'd like to, but we're part of a large tiered orginization and we use at our tier while other tiers do not. The top tier provides their own ticketing system. That said, we don't and will never need to keep user accounts in Kace in order to reference old tickets.
Answers (1)
It looks like you can run something like this, and get a list.
SELECT USER_NAME, EMAIL, MODIFIED, CREATED FROM `USER` U WHERE MODIFIED < DATE_SUB(NOW(), INTERVAL 3 MONTH)
I think the problem is getting it into a list so you can easily delete them. I'm pretty sure you cannot turn that into a smart label. It may be better to create a LDAP label that queries an OU of "deactivated accounts". Then change the view to this label, and delete.
Comments:
-
Thanks. I'll test the code and also try the LDAP label query and report back. The LDAP query seems like it could be a more sound solution. - murbot 10 years ago
-
Update: That code works as expected. We're at 5.5 now and it still works. I haven't gone through all these steps yet, but so far I've exported to a CSV and sorted the stale accounts.
I'm waiting on approval to delete users by X months back. My plan is to create a new label by copying the usernames from the spreadsheet into SQL code in Kace. Then open that label and delete its content (the list of users).
This post has good info on how to setup the data in Excel before copying back into SQL code in Kace.
http://www.itninja.com/question/how-to-report-the-ou-that-users-are-in - murbot 10 years ago
