PSQL - Show top 20 hit count per gateway

[Danny]

🏆 PSQL wizardry!
👉 Directly queries the management database

PSQL query to show the top 20 hit count per gateway on a Check Point Security Management (SmartCenter).
In expert mode run:

psql_client monitoring postgres -c "WITH ranked AS (SELECT netobj_name AS gateway, rule_uid, policy_type, SUM(hits) AS total_hits, MAX(end_date) AS last_seen, ROW_NUMBER() OVER (PARTITION BY netobj_name ORDER BY SUM(hits) DESC) AS rn FROM public.hitcount WHERE end_date >= now() - interval '30 days' GROUP BY netobj_name, rule_uid, policy_type) SELECT gateway, rule_uid, policy_type, total_hits, last_seen FROM ranked WHERE rn <= 20 ORDER BY gateway, total_hits DESC;"

[the_rock]

Excellent!