SugarCRM user activity report

/* user activity report for SugarCRM */ set @STARTDATE = '2016-01-01 00:00:00', @ENDDATE = '2016-06-30 23:59:59', @USERNAME = '%'; select t.session_id as 'Session ID', convert_tz(t.date_modified,'+00:00','+10:00') AS 'Date Accessed', # set the UTC offset as appropriate concat_ws(' ', u.first_name, u.last_name ) AS 'User', case when t.module_name = 'Accounts' then concat('Accounts: ',a.name,case when a.deleted = 0 then '' else ' (DELETED)' end) when t.module_name = 'Contacts' then concat('Contacts: ',c.first_name,' ',c.last_name,case when c.deleted = 0 then '' else ' (DELETED)' end) when t.module_name = 'Reports' then concat('Reports: ',saved_reports.name,case when saved_reports.deleted = 0 then '' else ' (DELETED)' end) when t.module_name = 'Users' then concat('Users: ',u.first_name,' ',u.last_name,case when u.deleted = 0 then '' else ' (DELETED)' end) when t.module_name = 'Contracts' then concat('Contracts: ',contract.name,case when contract.deleted = 0 then '' else ' (DELETED)' end) else concat(t.module_name,': ',t.item_id) end as 'Record', t.action from tracker t left join users u ON t.user_id = u.id left join acl_roles_users ru on ru.user_id = u.id left join acl_roles r on r.id = ru.role_id left join contacts c on t.item_id = c.id left join accounts a on t.item_id = a.id left join contracts contract on t.item_id = contract.id left join saved_reports on t.item_id = saved_reports.id where convert_tz(t.`date_modified`,'+00:00','+10:00') >= @STARTDATE AND convert_tz(t.`date_modified`,'+00:00','+10:00') <= @ENDDATE and u.user_name like @USERNAME and u.user_name <> 'john.who.you.do.not.want.in.report.doe' # remove duplicates group by concat(t.session_id,t.date_modified,u.first_name,u.last_name,t.item_id,t.action) # sort it by user, by session, by date. order by u.last_name ASC, u.first_name ASC, t.session_id desc, t.`date_modified` ASC;