I’m looking for a way compare open port changes on a monthly basis. I work in higher ed, and while our IT is generally centralized, we do have some academic areas administrating their own servers and need to be able to monitor for problematic changes.
I found the relevant fields, but writing a query to compare the results to a previous scan or date is giving me fits. I THINK my issue is that there’s no scan_id fields in any of the tables that also include serice/protocol fields, and bridging multiple tables is beyond me. I’m still new to SQL, so any help would be appreciated! Below is the basic query I started with, which identifies open ports. Thanks!
SELECT da.ip_address, da.host_name, dos.name AS OS, dos.version AS os_version, das.port, dp.name AS protocol, ds.name AS service, dsf.name AS service_name, dsf.version AS service_version
FROM dim_asset_service das
JOIN dim_service ds USING (service_id)
JOIN dim_protocol dp USING (protocol_id)
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_service_fingerprint dsf USING (service_fingerprint_id)
ORDER BY da.ip_address, das.port