Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Include Page
spaceKeyGrouper
pageTitleNavigation

In a patch patches in Grouper 2.4 (api #61, ui #37), Grouper will have has a reporting capability.  This will start simple and we can add more features later.

Info

You may want  to check out the blog on Grouper Reporting from November 2019. 


Children Display

Table of Contents

...

  • Configure a report on a group or folder
  • This report will have a cron that will run like loader jobs run
  • Reports consist The SQL report type consists 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

    Quick start

    Make sure you have patch: 2.4.0 api #60 and ui #37

    You need to use a file system (if you have a shared filesystem among all grouper component JVMs), or Amazon AWS S3.

    For this example lets use the file system.  Configure in grouper.properties

      • Note: it is a best practice to put the SQL in a view and call the view from grouper.  Keep the source in the view in source control for versioning
      • The SQL report type is available in 2.5.0, 2.6.0, and 2.4.0 api #60 and ui #37
    • The GSH report type uses a GSH script to either output directly to the report file, or to build data rows that are converted to a CSV file
      • The GSH report type is available in 2.5.53 and 2.6.0
    • 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

    Reporting Configuration

    You need to use a file system (if you have a shared filesystem among all grouper component JVMs), or Amazon AWS S3.

    grouper.properties

    Code Block
    ######################################
    ## Grouper Reporting
    ######################################
    
    # folder where system objects are for reporting config
    Code Block
    ######################################
    ## Grouper Reporting
    ######################################
    
    # grouper reporting file system path where reports will be stored, e.g. /opt/grouper/reports
    # {valueType: "string", required: falsestem"}
    reporting.file.system.pathreportConfig.systemFolder = d:/temp/temp/grouperReports
    
    

    Open a group, add a new report

    Image Removed

    ...

    $$grouper.rootStemForBuiltinObjects$$:reportConfig
    
    # if grouper reporting should be enabled
    # {valueType: "boolean", required: true}
    grouperReporting.enable = true
    
    # grouper reporting storage# grouper reporting storage option. valid values are database, fileSystem or S3
    # {valueType: "string", required: true}
    reporting.storage.option = database
    
    # grouper reporting file system path where reports will be stored, e.g. /opt/grouper/reports
    # {valueType: "string", required: false}
    reporting.file.system.path = 
    
    # grouper reporting s3 bucket name where the reports will be uploaded
    # {valueType: "string", required: false}
    reporting.s3.bucket.name =
    
    # grouper reporting s3 bucket name where the reports will be uploaded, e.g. us-west-2
    # {valueType: "string", required: false}
    reporting.s3.region = 
    
    # grouper reporting s3 access key
    # {valueType: "string", required: false}
    reporting.s3.access.key =
    
    # grouper reporting s3 secret key
    # {valueType: "string", required: false}
    reporting.s3.secret.key =
    
    #grouper reporting email subject
    # {valueType: "string"}
    reporting.email.subject = Report $$reportConfigName$$ generated 
    
    #grouper reporting email body.  Can use variables 
    # {valueType: "string"}
    reporting.email.body = Hello $$subjectName$$, \n\n Report $$reportConfigName$$ has been generated. Download the report: $$reportLink$$ \n\n Thanks


    For this example lets use the file system.  Configure in grouper.properties

    Code Block
    # grouper reporting file system path where reports will be stored, e.g. /opt/grouper/reports
    # {valueType: "string", required: false}
    reporting.file.system.path = d:/temp/temp/grouperReports
    
    

    Make sure you have mail setup in the SMTP external system

    Code Block
    #smtp server is a domain name or dns name.  set to "testing" if you want to log instead of send (e.g. for testing)
    # {valueType: "string"}
    mail.smtp.server = localhost
    mail.smtp.from.address = noreply@whatever.edu
    

    Make sure you have a mailAttributeName in your person subject source

    Make sure you have grouper.ui.url set in grouper.properties 

    Code Block
    #put the URL which will be used e.g. in emails to users.  include the webappname at the end, and nothing after that.
    #e.g. https://server.school.edu/grouper/
    # {valueType: "string"}
    grouper.ui.url = http://localhost:8097/grouper/


    Make sure you have an encrypt.key in morphString.properties

    Code Block
    subjectApi.source.jdbc.param.emailAttributeName.value = email

    If you are using the built in subject source, you can add a user for yourself with an email address (yours), this is GSH

    Code Block
    grouperSession = GrouperSession.startRootSession();
    RegistrySubject.addOrUpdate(grouperSession, "mchyzer", "person", "Chris Hyzer", "Chris Hyzer", "mchyzer", "Chris Hyzer - IAM architect", "your@email.address");


    SQL Report Example

    Open a group, add a new report

    Image Added

    myReport
    my service users
    SQL
    SELECT gm.subject_id as SUBJECT_ID, gm.name as NAME, gm.description as DESCRIPTION FROM grouper_memberships_lw_v gmlv, grouper_members gm WHERE gmlv.group_name = 'testB:testGroup2' AND gmlv.member_id = gm.id AND gmlv.subject_source = 'jdbc' ORDER BY 1
    CSV
    0 0 6 * * ?   (run daily at 6am)
    usersOfMyService_$$timestamp$$.csv
    Yes, send email when the report is ready
    Allowed group idA group with your user in it

    Image Added


    Now get an email about the report

    Image Added


    See the report


    Image Added


    GSH Report Example

    The GSH report type can have an output type of either CSV or FILE. For both types, the script will use fields from the available gsh_builtin_gshReportRuntime variable to add data to the output. For CSV output, the script will set a header array and a list of data arrays. For FILE output, the script will open a Writer and write arbitrary data to the character stream.

    Variables available to the GSH script

    VariableJava classDescription

    gsh_builtin_grouperSession

    GrouperSession

    session the script runs as

    gsh_builtin_ownerStemName

    Stringowner stem name where template was called

    gsh_builtin_ownerGroupName

    Stringowner group name where template was called

    gsh_builtin_gshReportRuntime

    GshReportRuntimecontainer to hold important information about the run
    gsh_builtin_gshReportRuntime.getOwnerGroup()Groupowner group where template was called
    gsh_builtin_gshReportRuntime.getOwnerStem()Stemowner stem where template was called
    gsh_builtin_gshReportRuntime.getOwnerGroupName()Stringsame as gsh_builtin_ownerGroupName
    gsh_builtin_gshReportRuntime.getOwnerStemName()Stringsame as gsh_builtin_ownerStemName
    gsh_builtin_gshReportRuntime.getGrouperReportData()GrouperReportDatacontainer for the output file (FILE) or csv rows (CSV)

    gsh_builtin_gshReportRuntime.getGrouperReportData().getFile()

    File(FILE) file object to be written to
    gsh_builtin_gshReportRuntime.getGrouperReportData().getHeaders()List<String>(CSV) column names to appear in csv header row
    gsh_builtin_gshReportRuntime.getGrouperReportData().getData()List<String[]>(CSV) rows of data to appear in the csv; not set by default, so must be initialized with at least an empty list


    Example script writing to FILE format

    Code Block
    languagegroovy
    Group g = gsh_builtin_gshReportRuntime.ownerGroup
    File file = gsh_builtin_gshReportRuntime.grouperReportData.file
    
    file.withWriter('utf-8') { writer ->
        writer << ['Row', 'ID', 'UID', 'Name', 'Email'].join(",") << "\n"
        g.members.eachWithIndex { it, i ->
            writer << i+1 << ","
            writer << it.subject.getAttributeValue('employeenumber') << ","
            writer << it.subject.getAttributeValue('uid') << ","
            writer << it.subject.getAttributeValue('cn') << ","
            writer << it.subject.getAttributeValue('mail') << "\n"
        }
    }


    Example script writing to CSV format

    Code Block
    languagegroovy
    Group g = gsh_builtin_gshReportRuntime.ownerGroup
    GrouperReportData grouperReportData = gsh_builtin_gshReportRuntime.grouperReportData
    
    grouperReportData.headers = ['Row', 'ID', 'UID', 'Name', 'Email']
    grouperReportData.data = new ArrayList<String[]>()
    
    g.members.eachWithIndex { it, i ->
        String[] row = [
                i+1,
                it.subject.getAttributeValue('employeenumber'),
                it.subject.getAttributeValue('uid'),
                it.subject.getAttributeValue('cn'),
                it.subject.getAttributeValue('mail'),
        ]
    
        grouperReportData.data << row
    }

    Mock setup of objects for development of a GSH reporting script

    Code Block
    languagegroovy
    import edu.internet2.middleware.grouper.app.reports.GshReportRuntime
    import edu.internet2.middleware.grouper.app.reports.GrouperReportData
    
    def gs = GrouperSession.startRootSessionIfNotStarted().grouperSession
    
    def g = GroupFinder.findByName(gs, "test:vpn:vpn_legacy_exceptions", true)
    GshReportRuntime gshReportRuntime = new GshReportRuntime()
    gshReportRuntime.ownerGroup = g
    gshReportRuntime.ownerGroupName = g.name
    
    GrouperReportData grouperReportData = new GrouperReportData()
    gshReportRuntime.grouperReportData = grouperReportData
    
    // (next line is for FILE output only, set to an arbitrary file instead of the autogenerated one)
    grouperReportData.file = new File('/tmp/legacy_exceptions.csv')
    
    // simulate the built-in variables
    GrouperSession gsh_builtin_grouperSession = gs
    GshReportRuntime gsh_builtin_gshReportRuntime = gshReportRuntime
    String gsh_builtin_ownerStemName = gsh_builtin_gshReportRuntime.ownerStemName
    String gsh_builtin_ownerGroupName = gsh_builtin_gshReportRuntime.ownerGroupName
    
    /** continue from here with reporting script */

    Make sure you have mail setup in grouper.properties:

    Code Block
    #####################################
    ## Mail settings
    #####################################
    
    #smtp server is a domain name or dns name.  set to "testing" if you want to log instead of send (e.g. for testing)
    # {valueType: "string"}
    mail.smtp.server = localhost
    mail.from.address = noreply@whatever.edu
    

    Make sure you have a mailAttributeName in your person subject source

    Make sure you have grouper.ui.url set in grouper.properties 

    Code Block
    #put the URL which will be used e.g. in emails to users.  include the webappname at the end, and nothing after that.
    #e.g. https://server.school.edu/grouper/
    # {valueType: "string"}
    grouper.ui.url = http://localhost:8097/grouper/

    Make sure you have an encrypt.key in morphString.properties

    Code Block
    subjectApi.source.jdbc.param.emailAttributeName.value = email

    If you are using the built in subject source, you can add a user for yourself with an email address (yours), this is GSH

    Code Block
    grouperSession = GrouperSession.startRootSession();
    RegistrySubject.addOrUpdate(grouperSession, "mchyzer", "person", "Chris Hyzer", "Chris Hyzer", "mchyzer", "Chris Hyzer - IAM architect", "your@email.address");

    Assign the user to a group and assign that group to be a READER of the group (so they get the report and can read it)

    Image Removed

    Now get an email about the report

    Configuration

    grouper.properties

    Code Block
    ######################################
    ## Grouper Reporting
    ######################################
    
    # folder where system objects are for reporting config
    # {valueType: "stem"}
    reportConfig.systemFolder = $$grouper.rootStemForBuiltinObjects$$:reportConfig
    
    # if grouper reporting should be enabled
    # {valueType: "boolean", required: true}
    grouperReporting.enable = true
    
    # grouper reporting storage option. valid values are fileSystem or S3
    # {valueType: "string", required: true}
    reporting.storage.option = fileSystem
    
    # grouper reporting file system path where reports will be stored, e.g. /opt/grouper/reports
    # {valueType: "string", required: false}
    reporting.file.system.path = 
    
    # grouper reporting s3 bucket name where the reports will be uploaded
    # {valueType: "string", required: false}
    reporting.s3.bucket.name =
    
    # grouper reporting s3 bucket name where the reports will be uploaded, e.g. us-west-2
    # {valueType: "string", required: false}
    reporting.s3.region = 
    
    # grouper reporting s3 access key
    # {valueType: "string", required: false}
    reporting.s3.access.key =
    
    # grouper reporting s3 secret key
    # {valueType: "string", required: false}
    reporting.s3.secret.key =
    
    #grouper reporting email subject
    # {valueType: "string"}
    reporting.email.subject = Report $$reportConfigName$$ generated 
    
    #grouper reporting email body.  Can use variables 
    # {valueType: "string"}
    reporting.email.body = Hello $$subjectName$$, \n\n Report $$reportConfigName$$ has been generated. Download the report: $$reportLink$$ \n\n Thanks
    
    
    


    Internal attributes

    The configuration will follow the same attribute structure as other Grouper modules like attestation and deprovisioning

    ...

    In 2.4 we dont want to add a new table to store files, so for people who want to use this feature the only option will be AWS S3 buckets or filesystem with the report encrypted.  We can add more storage options laterwith the report encrypted.  We can add more storage options later

    In 2.5.34+ this is stored by default in the database.

    Database

    Stores in grouper_file table

    AWS S3

    The deployer will need an AWS account, the free level might suffice

    Need to configure the AWS creds in grouper.propertiescreds in grouper.properties

    • You might create an IAM user or role, or have best practices, but to get started quickly do this
    • In the upper right click on your aws console username, "my security credentials"
    • Under access keys, create new access key

    Configure the AWS S3 bucket location

    • Note the region
    • You can keep all the settings as the default since we dont need versions and Grouper does its own encryption
    • Do you not allow public access to bucket

    Filesystem

    Configure the path where report files will be stored

    ...

    • Add entity relationship diagram on wiki to help with making queries
    • Add ability to use SCP to store reports
    • Add ability to use SCP to send reports
    • Add ability to store reports in box folder
    • 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)
    • Allow reports from GSH / java?
    • Allow reports from WS as user
    • 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
    • Add the daily Grouper report to run like this (dont email)
    • Email batching per user (user gets a weekly digest about their reports)?
    • In another pass we could create a report based on loading/provisioning.

    • On the Daemons job screen, show user friendly names for jobs (pull grouper object name from id and show under the report job id)

    ...

    Grouper Report showing summary of your installation

    Grouper Reporting Blog