SQL example - view open ports

This example query outputs a port per row with additional information for each port.

sql
1
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
2
FROM dim_asset_service das
3
   JOIN dim_service ds USING (service_id)
4
   JOIN dim_protocol dp USING (protocol_id)
5
   JOIN dim_asset da USING (asset_id)
6
   JOIN dim_operating_system dos USING (operating_system_id)
7
   JOIN dim_service_fingerprint dsf USING (service_fingerprint_id)
8
ORDER BY da.ip_address, das.port