Last updated at Mon, 21 Aug 2017 16:46:52 GMT

This morning we published the release of the new SQL Query Export report. Simultaneously the Nexpose Gem has released version 0.6.0 to support this new report format in all the reporting API calls (you must update to this latest version to run the report). When the SQL Query Export is paired with adhoc-report generation, you are able to write simple yet powerful custom scripts using the API. Let's walk through an example.

Example

The following example uses the Ruby Gem to invoke the API with a query that returns the metadata for all unauthenticated assets:

require 'nexpose'
require 'csv'

include Nexpose

query = "
  SELECT da.ip_address, da.host_name, da.mac_address, dos.description AS operating_system 
  FROM dim_asset da 
     JOIN dim_operating_system dos USING (operating_system_id) 
  WHERE da.asset_id IN ( 
     SELECT DISTINCT asset_id 
     FROM dim_asset_operating_system 
     WHERE certainty < 1 
  ) 
  ORDER BY da.ip_address"

@nsc = Connection.new('localhost', 'nxadmin', 'nxadmin')
@nsc.login

report_config = Nexpose::AdhocReportConfig.new(nil, 'sql')
report_config.add_filter('version', '1.1.0')
report_config.add_filter('query', query)

report_output = report_config.generate(@nsc)
csv_output = CSV.parse(report_output.chomp, { :headers => :first_row })

puts csv_output

@nsc.logout

This example creates a query to find all the assets with an operating system that does not have a high level confidence (indicative of improper credential configuration). Note: This example is built in to the product within the inline help. After building the SQL query, an adhoc-report configuration is constructed with two new filters. The SQL Query Export report takes two new filter types (in addition to the existing scope filters). The new filters are "version" and "query". The version identifies the version of the Reporting Data Model being queried against. For now, only version "1.1.0" is supported. This option matches what is visible in the user interface for configuring this type of report. The second filter is a "query" filter. This value is the query that you want to run. Both "version" and "query" are required filters for this report type ("sql"). The query can itself inherently filter the data in the output using WHERE clauses, but the report will also honor any scope filters that are applied via "site", "scan", "device", "group", and vulnerability filter types. Remember, the larger the scope and report output, the longer the report will take to generate and download, so use your scoping filters wisely for large reports.

After the adhoc-report is run successfully, the output can be parsed using a CSV library and further process. The example above simply echos the output to standard out, but we are sure you can find more creative ways to use the output data.

Errors

If the SQL query you specify is invalid, an error message will be returned helping point out what the problem is in the syntax. For example, if we misspelled "DISTINCT", then the following would be returned by this script:

NexposeAPI: Action failed: The query filter supplied is invalid: (Nexpose::APIError)
ERROR: column "distint" does not exist
Column: 216
State: undefined_column

Next Steps

For more examples you can try, refer to the Nexpose Reporting side street. Show us how you plan to leverage this capability and don't hesitate to ask questions or start discussions.