Adding port failed on, and certainty level SQL

InsightVM SQL Queries
#1

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

#2

can anyone help on this?

#3

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.

#4

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
#5

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?

#6

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
#7

Thank you so much it worked like a charm!

1 Like
#8

If i only wanted this status to show up from this sql how do i keep the ones that are successful from showing up with blank spaces and completely take them out. I took out the 3 and 4 from sql but they still show up…
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 = -1 THEN ‘None of the applicable services were discovered in the scan’

#9

@holly_wilsey

#10

To clarify, do you mean that you only want rows with a certain status to appear in your results? And you want to exclude the three you listed here?

1 Like
#11

I’m sorry i figured it out it was actually the opposite of what your questions leaving out the three but excluding the successful to give a non-authenticated report.

1 Like