Mysql user activity report from audit trail database

user_903467 0
Viewed 267

I need a mysql query to create a monthly report on user modified tables. We have an audit trail database where each insert & update will log in respective tables.

tblappuser (List if users in a database mydb)

id  name   crdate      mddate             createdby modifiedby
0   admin  crdatetime  modifieddatetime   0         0
1   abc                                   0         0
2   xyz                                   0         0
3   abc1                                  0         0
4   xyz1                                  0         0

table1 (sample table structure in mydb database - initial data)

id    name   crdate      mddate             createdby modifiedby
100   data1  crdatetime  modifieddatetime   0         2
101   data2                                 3         3
102   data3                                 4         0
103   data4                                 2         1
104   data5                                 1         0

table1_audit (sample table structure in mydb_audit database)(Audit will have an entry if 102 record's initial createdby&modofiedby 4,0 got changes to 4,2 and then 4,1)

newid oldid    name   crdate      mddate             createdby modifiedby
tblpk 102      data3  crdatetime  modifieddatetime   4         0
      102      data3                                 4         2

I have to create a report of list of tables modified by an user(user should be in tblappuser table) for a month like this. Much appreciated the quick solution.

User Activity Report
--------------------
User    Tables               Date
xyz1    Table1,Table2..      modifieddate
abc     No Tables
abc1    Table3

EDIT

I tried it for a single user but not certain about to loop all users of tblappuser table and respective entries of all tables in a database for each day .Hence looking for a solution to get the dynamic cursor for all table modifications for the users.

DROP PROCEDURE IF EXISTS useractivity;
delimiter //
CREATE PROCEDURE useractivity()
 BEGIN
 DECLARE userlist INT DEFAULT 0;
 DECLARE i INT DEFAULT 0;
 SELECT COUNT(*) FROM tblappuser INTO userlist;
 SET i=0;
 WHILE i<userlist  DO 
   INSERT INTO `mydb`.`useractivity` (username, tblname) 
      SELECT tu.name,'table1'
         FROM `mydb`.`tblappuser` tu
         INNER JOIN `mydb`.`table1` t ON t.modifiedby = tu.id
         INNER JOIN `mydb_audit`.`table1_audit` t_a ON t.id = t_a.id 
        WHERE t.mddate <> t_a.mddate AND t.modifiedby=i ; 
   SET i = i + 1;
 END WHILE;
 End;
 //

Below query is listing the tables modified in a day, Can anyone help me to iterate each table's modifiedby data is associated with an user in tblappuser table or the results of above procedure since the above procedure is checking only one table.

SELECT table_name 
FROM information_schema.tables
WHERE UPDATE_TIME >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
and table_schema='mydb' and table_name in (SELECT table_name 
FROM information_schema.tables
WHERE UPDATE_TIME >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
and table_schema='mydb_audit') ;
mysql-workbench mysql
1 Answers

Finally got the solution as expected but prepare statement is too lengthy hence SELECT @SQL is printing only few lines, Any help here.

use mydb;
DROP PROCEDURE IF EXISTS UserActivity;
DELIMITER $$
CREATE PROCEDURE UserActivity()
BEGIN
DECLARE var1 CHAR(50);
DECLARE done TINYINT DEFAULT FALSE;
DECLARE cursor1
 CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE  UPDATE_TIME >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
WHERE table_schema='mydb' and table_name in (SELECT table_name 
FROM information_schema.tables WHERE table_schema='mydb_audit') ;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cursor1;
 my_loop: 
 LOOP
 FETCH cursor1 INTO var1;
  IF done THEN
   LEAVE my_loop;
  ELSE
SET @SQL = CONCATE('INSERT INTO mydb_audit.UserActivity (userName,tableName,date) ',
 'SELECT Concat(ta.firstname, ''' ''', ta.lastname) AS ''UserName'',"', var1 ,'",
 DATE_FORMAT(lma.newDate, ''%y-%m-%d'') ''Date''
 from mydb.tblappuser ta '
'inner join mydb_audit.', var1, ' lma on lma.modifiedBy = ta.id 
and lma.newDate > current_date() 
Inner Join abc c on ta.xxId = c.id 
inner join abc1 c1 on c.id=c.xxid
DATE_FORMAT(lma.newDate, ''%y-%m-%d'') ' );
SELECT @SQL;
PREPARE stmt FROM @SQL;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
  END IF;
 END LOOP;
END$$
DELIMITER ;
user_9034670
Related