PSQL - Show all centrally managed Check Point systems

[Danny]

🏆 PSQL wizardry!
✔️ Works on all Single Domain Managements
👉 Directly queries the management database

PSQL query to show all centrally managed Check Point systems of a Check Point Security Management (SmartCenter).
In expert mode run:

psql_client monitoring postgres -c "SELECT a.gateway, s.cp_shared_version AS version, MAX(CASE WHEN p.deployment_packages_package_name ~ '(JHF_T|Bundle_T)[0-9]+' THEN 'Take '||REGEXP_REPLACE(p.deployment_packages_package_name, '.*(?:JHF_T|Bundle_T)(\\d+).*', '\\1') ELSE p.deployment_packages_package_name END) package_or_take, a.ip, STRING_AGG(DISTINCT e.family, ', ') AS families, s.fw_policy_name, s.fw_install_time AS last_policy_install FROM alive_gateways a JOIN statuses_view s USING (obj_id) LEFT JOIN entitlement_view e USING (obj_id) LEFT JOIN os_installed_packages_view p USING (obj_id) GROUP BY a.gateway, s.cp_shared_version, a.ip, s.fw_policy_name, s.fw_install_time ORDER BY a.gateway;"

Greetings to: @Kaspars_Zibarts, @Bob_Zimmerman 
Todo: Add support for Multi-Domain Security Managements (MDS)

[the_rock]

Just ran it in the lab...amazing.

[Expert@CP-MANAGEMENT:0]# psql_client monitoring postgres -c "SELECT alive_gateways.gateway, statuses_view.cp_shared_vrsion AS version, REGEXP_REPLACE(os_installed_packages_view.deployment_packages_package_name, '.*(?:Bundle_T|JHF_T)(\d).*', 'Take \1') AS jhf, alive_gateways.ip, string_agg(DISTINCT entitlement_view.family, ', ') AS families, statuses_vew.fw_policy_name, statuses_view.fw_install_time AS last_policy_install FROM alive_gateways JOIN statuses_view USING (bj_id) JOIN entitlement_view USING (obj_id) JOIN os_installed_packages_view USING (obj_id) WHERE os_installed_packagesview.deployment_packages_package_name LIKE 'Check_Point_%' GROUP BY alive_gateways.gateway, statuses_view.cp_shared_vesion, os_installed_packages_view.deployment_packages_package_name, alive_gateways.ip, statuses_view.fw_policy_name, sttuses_view.fw_install_time;"
gateway | version | jhf | ip | families |
fw_policy_name | last_policy_install
---------------+---------+-----------------------------------------------------+---------------+---------------------+
---------------+--------------------------
CP-FW-01 | R81.20 | Take 119 | 172.16.10.248 | Access, DLP, Threat |
LAB-POLICY | Tue Oct 28 10:15:34 2025
CP-FW-02 | R81.20 | Take 119 | 172.16.10.247 | Access, DLP, Threat |
LAB-POLICY | Tue Oct 28 10:15:34 2025
CP-GW | R82 | Take 43 | 172.16.10.249 | Access, DLP, Threat |
LAB-POLICY | Tue Oct 28 10:15:37 2025
CP-MANAGEMENT | R82 | Take 43 | 172.16.10.252 | Management |
|
CP-MANAGEMENT | R82 | Check_Point_SmartConsole_R82_jumbo_HF_B1053_Win.tgz | 172.16.10.252 | Management |
|
CP-SMARTEVENT | R82 | Take 43 | 172.16.10.244 | Management |
|
(6 rows)

[Expert@CP-MANAGEMENT:0]#

[PhoneBoy]

Directly from Postgres...nice 🙂

[Bob_Zimmerman]

It works on an R82 MDS, it just doesn't tell you which CMA a given firewall belongs to. A bigger issue is it seems to sometimes (but not always!) include more packages than it should. Here's an example from one of my managements:

        gateway         | version |                          jhf                          |       ip       |      families       | fw_policy_name |   last_policy_install    
------------------------+---------+-------------------------------------------------------+----------------+---------------------+----------------+--------------------------
 SomeCluster-fw01       | R81.20  | Check_Point_R81.20_T634_Fresh_Install_and_Upgrade.tgz | 10.100.200.101 | Access, DLP, Threat | Standard       | Thu Oct 30 04:32:39 2025
 SomeCluster-fw01       | R81.20  | Take 105                                              | 10.100.200.101 | Access, DLP, Threat | Standard       | Thu Oct 30 04:32:39 2025
 SomeCluster-fw02       | R81.20  | Take 105                                              | 10.100.200.102 | Access, DLP, Threat | Standard       | Thu Oct 30 04:32:39 2025

Changing the 'WHERE' clause to this dealt with the problem:

WHERE os_installed_packages_view.deployment_packages_category = 'jumbo'
AND os_installed_packages_view.deployment_packages_package_name LIKE 'Check_Point_R%'

