PSQL - Show policy packages

[Danny]

🏆 PSQL wizardry!
👉 Directly queries the management database

PSQL query to show all policy packages on a Check Point Security Management (SmartCenter).
In expert mode run:

psql_client cpm postgres -c "WITH latest_pkg AS (SELECT DISTINCT ON (name) * FROM policypackage_data WHERE NOT deleted AND dlesession = 0 AND name <> 'VR_Default' ORDER BY name, lastmodifytime DESC) SELECT pkg.name AS policy_package, apc.name AS access_policy, tp.name AS threat_policy, stp.name AS simplified_threat_policy, CASE WHEN pkg.usethreatpolicy THEN 'true' ELSE 'false' END AS use_threat_policy, STRING_AGG(DISTINCT tls.name, ', ') AS tls_inspection, COALESCE(nat.name, '*') AS nat_policy, COALESCE(NULLIF(STRING_AGG(DISTINCT gw.name, ', '), ''), '*') AS install_targets, TO_CHAR(pkg.creationtime, 'YYYY-MM-DD HH24:MI:SS') AS creation_time, TO_CHAR(pkg.lastmodifytime, 'YYYY-MM-DD HH24:MI:SS') AS last_modified FROM latest_pkg pkg LEFT JOIN accesspolicycontainer_data apc ON pkg.accesspolicycontainer = apc.objid LEFT JOIN threatpolicy_data tp ON pkg.threatpolicy = tp.objid LEFT JOIN simplifiedthreatpolicy_data stp ON pkg.simplifiedthreatpolicy =
 stp.objid LEFT JOIN tlsinspectionpolicy_data tls ON pkg.tlsinspectionpolicy = tls.objid LEFT JOIN natpolicy_data nat ON apc.natpolicy = nat.objid LEFT JOIN policy_specific_gateways_data sg ON pkg.objid = sg.owner_id AND NOT sg.deleted AND sg.dlesession = 0 LEFT JOIN dleobjectderef_data gw ON sg.specificgateways = gw.objid AND gw.cpmitable = 'network_objects' AND gw.cpmitype = 'gateway' GROUP BY pkg.name, apc.name, tp.name, stp.name, pkg.usethreatpolicy, nat.name, pkg.creationtime, pkg.lastmodifytime ORDER BY pkg.name;"

[the_rock]

Brilliant, as always!