Tutorial: Plug-in Report

Contents

  1. Background
  2. Define Requirements
  3. Write SQL Query
  4. Deploy Query
  5. View Report
  6. Conclusion

Background

While OpenFISMA contains many, useful built-in reports, organizations will still have unique reporting requirements that are not fulfilled by these canned reports.

Building new reports, however, is timely and costly. Most of the development time is spent writing code to fetch the data, format it, and render it on screen. Plug-in reports provide a shortcut past all of this code directly to writing report queries. A plug-in report consists of a SQL query and a brief configuration entry in OpenFISMA's report file.

Once you specify these two items, OpenFISMA handles the messy details for you. It fetches your data, renders it to the browser in tabular format, enforces access control, and even provides export options for Excel and PDF.

This tutorial covers the steps involved in creating a plug-in report.

Define Requirements

The first step in creating a plug-in report is to define your requirements. Specifically, you need to think about what kind of data you want to collect, how it should be arranged, and whether you want to roll-up (aggregate) any of the attributes.

For this tutorial, we will define some sample requirements, and then go through the steps to build a report based on those requirements.

  • Management wants a report to identify which quarters are most busy for audits so that resources can be planned accordingly.
  • The report should show for each quarter, historically, how many findings were opened in that quarter.

Write SQL Query

To address the requirements put forth in the previous section, we write a query as follows:

  1. SELECT 
  2.   CONCAT(YEAR(p.create_ts), ' Q', QUARTER(p.create_ts)) AS 'Quarter', COUNT(p.create_ts) AS 'Findings Created' 
  3. FROM
  4.   poams p 
  5. GROUP BY
  6.   YEAR(p.create_ts),
  7.   QUARTER(p.create_ts) 
  8. ORDER BY 
  9.   YEAR(p.create_ts),
  10.   QUARTER(p.create_ts)

There is no need to put a semicolon after the query.

Deploy Query

In order to deploy the query, you will need to configure the report and then copy the SQL query onto your server.

The configuration file for the plug-in reports is stored inside ./application/config/reports.conf. This file is an INI configuration file. Scroll down to the bottom of the file and add the following configuration information.

  1. [findings_opened]
  2. title = "Findings Opened By Quarter"
  3. description = "
  4.  
  5. Displays the number of findings opened in each past quarter.
  6.  
  7. "
  8. roles[] = "ADMIN"
  9. roles[] = "AO"
  10. roles[] = "SAISO"

Configuring the Plug-in Report

Deploying the SQL Query

This configuration file contains one or more entries describing the plug-in reports which are available on the system.

The first line indicates the internal name for this report. This name will also be used to name the query so that OpenFISMA can find the query that goes with this report configuration.

The second line defines the title of the report. This title will be used to represent the report to the user through the interface.

The third line is a description string which will be displayed in the user interface. You should enter a description of the report you are creating, what data it shows, etc. You may use HTML markup in the description to style the content.

Lines 4-6 define which roles are allowed to view this particular report. The roles are referred to by their nickname. So in this case, this report will be visible to everybody with either the ADMIN role, the AO (Authorizing Official) role, or the SAISO (Senior Agency Information Security Officer) role.

The next step is to copy the query to the server. Place the query into a file stored in ./application/config/reports/findings_opened.sql. Notice that the name of the SQL file is the same as the internal name defined in the configuration file, with the '.sql' suffix added to the end.

Each plug-in report should be stored in a separate file. Each file should contain a single query. If you want to include multiple queries in a single report, you will need to use the UNION operator to concatenage several subqueries into a single main query.

 

View Report

Plug-in Reports Interface

Viewing the Plug-in Report

Export Plug-in Report to Excel
Graph Created From Exported Data

To view the report: log in to OpenFISMA. From the "Reports" menu, select the "Plug-in Reports" item. OpenFISMA will display a screen showing all of the configured plug-in reports. Click the title of the report which you have just created, "Findings Opened By Quarter".

The report will be displayed in a table inside the OpenFISMA interface. To export the report, click on the PDF or Excel icons in the upper left corner. Once exported to Excel, you have the full power of Excel available: sort, filter, and chart patterns in your data.

Conclusion

With plug-in reports, any individual with experience writing SQL queries can quickly generate highly customized reports and deploy those reports without writing any code. Reports follow the same access control roles that the rest of OpenFISMA follows, so you don't need to worry about data being released to the wrong individuals.

With the ability to export plug-in reports to Excel, you can quickly create charts to convey data in OpenFISMA in graphical format.