Users change log in RT database


#1

Hello everyone

As you know, you can assign users to some groups or remove them from groups or disable them in RT.
I want to have users change log. for example I want to know when user “X” was in Group “test” and when he was removed from that group or (if) when this user had been disabled.

I searched whole database and couldn’t find anything. does anyone know what can I do?


#2

In web interface, you can go to admin page of the group and you will see a link named “History”. Here you should find list of members added/removed with date and who made this.

This information comes from the “Transactions” table.

select * from Transactions where ObjectType='RT::Group' And ( Type = 'AddMember' or Type = 'DeleteMember' );

On a user admin page you also have a “History” link where you can see any changes to this user attributes. It comes also from the Transactions table:

select * from Transactions where ObjectType='RT::User' and ( Type='Enabled' or Type='Disabled' );


#3

thanks for your quick reply!

But I think I asked my question in a bad way. Imagine I have user “x” who is in group “test” from 2018/01/01 until 2018/02/15. this user was removed from group “test” and immediately assigned to group “IT” in 2018/03/9 and this user is still in “IT” group. is there anywhere in database for storing this kind of log?


#4

As I aid, membership changes are recorded in transactions table,but in from the group view, hence if you want to see groupmembership changes for a user, you have to use a query like:

select Transactions.Created,Transactions.Type,Groups.Name from Groups, Transactions where Groups.id= Transactions.ObjectId and Transactions.ObjectType='RT::Group' And (Transactions.Type = 'AddMember' or Transactions.Type = 'DeleteMember') and Transactions.Field='50';

For current user membership, you have to join between Users, Groups and GroupMembers or CachedGroupMembers (the first one is direct membership, the later contains deep membership when there is nested groups).


#5

Thank you but there is no record with Transactions.Type = ‘AddMember’ in transactions table!
ps: I use RT 4.4.1.


#6

Then you may need to upgrade: that feature appears to have been added in 4.4.2:

Works fine on our test/dev install of 4.4.2:

mysql> select count(Transactions.Created) from Transactions where Transactions.ObjectType='RT::Group' and Transactions.Type = 'AddMember';
+-----------------------------+
| count(Transactions.Created) |
+-----------------------------+
|                         204 |
+-----------------------------+
1 row in set (0.01 sec)