Hello, I manage Casper at a global enterprise and I get asked every now and again how many Macs are on each corporate subnet. I can generate a report in the JSS to show which building and department Macs are on, but Casper can't seem to report on network segments. The way the environment is set up means I don't have a one-to-one ratio between buildings (~300), departments (~150), or network segments (~3500).
To meet the request, I developed a mySQL query I can run against the casper database. It'll spit out a csv with all network segments in Casper with departments, buildings, servers populated in place of the server ID. It'll also display client counts for each row (network segment). You can feed this csv into excel to better display the data. In the end this is essentially a on-demand Casper network map.
I'd love to have this code critiqued by a DBA, as I know just enough mySQL to be dangerous.... it takes about 4 minutes to execute against my dev server.
Hope this helps!
(select 'Subnet Name','Starting Address','Ending Address','Distribution Point','Software Update Server','Building Name','Department Name','Client Count')
union
(select n.display_name, n.starting_address, n.ending_address, dps.server_address, s.server_address, b.building_name, d.department_name, (select count(*) from computers where inet_aton(last_ip) between inet_aton(starting_address) AND inet_aton(ending_address))
from network_segments as n left join buildings as b
on n.building_id = b.building_id
left join departments as d
on n.department_id = d.department_id
left join distribution_points as dps
on n.distribution_point_id = dps.distribution_point_id
left join software_update_servers as s
on n.software_update_server_id = s.software_update_server_id
INTO OUTFILE 'c:/tmp/network_subnet_report.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
');
in addition, Here are two commands I use to check for overlapping network segments.
Test to see if any ENDING addresses overlap:
select count(*), display_name, starting_address, ending_address from jamfsoftware.network_segments
where inet_aton(ending_address) between inet_aton(starting_address) AND inet_aton(ending_address) group by inet_aton(ending_address) having count(*) >1;
Test to see if any STARTING addresses overlap:
select count(*), display_name, starting_address, ending_address from jamfsoftware.network_segments
where inet_aton(starting_address) between inet_aton(starting_address) AND inet_aton(ending_address) group by inet_aton(starting_address) having count(*) >1;
