Looking for a SQL Query that will give me Host name, port name, site and port and All products( services). Can anyone assist?
Here is one that you can run on the NeXpose Console from report section in the SQL report option
This groups the Ports and Services together but you can prob separate to fit your needs
WITH
open_ports_services AS (
SELECT
asset_id,
array_to_string(array_agg(dp.name || ‘/’ || das.port || ’ (’ || dsrv.name || ‘)’ ORDER BY das.port), ‘,’) AS ports_services
FROM dim_asset_service das
JOIN dim_protocol dp USING (protocol_id)
JOIN dim_service dsrv ON das.service_id = dsrv.service_id
GROUP BY asset_id
)
SELECT
da.ip_address AS “IP Address”,
da.host_name AS “Asset Host Name”,
dos.description AS “OS Details”,
ops.ports_services AS “Open Ports and Services”,
array_to_string(array_agg(ds.name), ‘,’) AS “Site Name(s)”
FROM
fact_asset_discovery fad
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN open_ports_services ops USING (asset_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds ON dsa.site_id = ds.site_id
GROUP BY
da.ip_address,
da.host_name,
dos.description,
ops.ports_services
2 Likes