Knowledge Base

Ask A Question

Questions

1

SQL Query Help - Combine all affected IP's into one field

In looking for ways to create a suitable csv for ingestion into a ticketing system, I've encountered a challenge in the SQL statement I'm trying to use. Disclaimer: I'm not a DBA, so this may be incredibly easy. Using the following simple query to build off of: ``` SELECT dv.title, da.ip_address, da.host_name, dp.name, CASE WHEN favi.port = -1 THEN NULL ELSE favi.port END, dsvc.name FROM fact_asset_vulnerability_instance favi JOIN dim_vulnerability dv USING (vulnerability_id) JOIN dim_asset da USING (asset_id) JOIN dim_protocol dp USING (protocol_id) JOIN dim_service dsvc USING (service_id) WHERE dv.nexpose_id = 'tlsv1_0-enabled' ``` Which yields an output such as: | title | ip_address | host_name | name | port| name | | ------ | ----------------- | ------------------ | --------- | ------ | --------- | | TLS Server Supports TLS version 1.0 | 10.0.0.1 | TCP | 743 | HTTPS | | TLS Server Supports TLS version 1.0 | 10.0.0.2 | TCP | 744 | HTTP | | TLS Server Supports TLS version 1.0 | 10.0.0.3 | TCP | 745 | HTTP | | TLS Server Supports TLS version 1.0 | 10.0.0.4 | TCP | 743 | HTTPS | I'm looking to combine all the affected ip's/hosts/protocol/port/name into a single field, such as: | title | affects | | ------ | ------------------------------------------------------------------------------------- | | TLS Server Supports TLS version 1.0 | 10.0.0.1 TCP 743 HTTPS, 10.0.0.2 TCP 744 HTTP, 10.0.0.3 TCP 745 HTTP, 10.0.0.4 TCP 743 HTTPS | Any help would be appreciated.

Posted by Chris Maier 24 days ago