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'