Tip: Gathering history log information on user actions in database

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.

Dear future anon finding this post.
Please note that we strongly advise against direct actions within the database of Zammad.

While reading may not be of an issue, direct writing / changing may end in unexpected results, incomplete/missing data or even corrupt data (due to you ignoring Zammads validations).

This may cause serious issues / downtimes / bricks or -if very unlucky- security risks.

1 Like

Yes, that would’ve probably been smart to include in my original post: we did not change anything, this was only for retrieving log data to be handed over to forensics, not for fixing or altering it. Also, we were very glad this data existed and was easy to collect. Huge thanks to the Zammad team for being security-conscious and thinking ahead.

1 Like