Child pages
  • Grouper automatically delete old data
Skip to end of metadata
Go to start of metadata

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;



  • No labels