Firepower System Database Access Guide v6.7 - 7.1

Sample Code for Custom Java Programs

The RunQuery source code uses the functions discussed below. These code samples illustrate one of several possible implementation approaches.

Dynamically setting the SSL provider connection

After you install the SSL security certificate on your client (see Installing the Client SSL Certificate), you can dynamically register your JSSE provider with the following line in your program:

Initializing the JDBC driver for your program

You can load the JDBC driver class in your Java application using the Class.forName() method, as follows:

If your program launches from the command line, the user supplies the JDBC class as follows:

where program_name is the name of your program.

Connecting the program to the database

Your program must obtain a JDBC connection object before it can submit queries. Use the DriverManager.getConnection method as follows to establish the connection and get the connection object:

where my_dc is either the FQDN or the IP address for the Firepower Management Center, user is the database access user account name, and password is the account password.

Querying the data in the Cisco tables

Create an SQL query object to submit the query and assign the retrieved records to a result set, as follows:

where sql is the SQL query. See Querying the Database for supported SQL functions.

Producing the results of a table query

With the result set ( rs) generated by the above query, you can output the fields as follows:

Getting schema information

Your program can list the tables in the database, as follows:

Your program can list a table’s columns, as follows:

where table_name is the name of the database table.

Running the Application

Before you run your application, you must set the CLASSPATH on the client computer to include the current directory and the locations of your application’s JAR files.

If you downloaded and unpacked the ZIP package for Database Access as noted in Downloading the JDBC Driver, update the CLASSPATH as follows:

To run the application in a Unix environment:

Step 1 Use the following command:

where path is the directory path where you unpacked the ZIP package downloaded from the Firepower Management Center.

To run the application in a Windows 7 environment:

Step 1 Right-click the Computer icon and select Properties.

The System window appears.

Step 2 Click Advanced System Settings.

The System Properties window appears.

Step 3 Select the Advanced tab.

Step 4 Click Environment Variables....

The Environment Variables window appears.

Step 5 Select the CLASSPATH system variable and click Edit....

The Edit System Variable window appears.

Step 6 Add the following to the Variable value: field:

where path is the directory path where you unpacked the ZIP package downloaded from the Firepower Management Center.

Step 7 Click OK to save the value.

The Environment Variables window appears.

Step 8 Click OK to save the value. You can now run the application.

Supported SHOW Statement Syntax

The SHOW statement lists all tables in the Firepower System database. The following represents the supported MySQL SHOW statement syntax you can use when querying the Firepower System database:

Any SHOW statement syntax not listed above is not supported.

Supported DESCRIBE or DESC Statement Syntax

The Firepower System database offers limited use of the DESCRIBE statement. In the Firepower System database, the output of the DESCRIBE statement only lists the names of the columns and the type of data in each column. The following represents the supported MySQL DESCRIBE statement syntax you can use when querying the Firepower System database:

DESCRIBE table_name ;

The Firepower System database also supports the identical command DESC :

DESC table_name;

Any DESCRIBE statement syntax not listed above is not supported. In particular, the Firepower System database access feature does not support:

• the INDEX FOR clause
• the TABLE clause
• the PROCEDURE clause

Supported SELECT Statement Syntax

The following represents the supported MySQL SELECT statement syntax you can use when querying the Firepower System database:

SELECT

[ALL | DISTINCT]

[COUNT ( field ) | COUNT (*) ]

select_expr [, select_expr... ]

FROM table_references

[WHERE where_condition]

[GROUP BY { column_name | position } [ ASC | DESC ], …]

[HAVING where_condition]

[ORDER BY { column_name | position } [ ASC | DESC ], …]

[LIMIT { [offset,] row_count | row_count OFFSET offset}]

The following table details the required syntax for the clauses and arguments in the above SELECT statement.

If you are not familiar with how supported MySQL syntax is expressed, see the following table for tips.

Any SELECT statement syntax not listed above is not supported. In particular, the Firepower System database access feature does not support:

• SELECT *, that is, you must explicitly specify fields
• unions
• subqueries
• the WITH ROLLUP modifier to the GROUP BY clause
• the INTO clause
• the FOR UPDATE clause

Join Constraints

The joins you can perform on Firepower System database tables are limited, for performance and other practical reasons. Cisco does not allow you to perform joins where the result is not likely to be useful for event analysis.

You can perform only inner joins and left (outer) joins. Nested joins, cross joins, natural joins, right (outer) joins, full (outer) joins, and joins with the USING clause are not supported.

