How is 'Changed by' determined using 'View Change Log'?

[Kate T]

I need to run regular sql queries for internal reporting that reproduce the same granular visibility into the life history of a Case (not the History of the case).

I'm interested in Cases primarily, but would like to understand how it is done in general.

An example use-case:

The status of dozen Cases change from New -> Assigned -> Closed -> Pending -> Closed, and I'd like to run a query that will output the datetime and user for each closure, among other things.

The only place I've seen such information presented is via the Cases 'View Change Log' UI.

Thanks in advance,

Kate

[Kate T]

This is my cases_audit table:

+---------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| id | char(36) | NO | | NULL | |
| parent_id | char(36) | NO | | NULL | |
| date_created | datetime | YES | | NULL | |
| created_by | varchar(36) | YES | | NULL | |
| field_name | varchar(100) | YES | | NULL | |
| data_type | varchar(100) | YES | | NULL | |
| before_value_string | varchar(255) | YES | | NULL | |
| after_value_string | varchar(255) | YES | | NULL | |
| before_value_text | text | YES | | NULL | |
| after_value_text | text | YES | | NULL | |
+---------------------+--------------+------+-----+---------+-------+

As there is no "modified_by" included in this table, I'm still at a loss for how it is inferred/deduced for each event documented in the 'view change log' window. What am I missing? I'm assuming I need the right subquery but I don't know how to nest it.

[Kate T]

Bumping this, as we are still trying to come up with SQL queries that will allow us to report this information for multiple cases at once.

Is there a transaction number or something similar that is included the table above as well as another table that does include the name of the user that initiated the transaction?