In a patch in Grouper 2.4, Grouper will be able to have reports. This will start simple and we can add more features
High level description
- Configure a report on a group or folder
- This report will have a cron that will run like loader jobs run
- Reports consist of a SQL to run in a database, generating a CSV file
- The output of the report will be encrypted and stored to storage
- Users can be notified by email that the report exists
- When the login they can download the most recent report
- This will have Grouper reverse proxy the report from storage, unencrypt it, and deliver it to the user
- Reports will be automatically deleted after 30 days
Configuration
The configuration will follow the same attribute structure as other Grouper modules like attestation and deprovisioning
Attribute definitions for config
Definition | Assigned To | Purpose | Value | Cardinality |
---|---|---|---|---|
reportConfigDef | folder, group | identify a report config | marker | Multi assign |
reportConfigValueDef | folder assignment, group assignment | name/value pairs | string | Single assign, single valued |
Attribute names for config
Name | Definition | Value |
---|---|---|
reportConfigMarker | reportConfigDef | <none> |
reportConfigType | reportConfigValueDef | Currently only SQL is available |
reportConfigFormat | reportConfigValueDef | Currently only CSV is available |
reportConfigName | reportConfigValueDef | Name of report |
reportConfigFilename | reportConfigValueDef | e.g. usersOfMyService_$$timestamp$$.csv $$timestamp$$ translates to current time in this format: yyyy_mm_dd_hh24_mi_ss |
reportConfigDescription | reportConfigValueDef | Textarea which describes the information in the report. Must be less than 4k |
reportConfigViewersGroupId | reportConfigValueDef | GroupId of people who can view this report. Grouper admins can view any report |
reportConfigQuartzCron | reportConfigValueDef | Quartz cron-like schedule |
reportConfigSendEmail | reportConfigValueDef | true/false if email should be sent |
reportConfigEmailSubject | reportConfigValueDef | subject for email |
reportConfigEmailBody | reportConfigValueDef | body for email, support \n for newlines, and substitute in: $$reportConfigName$$, $$reportConfigDescription$$, $$subjectName$$ and $$reportLink$$ The link will go to the report instance list for this report instance page for the report instance |
reportConfigSendEmailToViewers | reportConfigValueDef | true/false if report viewers should get email (if reportSendEmail is true) |
reportConfigSendEmailToGroupId | reportConfigValueDef | if reportSendEmail is true, and reportSendEmailToViewers is false), this is the groupId where members are retrieved from, and the subject email attribute, if not null then send |
reportConfigQuery | reportConfigValueDef | SQL for the report. The columns must be named in the SQL (e.g. not select *) and generally this comes from a view |
reportConfigEnabled | reportConfigValueDef | Use logic from loader enabled, either enable or disabled this job |
Attribute definitions for instance (a report that was run)
This attribute is assigned to the same owner as the config attribute (e.g. the same group/folder)
Definition | Assigned To | Purpose | Value | Cardinality |
---|---|---|---|---|
reportInstanceDef | folder, group | identify a report that was run | marker | Multi assign |
reportInstanceValueDef | folder assignment, group assignment | name/value pairs | string | Single assign, single valued |
Attribute names for config
Name | Definition | Value |
---|---|---|
reportInstanceMarker | reportInstanceDef | <none> |
reportInstanceStatus | reportInstanceValueDef | SUCCESS means link to the report from screen, ERROR means didnt execute successfully |
reportElapsedMillis | reportInstanceValueDef | number of millis it took to generate this report |
reportInstanceConfigMarkerAssignmentId | reportInstanceValueDef | Attribute assign ID of the marker attribute of the config (same owner as this attribute, but there could be many reports configured on one owner) |
reportInstanceMillisSince1970 | reportInstanceValueDef | millis since 1970 that this report was run. This must match the timestamp in the report name and storage |
reportInstanceSizeBytes | reportInstanceValueDef | number of bytes of the unencrypted report |
reportInstanceFilename | reportInstanceValueDef | filename of report |
reportInstanceDownloadCount | reportInstanceValueDef | number of times this report was downloaded |
reportInstanceEncryptionKey | reportInstanceValueDef | randomly generated 16 char alphanumeric encryption key |
To Do later
- Errors in report should be logged and throw error but maybe also store error in txt report (not sent out or available except to admins)
- Add diagnostics to test that a report is setup correctly
- Add paging to report instance list
- Configure how long reports are stored
- Screen in a user's subject screen that shows all the reports they have access to
- Centralized report dashboard
- Have a config option to "run now" (allows report viewers to run now)
- This would send a message to a daemon to run so it doesnt run in the UI
- Like Loader "run now"
- Allow another report type which runs off membership list (not straight SQL)
- Allow non admins to configure?
- Allow more columns to be added (join other database tables if allowed)
- Add another output type for JASPER report (PDF, etc)
- Support excel
- Add ability to display a CSV in the JSP in an HTML table
- Add metadata to make it clickable?
- Support more storage options, e.g. database with blobs (needs to wait until 2.5)
- Allow fields to be added from an LDAP filter