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
@jeff_ketter Since dim_asset_service is based on the most recent scan:
Description: Dimension that provides the services detected on an asset during the most recent scan of the asset. If an asset had no services enumerated during the scan, there will be no records in this dimension.
It might be necessary to run an export on an interval and then doing the comparison between the exports with a little scripting. It will certainly be the best approach for finding port changes between time periods.
@jeff_ketter You might find the fact_asset_scan_service table useful. It’s a large table, but it will contain those metrics over time, rather than the latest. To quickly get the most recent scan for an asset, you could use the lastscan(asset_id) function which just takes an asset ID as input and the previousscan(asset_id) function to get the scan before that one. You could add them in a subquery to get them all at once for each of your assets in scope:
SELECT asset_id, previousscan(asset_id) AS prev_scan_id, lastscan(asset_id) AS last_scan_id
FROM fact_asset
GROUP BY asset_id, prev_scan_id, last_scan_id