How to Delete Inactive users from Jasperserver

Issue Description:

How do you delete users in TIBCO JasperReports^® Server which have not been active for some period of time.

Resolution:

Lets say you want to delete the users which are inactive for more than 3 Months. There is no offical documented method or configuration to do this.

But, if you have enabled the Auditing and Monitoring feature in your JasperReports^® Server, I have created a workaround to delete the inactive users based on their last login time.

Note:

• This is not a supported solution. Use at your own risk. It is highly recommended that you create a backup of your database before proceeding.

• Here, users which are active(Logged in to JasperReports Server) after 01/06/2021 should be present in JasperReports Server and all other users should be deleted. Change the jia.event_date time in all the below queries and execute the delete queries.

• If there are any reports that are scheduled and running, which were created by deleted users, those jobs will be deleted.

First query:

(Delete jiuserrole rows associated with the users to be deleted)

DELETE FROM jiuserrole
      WHERE userid NOT IN( SELECT user_ID
                             FROM ( SELECT distinct(jia.username),
                                           max(jia.event_date) AS Last_Login_Date,
                                           jiuser.id AS user_ID
                                      FROM jiauditevent jia
                                     INNER JOIN jiuser ON jia.username=jiuser.username
                                     WHERE jia.event_type = 'login'
                                       AND jia.event_date > '2021-06-01 00:00:00.000'
                                     GROUP BY jia.username,jiuser.id
                                  ) as jieventquery
                         )

Second query:

(Delete logevent rows associated with the users to be deleted)

DELETE FROM jilogevent
      WHERE userid NOT IN( SELECT user_ID
                             FROM ( SELECT distinct(jia.username),
                                           max(jia.event_date) AS Last_Login_Date,
                                           jiuser.id as user_ID
                                      FROM jiauditevent jia
                                     INNER JOIN jiuser ON jia.username=jiuser.username
                                     WHERE jia.event_type = 'login'
                                       AND jia.event_date > '2021-06-01 00:00:00.000'
                                     GROUP BY jia.username,jiuser.id
                                   ) AS jieventquery
                         )

Third query:

(Delete jireportjoboutputformat rows associated with users to be deleted)

DELETE FROM jireportjoboutputformat
      WHERE report_job_id IN( SELECT id
                                FROM jireportjob 
                               WHERE owner NOT IN( SELECT user_ID
                                                     FROM ( SELECT distinct(jia.username),
                                                                   max(jia.event_date) AS Last_Login_Date,
                                                                   jiuser.id AS user_ID 
                                                              FROM jiauditevent jia
                                                             INNER JOIN jiuser ON jia.username=jiuser.username
                                                             WHERE jia.event_type = 'login'
                                                               AND jia.event_date > '2021-06-01 00:00:00.000'
                                                             GROUP BY jia.username,jiuser.id
                                                          ) AS jieventquery
                                                 )
                            )

Fourth query:

(Delete jireportjobparameter rows associated with users to be deleted)

DELETE FROM jireportjobparameter
      WHERE job_id IN( SELECT id
                         FROM jireportjob
                        WHERE owner NOT IN( SELECT user_ID
                                              FROM ( SELECT distinct(jia.username),
                                                            max(jia.event_date) AS Last_Login_Date,
                                                            jiuser.id AS user_ID
                                                       FROM jiauditevent jia
                                                      INNER JOIN jiuser ON jia.username=jiuser.username
                                                      WHERE jia.event_type = 'login'
                                                        AND jia.event_date > '2021-06-01 00:00:00.000'
                                                      GROUP BY jia.username,jiuser.id
                                                   ) AS jieventquery
                                          )
                     )

Fifth query:

(Delete jireportjob rows associated with users to be deleted)

DELETE FROM jireportjob
      WHERE owner NOT IN( SELECT user_ID
                            FROM ( SELECT distinct(jia.username),
                                          max(jia.event_date) AS Last_Login_Date,
                                          jiuser.id AS user_ID
                                     FROM jiauditevent jia
                                    INNER JOIN jiuser ON jia.username=jiuser.username
                                    WHERE jia.event_type = 'login'
                                      AND jia.event_date > '2021-06-01 00:00:00.000'
                                    GROUP BY jia.username,jiuser.id
                                 ) AS jieventquery
                        )

Final query:

(Delete users from jiuser table with inactive users by selecting the date as required)

DELETE FROM jiuser
 WHERE id NOT IN( SELECT user_ID 
                    FROM ( SELECT distinct(jia.username),
                                  max(jia.event_date) AS Last_Login_Date,
                                  jiuser.id AS user_ID
                             FROM jiauditevent jia
                            INNER JOIN jiuser ON jia.username=jiuser.username
                            WHERE jia.event_type = 'login'
                              AND jia.event_date > '2021-06-01 00:00:00.000'
                            GROUP BY jia.username,jiuser.id
                         ) AS jieventquery
                )

I hope this is helpful.