Infos:
- Used Zammad version: 4.1.x
- Used Zammad installation type: package
- Operating system: Ubuntu 20.04.3 LTS (Focal Fossa)
- Database: PostgreSQL 12.8
Summary
This deals with how to extract Zammad history logs/data regarding user actions from within the database.
Not really a question, since I’ve seem to have found what I’m looking for. It didn’t appear to be referenced in the documentation, so here’s possibly a tip for others searching for this very specific information.
Scenario
We’ve had to deal with a disgruntled ex-employee, who was laid off due to poor performance answering questions from the general public. That person, having an agent role in the helpdesk, decided it would be a good idea to take revenge upon one of the seniors responsible for their bad review, just before leaving. They used bulk actions to mass-close a lot of new/open and in-progress tickets and set the owner to the senior. Possibly hoping that negative responses from citizens (e.g. “Why haven’t you answered my question?”) would reflect poorly on that senior and/or cause negative publicity problems.
This might not be a huge issue in smaller setups, but not if you have thousands of new and/or open tickets, about 40 agents online and lots of daily activity, this could be a problem, as tickets tend to disappear from sight fairly soon after closing. However, this is likely a risk in most/many organizations, and something like this was bound to happen to us at some point.
How To
While searching for the Zammad history logs inside the database and tinkering with the resultset, the following query seemed to display all information regarding actions of that person after a specific moment in time. This allowed us to trace tickets, actions and timestamps and hand all the information over to the investigators dealing with the case.
You will obviously need interactive access to the database backend.
SELECT htype.name, hobs.name, hat.name, tick.number, related_history_object_id, id_to, id_from, value_from, value_to, hist.created_by_id, hist.created_at, hist.updated_at
FROM histories hist
LEFT JOIN history_objects hobs ON hist.history_object_id = hobs.id
LEFT JOIN history_types htype ON hist.history_type_id = htype.id
LEFT JOIN history_attributes hat ON hist.history_attribute_id = hat.id
LEFT JOIN tickets tick ON hist.o_id = tick.id
WHERE ( hist.created_by_id = 99999
AND hist.created_at >= '2021-10-08'
AND htype.name <> 'notification' );
Replace 99999 with the userid of the agent and the date which interests you, obviously. This will exclude all triggered ‘notification’ history type events.
Also, excuse my SQL grammar, as it is likely ugly and might benefit from some optimizations or improvements. I had to figure out the foreign key relationship on the tickets table, as this didn’t appear to be configured in the DDL.