[Danny]

@Bob_Zimmerman : Thanks for the quick MDS test. Adding the CMA domain_id is pretty easy, as shown below. I just need to find a table or view to get the CMA name from, so that I don't have to show its id instead.

psql_client monitoring postgres -c "SELECT alive_gateways.gateway, statuses_view.cp_shared_version AS version, REGEXP_REPLACE(os_installed_packages_view.deployment_packages_package_name, '.*(?:Bundle_T|JHF_T)(\d+).*', 'Take \1') AS jhf, alive_gateways.ip, string_agg(DISTINCT entitlement_view.family, ', ') AS families, statuses_view.fw_policy_name, statuses_view.fw_install_time AS last_policy_install, alive_gateways.gateway_domain AS domain_id FROM alive_gateways JOIN statuses_view USING (obj_id) JOIN entitlement_view USING (obj_id) JOIN os_installed_packages_view USING (obj_id) WHERE os_installed_packages_view.deployment_packages_package_name LIKE 'Check_Point_R%' GROUP BY alive_gateways.gateway, statuses_view.cp_shared_version, os_installed_packages_view.deployment_packages_package_name, alive_gateways.ip, statuses_view.fw_policy_name, statuses_view.fw_install_time, alive_gateways.gateway_domain;"

[Vincent_Bacher]

@Danny Regarding domain info: In cpm database you can see the relationship between uid and name.

psql_client cpm postgres -c "SELECT objid, name FROM domainbase_data WHERE deleted = false AND editingsession = -1 ORDER BY name;"

But i don't know how to combine both queries into one to have a one-liner including domain name as clear text.

[Bob_Zimmerman]

It's easy to make that output into a sed script to replace the domain's UUID with the name:

[Expert@DallasSC:0]# sedScript=$(mktemp);psql_client cpm postgres -c "SELECT objid, name FROM domainbase_data WHERE deleted = false AND editingsession = -1 ORDER BY name;" | egrep '^ [0-9a-f]' | sed -E 's#^ ([-0-9a-f]+) \| (.+)#s/\1/\2/g#' >"${sedScript}"

[Expert@DallasSC:0]# psql_client cpm postgres -c "SELECT objid, name FROM domainbase_data WHERE deleted = false AND editingsession = -1 ORDER BY name;"
                objid                 |       name       
--------------------------------------+------------------
 8bf4ac51-2df7-40e1-9bce-bedbedbedbed | APPI Data
 a0bbbc99-adef-4ef8-bb6d-defdefdefdef | Check Point Data
 1e294ce0-367a-11e3-aa6e-0800200c9a66 | Global
 a0bbbc99-adef-4ef8-bb6d-cebcebcebceb | IPS Data
 31ab94da-4ab1-5da9-a03d-ddddddaaaaaa | LOG Data
 41e821a0-3720-11e3-aa6e-0800200c9fde | SMC User
 a0eebc99-afed-4ef8-bb6d-fedfedfedfed | System Data
(7 rows)

[Expert@DallasSC:0]# !! | sed -f "${sedScript}"
psql_client cpm postgres -c "SELECT objid, name FROM domainbase_data WHERE deleted = false AND editingsession = -1 ORDER BY name;" | sed -f "${sedScript}"
                objid                 |       name       
--------------------------------------+------------------
 APPI Data | APPI Data
 Check Point Data | Check Point Data
 Global | Global
 IPS Data | IPS Data
 LOG Data | LOG Data
 SMC User | SMC User
 System Data | System Data
(7 rows)

[Expert@DallasSC:0]# cat "${sedScript}"
s/8bf4ac51-2df7-40e1-9bce-bedbedbedbed/APPI Data/g
s/a0bbbc99-adef-4ef8-bb6d-defdefdefdef/Check Point Data/g
s/1e294ce0-367a-11e3-aa6e-0800200c9a66/Global/g
s/a0bbbc99-adef-4ef8-bb6d-cebcebcebceb/IPS Data/g
s/31ab94da-4ab1-5da9-a03d-ddddddaaaaaa/LOG Data/g
s/41e821a0-3720-11e3-aa6e-0800200c9fde/SMC User/g
s/a0eebc99-afed-4ef8-bb6d-fedfedfedfed/System Data/g

[Flo]

Very nice one. It works fine on my lab.

it would be nice, if you also can show the information for the embedded gaia (spark) systems. In my environment with R82 Mgmt it does not show any information about it.

Thank you.

[PhoneBoy]

SMB gateways are a different object type and are probably not reflected in the psql query.

[Danny]

@PhoneBoy : It wasn't the object type, I fixed the PSQL query to handle the different SMB firmware package name correctly. Now they show up nicely as well.

[PhoneBoy]

Makes sense after reading the updated psql query 🙂

[Danny]

@Flo : I updated the PSQL query to handle embedded gaia (spark) appliances as well.

[Flo]

@Danny thank you very much. now it works fine.