Skip to content

Reporting

jakepaulus edited this page Jul 24, 2015 · 3 revisions

Collate:Network doesn't have any built-in reporting yet. Below are some quick and dirty SQL queries I've written to get useful data out of deployments. These might be useful in future efforts to build in some reports.

summary of each object type

SELECT
(SELECT count(*) from blocks) as blocks, 
(SELECT count(*) from subnets) as subnets,
(SELECT count(*) from statics) as statics,
(SELECT count(*) from users) as users;

top 10 emptiest ip blocks

select blocks.name as block_name, count(subnets.block_id) as subnets_count from blocks 
 left join subnets on (subnets.block_id = blocks.id) 
 where blocks.type='ipv4' 
 group by blocks.id 
 ORDER BY subnets_count ASC LIMIT 10; 

top 10 subnets with most stale addresses

select INET_NTOA(CAST(subnets.start_ip & 0xFFFFFFFF as UNSIGNED)) as subnet, 
	count(statics.subnet_id) as stale_ip_count from subnets
 left join statics on (statics.subnet_id = subnets.id)
 where statics.failed_scans > 0
 group by subnets.id
 ORDER BY stale_ip_count DESC LIMIT 10;

top 10 subnets with most stale addresses as a percentage of the subnet size

-- where subnet size is larger than 100 usable addresses
select INET_NTOA(CAST(subnets.start_ip & 0xFFFFFFFF as UNSIGNED)) as subnet,
 CAST(subnets.end_ip & 0xFFFFFFFF as UNSIGNED) - CAST(subnets.start_ip & 0xFFFFFFFF as UNSIGNED) - 1 as usable_ips, 
 count(statics.subnet_id) as stale_ip_count,
 count(statics.subnet_id) / (CAST(subnets.end_ip & 0xFFFFFFFF as UNSIGNED) - 
	CAST(subnets.start_ip & 0xFFFFFFFF as UNSIGNED)) * 100 as stale_ip_percent
 from subnets
 left join statics on (statics.subnet_id = subnets.id)
 where statics.failed_scans > 0 AND
 CAST(subnets.end_ip & 0xFFFFFFFF as UNSIGNED) - CAST(subnets.start_ip & 0xFFFFFFFF as UNSIGNED) > 100
 group by subnets.id
 ORDER BY stale_ip_percent DESC LIMIT 10;

top 10 subnets with most stale addresses as a percentage of the subnet size

-- where subnet size is larger than 100 usable addresses
-- (uses ACLs)
select INET_NTOA(CAST(subnets.start_ip & 0xFFFFFFFF as UNSIGNED)) as subnet,
 CAST(subnets.end_ip & 0xFFFFFFFF as UNSIGNED) - CAST(subnets.start_ip & 0xFFFFFFFF as UNSIGNED) - 1 - 
 (SELECT SUM(CAST(end_ip & 0xFFFFFFFF AS UNSIGNED) - CAST(start_ip & 0xFFFFFFFF AS UNSIGNED)) FROM acl WHERE 
	subnet_id=subnets.id) as usable_ips, 
 count(statics.subnet_id) as stale_ip_count,
 count(statics.subnet_id) / (CAST(subnets.end_ip & 0xFFFFFFFF as UNSIGNED) - 
	CAST(subnets.start_ip & 0xFFFFFFFF as UNSIGNED) + 1 - 
 (SELECT SUM(CAST(end_ip & 0xFFFFFFFF AS UNSIGNED) - CAST(start_ip & 0xFFFFFFFF AS UNSIGNED)) FROM acl WHERE 
	subnet_id=subnets.id)) * 100 as stale_ip_percent
 from subnets
 left join statics on (statics.subnet_id = subnets.id)
 where statics.failed_scans > 0 AND
 CAST(subnets.end_ip & 0xFFFFFFFF as UNSIGNED) - CAST(subnets.start_ip & 0xFFFFFFFF as UNSIGNED) > 100
 group by subnets.id
 ORDER BY stale_ip_percent DESC LIMIT 10;

How many statics might discovery scan?

SELECT
(SELECT SUM(CAST(end_ip & 0xFFFFFFFF AS UNSIGNED)-CAST(start_ip & 0xFFFFFFFF AS UNSIGNED)) FROM subnets) AS subnet_space,
(SELECT SUM(CAST(end_ip & 0xFFFFFFFF AS UNSIGNED)-CAST(start_ip & 0xFFFFFFFF AS UNSIGNED)) FROM acl) AS acl_space,
(SELECT count(*) from statics) as static_count,
(SELECT subnet_space-acl_space-static_count) AS total_unused_statics;