The schema documentation indicates the supported joins for each table. Joins not listed are not supported. For example, you cannot join the compliance_event and intrusion_event tables on IP address fields, even though both tables contain IP address information. In addition, joins on deprecated tables and deprecated fields are not listed.

Querying Data Stored in Unfamiliar Formats

The Firepower System database stores some data in formats that may not be display-friendly. The following sections detail how to perform queries on various fields so your queries are successful and your results appear as expected:

• IPv6 Addresses
• IPv4 Addresses
• MAC Addresses
• Packet Data
• UNIX Timestamps

IPv6 Addresses

The Firepower System database stores IPv6 addresses in binary format. For results in hex notation, use the HEX() function. To query the database on a specific IPv6 address, use the UNHEX() function.

For example, the following statement queries the connection_log table, which contains information on monitored sessions, constraining the query by a specific IPv6 address:

SELECT HEX(initiator_ip), HEX(responder_ip), packets_sent, bytes_sent

FROM connection_log

WHERE initiator_ip = UNHEX('20010db8000000000000000000004321');

IPv4 Addresses

The Firepower System database stores IPv4 addresses in binary format within the same fields as IPv6 addresses. As with IPv6 addresses, use the HEX() function for hex notation. The database follows the RFC by filling in bits 80-95 with 1s, which yields an invalid IPv6 address. For example, the IPv4 address 10.5.15.1 would be stored as 00000000000000000000FFFF0A050F01.

MAC Addresses

The Firepower System database stores MAC addresses in binary format. For results in hex notation, use the HEX() function.

For example. the following statement queries the rna_host_mac_map table, which contains information on hosts with MAC addresses that are not identified with an IP address, limiting the query to the first five hosts:

SELECT HEX(host_id), HEX(mac_address)

FROM rna_host_mac_map

LIMIT 5;

Packet Data

The Firepower System database stores packet data for intrusion events in binary format. For results in hex notation, use the HEX() function.

For example. the following statement queries the intrusion_event_packet table to obtain packet data for a particular event:

SELECT HEX(packet_data)

FROM intrusion_event_packet

WHERE event_id = 1234;

UNIX Timestamps

The Firepower System database stores most timestamps as UNIX timestamps, which represent the number of seconds elapsed since 00:00:00 January 1st, 1970 (UTC). For results in your local time, use the FROM_UNIXTIME() function.

For example, the following statement queries the audit_log table, which keeps a record of all user actions on the web interface of an appliance, and returns up to 25 results:

SELECT FROM_UNIXTIME(action_time_sec), user, message

FROM audit_log

LIMIT 0, 25;


Keep in mind that all times in the database are in UTC. Although the CONVERT_TZ() function is allowed, it only provides results in UTC.

Note that some events have microsecond resolution associated with them. Use the CONCAT() and LPAD() functions to concatenate the UNIX timestamp and the microsecond increment. For example, the following statement queries the intrusion_event table:

SELECT CONCAT(FROM_UNIXTIME(event_time_sec)), '.', LPAD (event_time_usec, 6, '0')), HEX(host_id),

rule_message

FROM intrusion_event

LIMIT 0, 25;


To query the database for events with a particular UNIX timestamp, use the UNIX_TIMESTAMP() function.

Limiting Queries for Performance Reasons

Although the system limits the joins you can perform on Firepower System database tables, it does still allow some expensive queries - queries that may negatively impact the performance of your Firepower Management Center.

Therefore, you should try to limit the result set for large tables. Strategies include:

• constraining queries to a specific leaf domain
• constraining queries to a specific time range
• constraining queries by IP address
• using the LIMIT clause

Depending on your deployment, querying many tables may require a limited result set. In particular, the following tables can contain up to 100 million events on a DC3000:

• fireamp_event
• intrusion_event
• intrusion_event_packet
• connection_log (pre-Version 5.0 name: rna_flow)
• connection_summary (pre-Version 5.0 name: rna_flow_summary)

Queries on network map tables may also be expensive, depending on the number of hosts the system has detected on your monitored network.

Query Tips

The following sections provide tips on ensuring unique results when you build queries that include detection engines or intrusion events.

Device Names

Device names are not necessarily unique across multiple Firepower Management Centers. To ensure uniqueness, include a specific device UUID in your query.

Intrusion Events

To uniquely match an intrusion event across multiple managed devices, include the following fields in your query of the intrusion_event table:

– intrusion_event.event_id

– intrusion_event.event_time_sec

– intrusion_event.sensor_uuid

Audit Records for a User

The following query returns all records in the audit log for a particular user, displaying all timestamps in UTC:

SELECT FROM_UNIXTIME(action_time_sec), user, message

