Used Zammad installation source: (source, package, …) package
Operating system: centos
Forgive me if this is still a feature request, but I’m wondering if anyone has been successful at getting more granular with the Base DN, in a update friendly way. We have 461,718 users in LDAP and growing everyday. This has been the one thing that bothers me about Zammad, housing all those users in the database, chewing up disk space and the impact on performance. I only need around a 100 of those users. A simple LDAP query for authentication would be so much nicer than sync…
Anyone have any tricks or will there be more options in the near future?
I’ve found these older posts but checking if there’s anything else or updates.
No idea about the base DN, but might a user filter help? For example, I’m using (&(objectClass=posixAccount)(givenName=*)) which causes Zammad only to import users that have a first name. (That’s admittedly a bit of a hack; I rather wanted to filter by group membership, but our LDAP is a bit crappy and doesn’t have memberOf nor useful user attributes, and filtering by first name was the next best thing. You might have more luck depending on your LDAP structure…?)
So due to my poor initial LDAP config (not Zammads fault! although a LDAP query would be better imo than sync) I have close to 500,000 unwanted users…not sure if expert mode was available when we rolled out Zammad a couple years ago…anyways hoping the delete feature comes out soon since we hoped it would have been released at the end of 2018.
System is back to normal now I had to manually stop the ldap sync each time it would get stuck, and restart the zammad service until the job count got down to zero, then the ldap sync finished successfully.
I tried the code in my test zammad to removed users but it failed after a few deletes. Might just wait for an official delete feature, any idea how much longer?
Thanks Zammad team and community, y’all are saints!
I prefer being on point hehe. But your error message is quite interesting, it indicates trouble with a tag which is unusual.
I will provide all codes that should ensure there is no foreign key left.
In the above case the last line should be relevant. As you don’t have a mail address there, you can just set u_id=438 (in your special case as this is the bad ID of the affected user)
No luck, I tried both email and u_id=438…maybe operator error on my part?
Loading production environment (Rails 5.1.6.2)
irb(main):001:0> u_id=438
=> 438
irb(main):002:0>
irb(main):003:0* ActivityStream.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):004:0> History.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):005:0> Ticket::Article.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):006:0> Ticket::Article.where(updated_by_id: u_id).update_all(updated_by_id: 1)
=> 0
irb(main):007:0> Store.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):008:0> StatsStore.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):009:0> OnlineNotification.find_by(user_id: u_id).destroy!
=> #<OnlineNotification id: 7840, o_id: 475, object_lookup_id: 2, type_lookup_id: 2, user_id: 438, seen: false, updated_by_id: 23, created_by_id: 23, created_at: "2018-08-21 21:28:00", updated_at: "2018-08-21 21:28:00">
irb(main):010:0> Tag.where(created_by_id: u_id).update_all(created_by_id: 1)
=> 0
irb(main):011:0> User.joins(roles: :permissions).where(active: false, roles: { active: true }, permissions: { name: 'ticket.customer', active: true }).where.not(id: 1).find_each do |user|
irb(main):012:1* puts "Customer #{user.login}/#{user.email} has #{Ticket.where(customer_id: user.id).count} tickets"
irb(main):013:1>
irb(main):014:1* Ticket.where(customer: user).find_each do |ticket|
irb(main):015:2* puts " Deleting ticket #{ticket.number}..."
irb(main):016:2> ticket.destroy
irb(main):017:2> end
irb(main):018:1>
irb(main):019:1* puts " Deleting user #{user.login}/#{user.email}..."
irb(main):020:1> user.destroy
irb(main):021:1> end
Customer xxxxxxx/xxxxxxx@wsu.edu has 0 tickets
Deleting user xxxxxxx/xxxxxxx@wsu.edu...
Group.find(1) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: c3e9177c-00fb-4f37-bad8-8f0fbd923179) to DelayedJob(default) with arguments: "User", 438
Group.find(2) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 7084264c-63c7-442b-9898-cca98c49d49d) to DelayedJob(default) with arguments: "User", 438
Group.find(3) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 97119b63-6ec4-427f-ba59-897de669d174) to DelayedJob(default) with arguments: "User", 438
Group.find(4) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: f7bd7467-e9af-46ed-8e52-446d78908ba7) to DelayedJob(default) with arguments: "User", 438
Group.find(5) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 18111a74-6972-4c3e-850b-1753f8f3507f) to DelayedJob(default) with arguments: "User", 438
Group.find(6) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: ddb57887-cb3e-4d39-a7cb-2afcea370c97) to DelayedJob(default) with arguments: "User", 438
Group.find(7) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 756e7e37-28d5-4de0-90d0-f4f03fa61bc0) to DelayedJob(default) with arguments: "User", 438
Group.find(8) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: b4613a38-c9cc-487b-a9f8-ee645e28adb4) to DelayedJob(default) with arguments: "User", 438
Group.find(9) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: f8794f13-2826-4582-80e1-368130a9f4ad) to DelayedJob(default) with arguments: "User", 438
Group.find(10) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 83bed5dc-293b-4705-bdbc-3d61768ae900) to DelayedJob(default) with arguments: "User", 438
Group.find(11) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 6b777c8f-9e1b-45ab-b799-3b4eea15ecc8) to DelayedJob(default) with arguments: "User", 438
Group.find(12) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: ec11468b-474f-4691-950c-2cd11d56e6d5) to DelayedJob(default) with arguments: "User", 438
Group.find(13) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 496ab84b-c2f3-4f95-a7d8-0f7a60555709) to DelayedJob(default) with arguments: "User", 438
Group.find(14) notify TOUCH 2019-04-09 19:42:41 UTC
User.find(438) notify TOUCH 2019-04-09 19:42:41 UTC
Enqueued SearchIndexJob (Job ID: 02a7a455-0496-4420-98b7-33682b687cba) to DelayedJob(default) with arguments: "User", 438
# curl -X DELETE "http://127.0.0.1:9200/zammad_production/StatsStore/6?pipeline=zammad-attachment"
# 400
ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR: update or delete on table "users" violates foreign key constraint "fk_rails_939b990649" on table "tickets"
DETAIL: Key (id)=(438) is still referenced from table "tickets".
: DELETE FROM "users" WHERE "users"."id" = $1
from (irb):20:in `block in irb_binding'
from (irb):11
irb(main):022:0>
This person was an agent at one time, now a inactive customer, but still shows them as part of a group. Their tickets have been deleted. Other inactive customers (never part of any group besides customers) were deleting ok.
It’s possible that user has a left over Ticket or article somewhere. You can check that by:
Ticket.where(updated_by_id:438).pluck(:id) # Gives all Ticket-IDs which have been updated by offending user)
Ticket.where(created_by_id:438).pluck(:id) # Just double check, there shouldn't be any
Ticket::Article.where(updated_by_id:438).pluck(:id) # Gives you all Articles affected (if any)
Ticket::Article.where(created_by_id:438).pluck(:id) # Maybe the user somewhere created an article, gives us the articles
So… to the destructive part. If you find Tickets or Articles anywhere. You can either add .destroy to remove the entry completely or .update(created_by_id:1) to reset to “System”. created_by_id may need to be replaced with updated_by_id depending on how you found it.
Please note that deleting might be the better way because of references and stuff. If you have an article or ticket that’s important, you might want to assign it to another agent via rails.
I had to manually stop the ldap sync each time it would get stuck, and restart the zammad service until the job count got down to zero, then the ldap sync finished successfully.
