This is available in Grouper API 2.3.0 patch 97+.
Delete audit entries with no logged in user
Configured in grouper-loader properties
############################################ ## audit entries with no logged in user aren't really all that useful. There is point in time data still. So removing these shouldn't be a big deal ## default is remove these that are 5 years old. ############################################ # number of days to retain db rows in grouper_audit_entry with no logged in user (loader, gsh, etc). -1 is forever. suggested is 365 or five years: 1825. Default is -1 loader.retain.db.audit_entry_no_logged_in_user.days=-1
Call from GSH
edu.internet2.middleware.grouper.app.loader.GrouperDaemonDeleteOldRecords.deleteOldAuditEntryNoLoggedInUser(1825); // delete records older than the configured amount of days edu.internet2.middleware.grouper.app.loader.GrouperDaemonDeleteOldRecords.deleteOldAuditEntryNoLoggedInUser(); // delete records older than the specified amount of days edu.internet2.middleware.grouper.app.loader.GrouperDaemonDeleteOldRecords.deleteOldAuditEntryNoLoggedInUser(int);
Example logs in grouper daemon log
2018-02-19 14:54:17,684: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteOldAuditNotLoggedInDays: -1, threadId: 1, elapsed: 0 ms 2018-02-19 14:54:17,694: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteOldAuditNotLoggedInDays: 9, deleteOldAuditNotLoggedInCount: 0, threadId: 1, elapsed: 2 ms 2018-02-19 14:54:17,715: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteOldAuditNotLoggedInDays: 7, deleteOldAuditNotLoggedInCount: 1, threadId: 1, elapsed: 15 ms 2018-02-19 14:54:17,736: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteOldAuditNotLoggedInDays: 5, deleteOldAuditNotLoggedInCount: 1, threadId: 1, elapsed: 14 ms
Delete audit entries
Configured in grouper-loader properties
############################################ ## Some think its ok to remove all audit entries over 10 (or X) years, but will default this ## to never since even at large institutions there aren't that many records. ## These are audits for things people do on the UI or WS generally (as a different to records with no logged in user) ############################################ # number of days to retain db rows in grouper_audit_entry. -1 is forever. suggested is -1 or ten years: 3650 loader.retain.db.audit_entry.days=-1
Call from GSH
edu.internet2.middleware.grouper.app.loader.GrouperDaemonDeleteOldRecords.deleteOldAuditEntry(3650); // delete records older than the configured amount of days edu.internet2.middleware.grouper.app.loader.GrouperDaemonDeleteOldRecords.deleteOldAuditEntry(); // delete records older than the specified amount of days edu.internet2.middleware.grouper.app.loader.GrouperDaemonDeleteOldRecords.deleteOldAuditEntry(int);
Example logs in grouper daemon log
2018-02-19 14:54:25,125: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteOldAuditDays: -1, threadId: 1, elapsed: 0 ms 2018-02-19 14:54:25,132: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteOldAuditDays: 9, deleteOldAuditCount: 0, threadId: 1, elapsed: 2 ms 2018-02-19 14:54:25,143: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteOldAuditDays: 7, deleteOldAuditCount: 2, threadId: 1, elapsed: 5 ms 2018-02-19 14:54:25,154: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteOldAuditDays: 5, deleteOldAuditCount: 2, threadId: 1, elapsed: 5 ms
Delete old deleted point in time data
Configured in grouper-loader.properties
############################################ ## After you delete an object in grouper, it is still in point in time. So if you want to know who ## was in a group a year ago, you need this info ## However, after some time it might be ok to let it go. So the default is 5 years ############################################ # number of days to retain db rows for point in time deleted objects. -1 is forever. suggested is 365 or five years: 1825. Default is -1 loader.retain.db.point_in_time_deleted_objects.days=-1
Call from GSH
edu.internet2.middleware.grouper.app.loader.GrouperDaemonDeleteOldRecords.deleteOldDeletedPointInTimeObjects(1825); // delete records older than the configured amount of days edu.internet2.middleware.grouper.app.loader.GrouperDaemonDeleteOldRecords.deleteOldDeletedPointInTimeObjects(); // delete records older than the specified amount of days edu.internet2.middleware.grouper.app.loader.GrouperDaemonDeleteOldRecords.deleteOldDeletedPointInTimeObjects(int);
Example logs in grouper daemon log
2018-02-19 14:54:39,461: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteDeletedPointInTimeObjectsDays: -1, threadId: 1, elapsed: 0 ms 2018-02-19 14:54:39,491: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteDeletedPointInTimeObjectsDays: 9, deleteDeletedPointInTimeObjectsCount: 0, threadId: 1, elapsed: 25 ms 2018-02-19 14:54:39,520: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteDeletedPointInTimeObjectsDays: 7, deleteDeletedPointInTimeObjectsCount: 3, threadId: 1, elapsed: 24 ms 2018-02-19 14:54:39,601: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, deleteDeletedPointInTimeObjectsDays: 5, deleteDeletedPointInTimeObjectsCount: 3, threadId: 1, elapsed: 76 ms
Delete old folders
Configured in grouper-loader.properties
############################################ ## This is optional. You can just automatically obliterate folders *directly in a parent folder* that are a certain age old e.g. courses. ## so you could delete a term of courses 4 years old if you like. Note, make sure the loader isn't going to recreate or you will get churn ## Note this will also delete the point in time data as well. ############################################ # number of days after a subfolder (directly in a parent folder) is created that it will be obliterated (deleted) # and point in time will be deleted too. # "courses" or "anotherLabel" are variables you make up in these examples #loader.retain.db.folder.courses.days=1825 #loader.retain.db.folder.courses.parentFolderName=my:folder:for:courses #loader.retain.db.folder.courses.deletePointInTime=true #loader.retain.db.folder.anotherLabel.days=1825 #loader.retain.db.folder.anotherLabel.parentFolderName=my:folder:for:something #loader.retain.db.folder.anotherLabel.deletePointInTime=false
Call from GSH
edu.internet2.middleware.grouper.app.loader.GrouperDaemonDeleteOldRecords.obliterateOldStemsDirectlyInStem(Set<DeleteOldStem>); // delete records older than the configured amount of days edu.internet2.middleware.grouper.app.loader.GrouperDaemonDeleteOldRecords.obliterateOldStemsDirectlyInStem();
Example logs in grouper daemon log
2018-02-19 14:51:57,161: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, obliterateOldStemsCount: 0, threadId: 1, elapsed: 0 ms 2018-02-19 14:51:57,168: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, obliterateOldStemsCount: 1, obliterateOldStems.0.stem: test, obliterateOldStems.0.days: 9, obliterateOldStems.0.deletePointInTime: true, obliterateOldStems.0.stem.subFolderCount: 2, obliterateOldStems.0.stem.test1.notOldEnoughToDelete: true, obliterateOldStems.0.stem.test2.notOldEnoughToDelete: true, threadId: 1, elapsed: 6 ms 2018-02-19 14:53:06,383: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, obliterateOldStemsCount: 1, obliterateOldStems.0.stem: test, obliterateOldStems.0.days: 7, obliterateOldStems.0.deletePointInTime: true, obliterateOldStems.0.stem.subFolderCount: 2, obliterateOldStems.0.stem.test1.notOldEnoughToDelete: true, obliterateOldStems.0.stem.test2.deleting: true, obliterateOldStems.0.stem.test2.folderCreatedOn: 2018-02-11 14:51:57.096, threadId: 1, elapsed: 60744 ms 2018-02-19 14:54:06,941: [main] DEBUG GrouperLoaderLog.logDebug(33) - - logType: maintenanceDeleteOldRecords, overallId: null, obliterateOldStemsCount: 1, obliterateOldStems.0.stem: test, obliterateOldStems.0.days: 5, obliterateOldStems.0.deletePointInTime: true, obliterateOldStems.0.stem.subFolderCount: 1, obliterateOldStems.0.stem.test1.deleting: true, obliterateOldStems.0.stem.test1.folderCreatedOn: 2018-02-13 14:51:57.096, threadId: 1, elapsed: 60538 ms
Experience at Grouper Demo
Pre-patch
-- Audit entries: 137210 select count(1) from grouper_audit_entry_v; -- Audit entries no logged in user: 128204 select count(1) from grouper_audit_entry_v where logged_in_subject_id is null; -- Audit entries no logged in user older than a month old: 82201 select count(1) from grouper_audit_entry_v where logged_in_subject_id is null and created_on < 1516338000000; -- PIT entries: 148907 SELECT (SELECT COUNT(1) FROM grouper_pit_attribute_assign) + (SELECT COUNT(1) FROM grouper_pit_attribute_def) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn_set) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_value) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name_set) + (SELECT COUNT(1) FROM grouper_pit_fields) + (SELECT COUNT(1) FROM grouper_pit_groups) + (SELECT COUNT(1) FROM grouper_pit_group_set) + (SELECT COUNT(1) FROM grouper_pit_members) + (SELECT COUNT(1) FROM grouper_pit_memberships) + (SELECT COUNT(1) FROM grouper_pit_role_set) + (SELECT COUNT(1) FROM grouper_pit_stems) AS pit_count FROM DUAL; -- PIT entries of deleted objects: 126095 SELECT (SELECT COUNT(1) FROM grouper_pit_attribute_assign WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attribute_def WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn_set WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_value WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name_set WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_fields WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_groups WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_group_set WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_members WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_memberships WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_role_set WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_stems WHERE end_time IS NOT NULL) AS pit_count FROM DUAL; -- PIT entries of deleted objects older than a month: 80342 SELECT (SELECT COUNT(1) FROM grouper_pit_attribute_assign WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_attribute_def WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn_set WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_value WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name_set WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_fields WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_groups WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_group_set WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_members WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_memberships WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_role_set WHERE end_time IS NOT NULL and end_time < 1516338000000000) + (SELECT COUNT(1) FROM grouper_pit_stems WHERE end_time IS NOT NULL and end_time < 1516338000000000) AS pit_count FROM DUAL; -- Folders in delete folder 'users:penn' newer than 5 years: 5 SELECT COUNT(1) FROM grouper_stems WHERE NAME LIKE 'users:penn:%' AND create_time > 1361250000000; -- Folders in delete folder 'users:penn' older than 5 years: 39 SELECT COUNT(1) FROM grouper_stems WHERE NAME LIKE 'users:penn:%' AND create_time < 1361250000000;
Post-patch: (dont configure anything)
Run the clean logs job
gsh 0% grouperSession = GrouperSession.startRootSession();
edu.internet2.middleware.grouper.GrouperSession: 7f8474d2914a4cecac143be77768a34b,'GrouperSystem','application'
gsh 1% loaderRunOneJob("MAINTENANCE_cleanLogs");
loader ran successfully: Deleted 3872 records from grouper_loader_log older than 7 days old. Deleted 2038 records from grouper_change_log_entry older than 14 days old. (1517867128940000) Deleted 0 instrumentation records older than 30 days old. (1516484734612) Configured to not delete records from audit_entry table with null logged in member idConfigured to not delete records from audit_entry tableConfigured to not delete records from DeletedPointInTimeObjects
Do counts
Audit entries: 137214 Audit entries no logged in user: 128207 Audit entries no logged in user older than a month old: 82201 PIT entries: 148913 PIT entries of deleted objects: 126096 PIT entries of deleted objects older than a month: 80342 Folders in delete folder 'users:penn' newer than 5 years: 5 Folders in delete folder 'users:penn' older than 5 years: 39
Configure deletes in grouper-loader.properties
############################################ ## audit entries with no logged in user aren't really all that useful. There is point in time data still. ## So removing these shouldn't be a big deal ## default is remove these that are 5 years old. ############################################ # number of days to retain db rows in grouper_audit_entry with no logged in user (loader, gsh, etc). -1 is forever. # suggested is 365 or five years: 1825. Default is -1 loader.retain.db.audit_entry_no_logged_in_user.days=31 ############################################ ## Some think its ok to remove all audit entries over 10 (or X) years, but will default this ## to never since even at large institutions there aren't that many records. ## These are audits for things people do on the UI or WS generally (as a different to records with no logged in user) ############################################ # number of days to retain db rows in grouper_audit_entry. -1 is forever. suggested is -1 or ten years: 3650 loader.retain.db.audit_entry.days=365 ############################################ ## After you delete an object in grouper, it is still in point in time. So if you want to know who ## was in a group a year ago, you need this info ## However, after some time it might be ok to let it go. So the default is 5 years ############################################ # number of days to retain db rows for point in time deleted objects. -1 is forever. suggested is 365 or five years: 1825. # Default is -1 loader.retain.db.point_in_time_deleted_objects.days=31 ############################################ ## This is optional. You can just automatically obliterate folders *directly in a parent folder* ## that are a certain age old e.g. courses. ## so you could delete a term of courses 4 years old if you like. Note, make sure the loader isn't going to recreate ## or you will get churn ## Note this will also delete the point in time data as well. ############################################ # number of days after a subfolder (directly in a parent folder) is created that it will be obliterated (deleted) # and point in time will be deleted too. # "courses" or "anotherLabel" are variables you make up in these examples loader.retain.db.folder.test.days=1825 loader.retain.db.folder.test.parentFolderName=users:penn loader.retain.db.folder.test.deletePointInTime=true
Run the daemon with GSH
[appadmin@i2midev6 tomcats]$ cd tomcat_f_gsh/webapps/grouper_v2_3/WEB-INF/bin/
gsh 0% grouperSession = GrouperSession.startRootSession();
edu.internet2.middleware.grouper.GrouperSession: 7f8474d2914a4cecac143be77768a34b,'GrouperSystem','application'
gsh 1% loaderRunOneJob("MAINTENANCE_cleanLogs");
loader ran successfully: Deleted 3872 records from grouper_loader_log older than 7 days old. Deleted 2038 records from grouper_change_log_entry older than 14 days old. (1517867128940000) Deleted 0 instrumentation records older than 30 days old. (1516484734612) Configured to not delete records from audit_entry table with null logged in member idConfigured to not delete records from audit_entry tableConfigured to not delete records from DeletedPointInTimeObjects
Counts afterwards
-- Audit entries: 3727 -- Audit entries no logged in user: 275 -- Audit entries no logged in user older than a month old: 0 -- PIT entries: 19859 -- PIT entries of deleted objects: 46 -- PIT entries of deleted objects older than a month: 0 -- Folders in delete folder 'users:penn' newer than 5 years: 5 -- Folders in delete folder 'users:penn' older than 5 years: 5
Experience at Penn test env
Pre-patch counts
-- Audit entries: 12538020 SELECT COUNT(1) FROM grouper_audit_entry_v; -- Audit entries no logged in user: 12234228 SELECT COUNT(1) FROM grouper_audit_entry_v WHERE logged_in_subject_id IS NULL; -- Audit entries no logged in user older than 1 year old: 8804148 SELECT COUNT(1) FROM grouper_audit_entry_v WHERE logged_in_subject_id IS NULL AND created_on < 1490839886000; -- PIT entries: 16644841 SELECT (SELECT COUNT(1) FROM grouper_pit_attribute_assign) + (SELECT COUNT(1) FROM grouper_pit_attribute_def) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn_set) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_value) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name_set) + (SELECT COUNT(1) FROM grouper_pit_fields) + (SELECT COUNT(1) FROM grouper_pit_groups) + (SELECT COUNT(1) FROM grouper_pit_group_set) + (SELECT COUNT(1) FROM grouper_pit_members) + (SELECT COUNT(1) FROM grouper_pit_memberships) + (SELECT COUNT(1) FROM grouper_pit_role_set) + (SELECT COUNT(1) FROM grouper_pit_stems) AS pit_count FROM DUAL; -- PIT entries of deleted objects: 6954271 SELECT (SELECT COUNT(1) FROM grouper_pit_attribute_assign WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attribute_def WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn_set WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_value WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name_set WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_fields WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_groups WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_group_set WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_members WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_memberships WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_role_set WHERE end_time IS NOT NULL) + (SELECT COUNT(1) FROM grouper_pit_stems WHERE end_time IS NOT NULL) AS pit_count FROM DUAL; -- PIT entries of deleted objects older than 3 years: 0 SELECT (SELECT COUNT(1) FROM grouper_pit_attribute_assign WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_attribute_def WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_actn_set WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_attr_assn_value WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_attr_def_name_set WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_fields WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_groups WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_group_set WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_members WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_memberships WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_role_set WHERE end_time IS NOT NULL AND end_time < 1490839886000000) + (SELECT COUNT(1) FROM grouper_pit_stems WHERE end_time IS NOT NULL AND end_time < 1490839886000000) AS pit_count FROM DUAL; -- Folders in delete folder 'users:penn' newer than 5 years: 13097 SELECT COUNT(1) FROM grouper_stems WHERE NAME LIKE 'penn:community:student:course:%' AND create_time > 1364609486000; -- Folders in delete folder 'users:penn' older than 5 years: 3095 SELECT COUNT(1) FROM grouper_stems WHERE NAME LIKE 'penn:community:student:course:%' AND create_time < 1364609486000;