Exporting Nessus Data into SQLite

Nessus plugin data is the most valuable set of data Tenable provides. Tenable has 170,232 plugins and over 21,000 are Informational plugins which add context about the asset scanned.

Advanced automation use-cases often involve exporting data from multiple security tools to help prioritize vulnerability remediation given the different viewpoints provided. Nessus plugin data is often used as the glue between other security tools given the amount of information Tenable could have on an asset.

There are many different storage methods available to execute advanced automation. For example: I’ve helped build custom integrations with Tableau, Excel, MongoDB, SQLite, AWS S3 Buckets, and PostgreSQL.

In all cases the flow is similar:

1. Export plugin data and/or asset data
2. Search the data for something interesting
3. Parse and/or transform the desired results
4. Take some action

In an effort to simplify our python example, we will use SQLite to store the data. SQLite is easy to deploy and administer especially for ephemeral use-cases since it is built into python.

Learn more about SQLite

To drive our decisions in this example, we will be designing a solution to identify assets by plugin data and tag them in Tenable.io for advanced reporting and Role Based Access Controls. The use-cases and rationale will be covered in-depth in my soon-to-be-released article, “Tagging Assets by Vulnerability Data”. While we are focused on tagging assets, the concepts in this article can help you with most custom integrations and advanced use-cases.

Common Use-cases

Most assets in an environment have multiple identities. The business identity of an asset is rarely its IP address or Fully-Qualified Domain Name(FQDN). The most common identifier used amongst internal business groups is the software or service running on the asset.

Knowing the service or software running on an asset helps assume ownership and therefore route assets to those who own them for remediation actions.

With that in mind our goal is to group assets by the plugin data found by Nessus and stored in Tenable.io. There are four core methods to tag our assets that we will cover. Each method has potentially thousands of individual use-cases. Below each method, I’ve added a single common broad use-case. These use-cases will be expanded on further in my next article “Tagging Assets by Vulnerability Data”.

Grouping Assets — How/Why — High-level

• How: Tagging by Text found in the Plugin Output
  - Why: Tag by Software/Packages installed for asset ownership
• How: Tagging by Text found in the Plugin name
  - Why: Tag by Headline vulnerabilities(log4J) for tactical remediation
• How: Tagging by Port found open
  - Why: Tag by a custom application for advanced asset ownership
• How: Tagging by Plugin ID
  - Why: Tag by credential failures to improve data accuracy

Plan the Database Structure

Each API use-case may require keeping more data returned. Since we are tagging on plugin data we need the obvious fields:

• Plugin ID
• Plugin Name
• Plugin Output
• Port
• UUID

We need the asset unique identifier referenced in the API as UUID; this will be required to identify and tag assets later.

After reviewing the pyTenable documentation and Tenable.io API documentation we determined we only need to utilize the vulnerability export APIs. The vulnerability export returns all of the vulnerabilities per asset and contains all of the data that is required for our four methods. As such our database structure is extremely simple: a single table with five columns.

General workflow

There are four simple steps when creating our SQLite database. Since we are building our database on the fly using python we will use functions for simplicity and readability.

1. Connect to the database
2. Create the vulnerability table
3. Create a way to insert data into the database
4. Create a way to query the database

Note: There are a number of ways to accomplish this task. This project was built this way for explainability and to reduce complexity. Advanced long-term projects will require a more thoughtful approach and potentially a more robust database.

Code Walk-Through

Before You Begin

This article assumes that you have learned how to authenticate to the Tenable.io API using pyTenable as explained in the Introduction to pyTenable article. Additionally, the examples provided below use an instantiated pyTenable object called tio as described in the Introduction to pyTenable article.

Connect to the Database

While this could be simplified further into a single line of code, sqlite3.connect(db_file), we are wrapping our SQLite database connection with a try block for routing SQLite errors. Our function takes our database file, creates a connection and returns an SQLite object for further use or prints an error.

Note: This function will be used every time we make a connection to the database.

Function to connect to a database

Create the vulnerability table

Now that we have a way to connect to our database we need to build the table structure. Our database is cleverly named after Tenable’s stock ticker: TENB.db. We then use our new_db_connection() function to connect to the database so we can create our table should it not exist.