FROM audit_log

WHERE user = 'eventanalyst';

Intrusion Events by Priority and Classification

The following query duplicates the Drilldown of Event, Priority, and Classification view in the Events By Priority and Classification workflow. If you have not changed the default Intrusion Events workflow in your user preferences, this is the first page you see when you select Analysis > Intrusion Events on the Firepower Management Center web interface:

SELECT rule_message, priority, rule_classification, count(*) as Count

FROM intrusion_event

WHERE reviewed="0" GROUP BY rule_message, priority, rule_classification

ORDER BY Count

DESCLIMIT 0, 25;

Intrusion Events and Their Associated Policies

The following query lists intrusion events from the specified week. For each event it shows the associated intrusion policy violation and rule classification.

SELECT FROM_UNIXTIME(event_time_sec) AS event_time, event_id AS intrusion_event, intrusion_event_policy_name AS policy, rule_classification AS classification

FROM intrusion_event

WHERE event_time_sec BETWEEN UNIX_TIMESTAMP(’2011-10-01 00:00:00’) AND

UNIX_TIMESTAMP(’2011-10-07 23:59:59’)

ORDER BY policy ASC;

Lists of Detected Hosts

The following query returns the basic information in the hosts network map for all MAC hosts (hosts without an IP address) detected on your network, along with the hardware vendor for each NIC:

SELECT HEX(mac_address), mac_vendor, host_type, FROM_UNIXTIME(last_seen_sec)

FROM rna_mac_host;


The following query maps IP addresses to MAC addresses:

SELECT HEX(ipaddr), HEX(mac_address), HEX(host_id)

FROM rna_host_ip_map LEFT JOIN rna_host_mac_map on

rna_host_ip_map.host_id=rna_host_mac_map.host_id;

List of Detected Servers

The following query joins two related tables to give you a list of the servers detected on your network along with many of their attributes, similar to what you can see in a table view of servers on the Firepower Management Center’s web interface:

SELECT FROM_UNIXTIME(s.last_used_sec), HEX(s.host_id), s.port, s.protocol, s.hits,

i.service_name, i.vendor, i.version, i.source_type, s.confidence

FROM AS s

LEFT JOIN rna_ip_host_service_info AS i ON (s.host_id = i.host_id AND s.port = i.port AND s.protocol =

i.protocol);


Note that this query left joins the tables on the set of host_id, port, and protocol, as required by Database Access. See rna_host_service Joins and rna_host_service_info Joins.

Server Vulnerabilities on Your Network

The following query joins two vulnerability-related tables to give you a list of valid server-related vulnerabilities detected for a particular host, along with whether each vulnerability is exploitable across a network:

SELECT h.rna_vuln_id, v.title, v.remote

FROM rna_host_service_vulns AS h

LEFT JOIN rna_vuln AS v ON (h.rna_vuln_id = v.rna_vuln_id)

WHERE h.ip_address = INET_ATON('10.10.10.4')

AND h.invalid = 0;


Note that this query left joins the tables on rna_vuln_id, as required by rna_host_service_vulns and rna_vuln Joins.

Operating System Summary

The following query duplicates the Summary of OS Names page in the Operating System Summary workflow. If you have not changed the default workflow in your user preferences, this is the first page you see when you select Analysis > Hosts on the Firepower Management Center web interface, then select Hosts :

SELECT vendor, product, count(*) AS total

FROM rna_host_os

GROUP BY vendor, product

ORDER BY total DESC;

Operating System Vulnerabilities for a Host

The following query joins two vulnerability-related tables to give you a list of valid operating system-related vulnerabilities detected for a particular host, along with whether each vulnerability is exploitable across a network:

SELECT h.rna_vuln_id, v.title, v.remote

FROM rna_host_os_vulns AS h

LEFT JOIN rna_vuln AS v ON (h.rna_vuln_id = v.rna_vuln_id)

WHERE h.host_id = UNHEX('9610B6E6F1784DA4B39BEA7A210AAD68')

AND h.invalid = 0;


Note that this query left joins the tables on rna_vuln_id, as required by Database Access. See rna_host_os_vulns and rna_vuln Joins.

Host Violation Count

The following query duplicates the Host Violation Count page in the Host Violation Count workflow. If you have not changed the default Compliance White List Violations workflow in your user preferences, this is the first page you see when you select Analysis > Correlation > White List Violations on the Firepower Management Center’s web interface.

SELECT host_id, HEX(host_id), white_list_name, count(*) AS total

FROM white_list_violation

GROUP BY host_id, white_list_name

ORDER BY total DESC;