Before you even consider this, please run the daily backup task of the kbox. THEN pull the backups via FTP. Do NOT rely on downloading the backups via the web browser. I highly recommend scripting a regular backup pull via ftp. Once you have the newest possible backup files, make a copy of them and then consider if you would like to try the cleanup.
Use at your own risk.
select statement:
SELECT USER.*
from USER
where (not exists(select 1 from HD_TICKET
where HD_TICKET.SUBMITTER_ID = USER.ID ))
AND USER.USER_NAME != 'Admin'
AND USER.USER_NAME != 'R1'
Update statement:
DELETE
USER.*
from USER
where (not exists(select 1 from HD_TICKET where HD_TICKET.SUBMITTER_ID = USER.ID ))
AND USER.USER_NAME != 'Admin'
AND USER.USER_NAME != 'R1'
After this runs, you can reimport LDAP users with your typical settings/schedule and will have effectively refreshed to the latest users in ldap at that time.
To only import active LDAP entries, consider the following filter:
(&(mail=KBOX_USER)(sAMAccountType=805306368)(!(userAccountControl=514)))
We are looking to do this on version 7.2 of the K1000 but we can't seem to get it working. We are running the select statement and it is coming back with an error. We also tried on version 8.1 and got the same error.
Were the ticket rules changed to not allow this since the date this was posted? Maybe something to do with having to specify a queue in the rule? - wimberlye 5 years ago