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 or if there are more than 100 instances of a report
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 screen for this report |
| 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 instance
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 |
| reportInstanceRows | reportInstanceValueDef | number of rows returned in report |
| reportInstanceEmailSubjects | reportInstanceValueDef | source::::subjectId1, source2::::subjectId2 list for subjects who were were emailed successfully (cant be more than 4k chars) |
| reportInstanceEmailSubjectsError | reportInstanceValueDef | source::::subjectId1, source2::::subjectId2 list for subjects who were were NOT emailed successfully, dont include g:gsa groups (cant be more than 4k chars) |
Changes to group or folder drop down
Under folders or groups, in the more actions, should be "Reports"
There should be a drop down in reports, with the following options:
- View reports
- Edit reports
View reports screen
- This is the default screen
- For all the configured reports, see if the current user can view them (wheel group or in the reportViewers group for the report), and if so, list the reports there, one line per report, with a link to the report page, and a link to the latest report download
- If there are no reports available, display a message "There are no reports you are allowed to view"
- Column for report name (clickable to report screen)
- Column for if enabled or not
- Column for last timestamp it was run (from report instance attribute)
- Column for status (SUCCESS?) (from report instance attribute)
- Column for number of rows in report (from report instance attribute)
- Column for cron schedule (from report config attribute)
- Column with drop down to download most recent report, view reports (report screen), report logs, enable/disable
Edit reports screen
- Only for wheel group
- Can pick a report to edit or can add a new. Like the deprovisioning edit
Report screen
- Can see if wheel user or in the reportViewers group for the report
- Show the report name and description
- Show table with most recent 100 entries with columns (from report instance attributes)
- Report name (same for each row)
- Timestamp (sorted descending)
- Download report link
- View report details link (goes to report instance screen)
- Status
- When the report run
- How many rows in report
- Show the settings in read only mode
- In right of screen have one actions drop down: download most recent report, view reports (report screen), report logs, enable/disable
Report logs
- Clickable from Report screen or drop down in some of the report screens
- Can see screen if wheel user or in the reportViewers group for the report
Report logs
- Clickable from Report screen or drop down in some of the report screens
- Can see screen if wheel user or in the reportViewers group for the report
- Show list of most recent 100 report logs from grouper loader log table
- Should be a table that looks like the grouper loader log screen
- Should have exception stack if there was an error
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