Useful SQL queries to Check Point cpview database

cpview_queries.sql

-------- Useful cpview SQL queries --------

-------- numbers of concurrent connections from an alternative table
SELECT
	datetime(timestamp, 'unixepoch', 'localtime') AS datetime,
    conns
FROM fw_network_stats_concurrent_conns
WHERE component_name = 'Total'
;

-------- maximum number of concurrent connections with time
SELECT
	datetime(timestamp, 'unixepoch', 'localtime') AS datetime,
    conns
FROM fw_network_stats_concurrent_conns
WHERE
	component_name = 'Total'
	AND conns = (
		SELECT MAX(conns)
		FROM fw_network_stats_concurrent_conns
		WHERE component_name = 'Total')
;

--------- numbers of concurrent connections above a threshold
SELECT
	datetime(timestamp, 'unixepoch', 'localtime') AS datetime,
	timestamp,
	conns
FROM fw_network_stats_concurrent_conns
WHERE
	component_name = 'Total'
	AND conns > 5000
;

--------- list of interfaces (good to identify the VS)
SELECT
	DISTINCT cpview_ref_table.val
FROM UM_STAT_UM_HW_UM_IF_TABLE
LEFT JOIN cpview_ref_table
	ON UM_STAT_UM_HW_UM_IF_TABLE.name = cpview_ref_table.seq
;

--------- RX drops per second (convert cumulative counters to count per time step)
WITH UM_STAT_UM_HW_diff AS (
	SELECT
		timestamp,
		timestamp - LAG(timestamp) OVER diff_window AS time_step,
		total_rx_drops - LAG(total_rx_drops) OVER diff_window AS rx_drops
	FROM UM_STAT_UM_HW
	WINDOW diff_window AS (ORDER BY timestamp)
)
SELECT
	datetime(timestamp, 'unixepoch', 'localtime') AS datetime,
	rx_drops / CAST(time_step AS FLOAT) AS rx_drops_per_sec
FROM UM_STAT_UM_HW_diff
WHERE rx_drops_per_sec > 10
;

--- other cumulative drop counters:
--- drop_reason_corexl_queue, drop_reason_capacity, drop_reason_sxl, ...
--- FROM fw_network_stats

--------- Show nonzero abnormal drop counters
--- FIXME: convert cumulative to differential
SELECT
	datetime(timestamp, 'unixepoch', 'localtime') AS datetime,
	*
FROM fw_network_stats
WHERE
	drop_reason_general + drop_reason_corexl_queue + drop_reason_capacity +	drop_reason_sxl > 0
;