Hi there!
I’m trying to retrieve all assets that are running tcp 1433 & 1434 (MS SQL). I’m having trouble to create a SQL query for this request. Is there someone who can help me with this?
Kind regards,
Julian
Try this… go into your NeXpose Reporting and use the custom SQL reporting, then choose which site(s) you want to run it against.
WITH
open_ports AS (
SELECT asset_id, array_to_string(array_agg(dp.name || ‘/’ || port ORDER BY port), ‘,’) AS ports
FROM dim_asset_service
JOIN dim_protocol dp USING (protocol_id)
WHERE port IN (1433, 1434)
GROUP BY asset_id
)
SELECT
da.ip_address AS “IP Address”,
da.host_name AS “Asset Host Name”,
dos.description AS “OS Details”,
open_ports.ports AS “Open Ports”,
array_to_string(array_agg(ds.name), ', ') AS “Site Name(s)”
FROM fact_asset_discovery
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN open_ports USING (asset_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
GROUP BY da.ip_address, da.host_name, dos.description, open_ports.ports;
Hi @bradpjaxx ,
This seems to do the trick! But I would like to run it against specific asset groups, is it possible to do that as well?
I would love to hear from you.
Kind regards,
Julian
