SQL example - assets using ciphertext

This example query will return the ciphertexts being used by your assets.

sql
1
SELECT ds.name AS site_name, da.ip_address, da.host_name, dos.asset_type, dasc.port, split_part(dasc.name, '.', 1) protocol_version, unnest(string_to_array(dasc.value, ',')) cipher_suite 
2
3
FROM dim_asset da 
4
5
JOIN dim_operating_system dos USING (operating_system_id) 
6
7
JOIN dim_host_type dht USING (host_type_id) 
8
9
JOIN dim_asset_service_configuration dasc USING (asset_id) 
10
11
JOIN dim_site_asset dsa USING (asset_id) 
12
13
JOIN dim_site ds USING (site_id) 
14
15
WHERE dasc.name ILIKE 'sslv2.ciphers' OR dasc.name ILIKE 'sslv3.ciphers' OR dasc.name ILIKE 'tlsv1_0.ciphers' OR dasc.name ILIKE 'tlsv1_1.ciphers' OR dasc.name ILIKE 'tlsv1_2.ciphers'