Last updated at Mon, 24 Jul 2017 15:20:44 GMT

Do any of these these questions sound familiar?

"Printable reports are really valuable and I use them on a daily basis. However, is there a section that I can add to show a summary by asset group or site?"

"I really like the XML format, but its a little hard to process and I have to write code to do anything useful with it. What other options do I have?"

"The CSV export is fantastic, yet when I select just the asset columns it shows duplicates? How do I export my asset inventory?"

"You added malware and exploit data to your user interface, which is really valuable to use with my security organization. Why is that not available in all the report formats?"

"Nexpose does a great job of scanning for my data. How do I get my data out?"

Have you asked a similar question? Well, we heard you loud and clear. Over the last year we've been investing in a solution that addresses all of these questions. This solution has been the foundation of the Custom Reports developed within the Reporting side street, and the new custom reports delivered in the last year. Now is the time for you to start leveraging this capability as well. So what is this mysterious capability? The secret is now out, something we call the Reporting Data Model. The Reporting Data Model is a clean, clear, well-defined, and versioned interface to your data with a simple human-readable or machine-parsable output format. By creating an interface to raw data, we are now capable of exposing this not only through Custom Report Templates, but through user-defined reports.

We are pleased to announce this new reporting capability is exposed in the SQL Query Export report as of version 5.8.2. This short video gives a quick introduction to this new feature: SQL Query Export Reports.

What is the SQL Query Export?

The SQL Query Export is an export report format that allows you to define a query using the Structured Query Language (SQL) syntax to output data into a Comma-separated Value (CSV) format. A dimensional model is exposed as the interface to which the SQL queries are run against. The following demonstrates what the configuration of a SQL Query Export report looks like:

What is a dimensional model?

The Reporting Data Model is a dimensional model that provides the interface for your SQL queries. Dimensional modeling is a data warehousing technique that focuses on structuring data in a business-friendly, understandable representation to allow customized slicing and dicing. Dimensional modeling is a four step design process. The result of this modeling process is a set of facts and dimensions. Facts are the measurements, typically numerical, that are acquired by the business. In the case of Nexpose, this means all your glorious scan data: vulnerability results, counts, risk scores, etc. Dimensions are the business context, and are typically textual data. In Nexpose, this includes configuration information like sites and asset groups, as well as all of the textual data that links to the factual scan data like operating system information, asset metadata, etc. Combining facts and dimensions in a denormalized relational view, the Reporting Data Model allows you to gather, group, aggregate, and process this data in any way you wish.

Refer to the Creating reports based on SQL queries help section of the Nexpose User's Guide for complete documentation of the Reporting Data Model. For general information about dimensional modeling, refer to the Kimball Group's Dimensional Modeling Techniques.

Why is the output in CSV?

As the dimensional model exposed in the Reporting Data Model is built on a relational database, the output is a two-dimensional table with rows and columns. This format translates very simply and easily to the CSV format, and allows you to easily load the data into numerous existing tools, most notably office tools like Excel or LibreOffice, which are great at digesting tabular data.

Why not output in XML or JSON?

The XML and JSON formats are better candidates for representing hierarchical rather than tabular data. We are considering adding support for these formats in the future, so if this is interesting to you, let us know.

How is this different than existing reports?

Given the various types of export formats Nexpose currently supports, let me describe the differences and similarities quickly with the most popular export formats:

Existing Report FormatAdvantages vs SQL Query ExportDisadvantages vs SQL Query Export
XML Export 2.0
  • Requires no special configuration or SQL knowledge
  • Not human readable
  • Data limited to asset and vulnerabilities
CSV Export
  • Simple drag and drop configuration of columns in custom templates
  • Exports only vulnerability check results
  • No control over the content of the rows, just the columns
Database Export
  • Allows direct DB connections (e.g. JDBC and ODBC)
  • Requires configuration of an external database
  • Export schema not versioned
Data Warehouse Export
  • Allows direct DB connections (e.g. JDBC and ODBC)
  • Requires configuration of an external database
  • Export schema not versioned

As you can see, the new SQL Query Export provides a complementary alternative to many of the existing formats. Please continue using these formats if you already have them integrated into your security workflow.

What do I need to start using it?

To get started with using this feature you will need to familiarize yourself with the content and structure of the Reporting Data Model, and be familiar with how to write basic SQL queries. Most SQL queries against the Reporting Data Model will require no more complex SQL than natural joins, ordering, and perhaps grouping. The SQL can be as complex or as simple as you want it to be.

What if I don't know how to write SQL?

Open discussions and questions on the  Reporting side street with information about what data you want to return, and we'd be happy to help you get started - we have numerous in-house SQL experts willing to help. You can also get acquainted with dimensional modeling and SQL tips and tricks in our SQL Query Export: Development Guide and Best Practices.

What SQL syntax is supported?

The SQL Query Export materializes the dimensional model using the PostgreSQL relational database. As a result, full PostgreSQL syntax is supported, including built-in functions, operators, and data types.

What data can I report on?

  • Asset metadata
    • Host names, IP addresses/aliases, MAC Address, operating system, device type
    • Users, groups, services, software
  • Scan results
    • Vulnerability findings, instances
    • Aggregated by sites, assets, asset groups, or even across your entire system
  • Vulnerability exceptions
    • Approved and pending exceptions
    • Vulnerabilities they apply to
  • Vulnerability definitions
    • Summaries, descriptions, external references
  • Vulnerability solutions
    • Summaries, remediation steps, supercedence information
  • and much more...

What data can I not report on (yet)?

The following are areas where we are planning to expose in the Reporting Data Model in the near future:

  • Policy and configuration compliance results
  • Scan engines
  • Scan templates
  • Scan schedules

Do you have any examples?

Of course! We have several examples included directly within the help in the product. These are a great place to start when you get SQL writer's block.

In addition, we'll be posting several examples in upcoming blogs and documents that can show you what type of business problems can be solved. See the SQL Query Export Example: Vulnerability Coverage for starters.

What versions of Nexpose is this supported in?

This enhanced reporting capability is available in version 5.8.2, but is not enabled in Community licenses. Please contact a member of the sales team if you would like to purchase a license or perform an evaluation.

What else are you planning?

We are planning on several upcoming improvements to the SQL Query Export and Reporting Data Model capabilities, so please give us feedback so we may incorporate your ideas as well.