FMC database external access

[Isaiah]

I am trying to get external database access to my FirePOWER Management Center.  I can connect to the database just fine with the drivers downloaded from the FMC, but every SELECT query that I issue gives me an error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1".

This happens regardless of whether I manually specify a limit parameter in the query or not.  For example, these two queries both return the same error: select count(first_packet_sec) from connection_log; select first_packet_sec from connection_log limit 10;

It looks like the MySQL version running on the FMC is 5.6.24 (the FMC itself is version 6.0.1 (build 1214)), and the "SET OPTION" command was deprecated and removed in MySQL 5.6.  So the question is, where is that coming from and why?

[Patrick_Warren]

Sorry to dig up an old thread, but were you able to resolve this? I am running into the same issue when trying to query the FMC database. 

[Marvin Rhoads]

What information are you trying to retrieve?

Generally speaking the RESTful API would be a better and supported method to interact with FMC programmatically.

[Patrick_Warren]

Thank you. I will research that. 

I am initially looking for a way to query connection events from outside of the FMC gui interface.

[Marvin Rhoads]

You can configure your policies to send connection events via syslog or eStreamer. Many customers use something like this for external analysis using a SIEM like Splunk, Logrhythm or Arcsight.

I don't believe connection events are queryable using the REST API.

https://www.cisco.com/c/en/us/td/docs/security/firepower/630/api/REST/Firepower_Management_Center_REST_API_Quick_Start_Guide_630/Objects_in_the_REST_API.html

[reached-dp1]

You should be able to query the MySQL database on the FMC provided you go through the installation of the client-side certificate as per the Cisco Database connection on FMC.

You need to query the connection log database schema.

Query sample:

SELECT first_packet_sec,last_packet_sec,initiator_ipaddr,initiator_port,responder_ipaddr,responder_port,security_zone_ingress_name,security_zone_egress_name,protocol_name,url FROM connection_log ORDER BY first_packet_sec LIMIT 10;

Few things to note.  The IP addresses are in Hex and must be converted to decimal. So A3972DF becomes 10.57.114.223.

And the first_packet_sec timestamp is in Unix time (epoch).

So 1657125720 becomes Wed Jul 06 2022 16:42:00 GMT+0000

So your MySQL connection query needs to be tweaked depending on what attributes you are looking for and then ingest into the ELK or other SIEM.

Hope that helps and good luck!