Adding port failed on, and certainty level SQL

I’m having trouble adding certainty level to these partial, full, and unknown authentication the port it’s failing on would be great to but not necessary. On this SQL can anyone help?

WITH owner_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘OWNER’
GROUP BY asset_id
),

custom_tags AS (  
  SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS custom_tags  
  FROM dim_tag  
     JOIN dim_tag_asset USING (tag_id)  
  WHERE tag_type = 'CUSTOM'  
  GROUP BY asset_id  

)

SELECT ot.owner_tags AS “Owner”,
ct.custom_tags AS “Custom Tags”,
dos.asset_type AS “Asset Type”,
dos.description AS “Asset Description”,
dos.name AS “OS Name”,
da.host_name AS “Host Name”,
da.ip_address AS “IP Address”,
CASE
WHEN fa.aggregated_credential_status_id = 1 THEN ‘No credentials supplied’
WHEN fa.aggregated_credential_status_id = 2 THEN ‘All credentials failed’
WHEN fa.aggregated_credential_status_id = 3 THEN ‘Credentials partially successful’
WHEN fa.aggregated_credential_status_id = 4 THEN ‘All credentials successful’
WHEN fa.aggregated_credential_status_id = -1 THEN ‘None of the applicable services were discovered in the scan’
END AS “Credential Status”,
to_char (fa.scan_finished, ‘mm/dd/yyyy’) AS “Date Scan Completed”

FROM fact_asset fa
LEFT JOIN dim_asset da USING (asset_id)
LEFT JOIN custom_tags ct USING (asset_id)
LEFT JOIN owner_tags ot USING (asset_id)
LEFT JOIN dim_operating_system dos USING (operating_system_id)

ORDER BY fa.aggregated_credential_status_id DESC, ot.owner_tags DESC

can anyone help on this?

I see the certainty calculation stored in fact_asset_scan_operating_system, so I believe you can join with that table based on the latest scan ID and asset ID from fact_asset. Here’s the join I added and it worked:

JOIN fact_asset_scan_operating_system fasos ON fa.last_scan_id = fasos.scan_id AND fa.asset_id = fasos.asset_id

Then you can add fasos.certainty to your SELECT and it should work.

Holly thank you so much worked like a charm. I thought certainty was embedded in operating_system_id and was racking my brain.

1 Like

Hi Holley i didn’t realize it but it duplicated all of my servers is that giving the history of the certainty as well it doesn’t look like it but couldn’t figure out why it duplicated all the servers i originally had in the report thought it would just add on the certainty column.
I see what happened it does give me the highest it adds on the starting certainty level then ends with the highest some are 1 some are 85 that started from 80.
Can i get a sort and only keep highest?

If you’re looking for the highest one, you could do a max() of the certainty. I did a max() on that and then added a GROUP BY for everything else. I also added the asset_id, just because.

WITH owner_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = 'OWNER'
GROUP BY asset_id
),
custom_tags AS (
  SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS custom_tags
  FROM dim_tag
     JOIN dim_tag_asset USING (tag_id)
  WHERE tag_type = 'CUSTOM'
  GROUP BY asset_id
)
SELECT fa.asset_id,
ot.owner_tags AS "Owner",
ct.custom_tags AS "Custom Tags",
dos.asset_type AS "Asset Type",
dos.description AS "Asset Description",
dos.name AS "OS Name",
da.host_name AS "Host Name",
da.ip_address AS "IP Address",
CASE
WHEN fa.aggregated_credential_status_id = 1 THEN 'No credentials supplied'
WHEN fa.aggregated_credential_status_id = 2 THEN 'All credentials failed'
WHEN fa.aggregated_credential_status_id = 3 THEN 'Credentials partially successful'
WHEN fa.aggregated_credential_status_id = 4 THEN 'All credentials successful'
WHEN fa.aggregated_credential_status_id = -1 THEN 'None of the applicable services were discovered in the scan'
END AS "Credential Status",
max(fasos.certainty) AS "Certainty",
to_char (fa.scan_finished, 'mm/dd/yyyy') AS "Date Scan Completed"
FROM fact_asset fa
LEFT JOIN dim_asset da USING (asset_id)
LEFT JOIN custom_tags ct USING (asset_id)
LEFT JOIN owner_tags ot USING (asset_id)
LEFT JOIN dim_operating_system dos USING (operating_system_id)
JOIN fact_asset_scan_operating_system fasos ON fa.last_scan_id = fasos.scan_id AND fa.asset_id = fasos.asset_id
GROUP BY fa.asset_id, ot.owner_tags, ct.custom_tags, dos.asset_type, dos.description, dos.name, da.host_name, da.ip_address,
         fa.aggregated_credential_status_id, fa.scan_finished
ORDER BY fa.aggregated_credential_status_id DESC, ot.owner_tags DESC
1 Like

Thank you so much it worked like a charm!

1 Like