As previously planned we create single table vulns and our five columns:

1. asset_uuid
2. plugin_id
3. output
4. plugin_name
5. port

Note: Because our goal is very simple and doesn’t require specific data types we will set all of the table data types to text.

Learn more about SQLite Datatypes

Function to Create the vulns table.

Create a function to insert data into the database

When we get to exporting data, we need a way to insert what data we want to keep into our database. Utilizing the five attributes we defined early our SQL insert query takes form:

“INSERT or IGNORE into vulns(asset_uuid, output, plugin_id, plugin_name, port” VALUES (?, ?, ?, ? ,?)

SQLite requires we utilize a cursor object before executing our insert statement defined here as cur.

cur = conn.cursor()

Last, to make our queries faster we are using “Write-Ahead Logging” by executing a pragma statement.

cur.execute(‘pragma journal_mode=wal;’)

Learn more about pragma statements.

Learn more about Write-Ahead-Logging(WAL)

Function to insert data into our Database

Create an easy way to query the database

After the data is populated into the database we need a way to query the data to identify assets that match our query. Just as before, we need to create a connection to our database, which is the first two lines of our db_query() function.

database = r"TENB.db"
query_conn = new_db_connection(database)

Once we have a connection to our database we initiate a cursor object and execute the statement that is passed to our function.

with query_conn:
    cur = query_conn.cursor()
    cur.execute(statement)

The fetchall() method in SQLite library will pull all of the rows matching our query; we then assign it to our data variable.

    data = cur.fetchall()

Finally, we close a connection to our database and return our data variable.

Function to query our Database

Exporting and Importing Data

It’s time to fill our SQLite database with plugin data. We are going to use all four of the functions we created earlier. Let’s start off by assigning our database, TENB.db, and making the connection to the database.

    database = r"TENB.db"
    vuln_conn = new_db_connection(database)

Before we start the export we need to create the vulnerability table by calling our create_vulns_table() function.

    create_vulns_table()

While we are connected to the database we kick off our Tenable.io export request. pyTenable reduces what would be over a 100 lines of code into a single line for exporting data: tio.exports.vulns() and it returns an iterator. Leaving our responsibility to looping through the data. As we loop through each asset in each chunk returned we need to save the five attributes we planned on earlier.

    with vuln_conn:
       for vulns in tio.exports.vulns():
            asset_uuid = vulns['asset']['uuid']
            port = vulns['port']['port']
            plugin_id = vulns['plugin']['id']
            plugin_name = vulns['plugin']['name'

Not every plugin has an output and to handle this condition we utilize a try block. When we encounter this condition python throws a Key Error indicating there isn’t an output. We can catch the error and set the output to None so our loop can continue.

            try:
                output = vulns['output']
            except KeyError:
                output = None

To ensure the right data gets imputed into the correct field in the database we structure our list in the same order as our table is designed. Getting this order mixed up will pollute our database.

data_list = [asset_uuid, output, plugin_id, plugin_name, port]

Take a look: To see an example of all of the data returned by the vulnerability export APIs check the Tenable developer portal.

Finally, we call our insert_vulns function to insert each row as we loop through the data.

insert_vulns(vuln_conn, data_list)

Main function to execute our export

Wrapping Up

After running our newly created function export(), we need to verify our data was inserted properly. Let’s use our db_query() function to run a simple select statement for data verification. Using the count function built-in to SQLite we can count the number of plugins saved.

print(db_query(“select count(*) from vulns;”))

In my small lab, [(6008,)] was returned; indicating 6008 plugins downloaded into our TENB.db database. Now we have a way to get data into a database and a way to query the data for results.

No-Code Solution

Before seeing the full code solution, let’s briefly review a no-code solution; Navi. Navi downloads most of the important vulnerability and asset data fields. Navi downloads vulnerability data, asset data, web application data and compliance data into a SQLite database called navi.db

To download vulnerability data(30 days) and asset data(90 days) simply run the below command after entering your API keys.

navi update full

Then query the database by using the below command

navi find query "select count(*) from vulns;"

Learn more about Navi

The Code