SQL Query for failed assets in the last three consecutive scans

InsightVM SQL Queries
,
1

Has anyone tried this and if yes, could you please share which SQL query you have used. I am trying with below query but not getting the desired results.

Sample query:

SELECT
da.asset_id,
da.ip_address,
da.host_name,
da.sites,
dos.description AS operating_system,
dacs.aggregated_credential_status_description,
da.ip_address,
ds.name AS Service,
dasc.port,
dcs.credential_status_description,
site.name AS site_name,
dcs.credential_status_description
FROM
dim_asset da
JOIN dim_operating_system dos ON
da.operating_system_id = dos.operating_system_id
JOIN fact_asset fa ON da.asset_id = fa.asset_id
JOIN dim_aggregated_credential_status dacs ON fa.aggregated_credential_status_id = dacs.aggregated_credential_status_id
JOIN dim_asset_service_credential dasc ON da.asset_id = dasc.asset_id
JOIN dim_credential_status dcs ON dasc.credential_status_id = dcs.credential_status_id
JOIN dim_service ds ON dasc.service_id = ds.service_id
JOIN dim_site_asset dsa ON da.asset_id = dsa.asset_id
JOIN dim_site site ON dsa.site_id = site.site_id
WHERE
dos.description ILIKE ‘%windows%server%’
AND (
dos.description ILIKE ‘%2012%’ OR
dos.description ILIKE ‘%2016%’ OR
dos.description ILIKE ‘%2019%’ OR
dos.description ILIKE ‘%2022%’
)
– Filter for credential failures
AND (
dacs.aggregated_credential_status_id IN (‘1’, ‘2’) – 1=No credentials, 2=Credential failure
OR dcs.credential_status_description ILIKE ‘%fail%’
OR dcs.credential_status_description ILIKE ‘%denied%’
OR dcs.credential_status_description ILIKE ‘%invalid%’
)
– Filter for SSH service authentication failures (primary authentication method for UNIX)
AND ds.name ILIKE ‘%remote desktop%’
– Only include assets that have been scanned
AND da.last_assessed_for_vulnerabilities IS NOT NULL

2

are you refering to the last scan and their credential status? or Reviewing the last 3 scans if there is a success your good.

can you give us a problem statement and describe the end goal?

3

In our current process of conducting weekly schedule scans, assets occasionally fail due to connectivity issues or other unforeseen problems. While isolated failures are acceptable, there is a recurring issue where certain assets consistently fail for three consecutive weeks. This pattern indicates potential underlying problems that need to be addressed. Our challenge is to efficiently identify these persistently failing assets and implement corrective actions to ensure their reliability and optimal performance in future scans.

4

so you are referring to INCOMPLETE Assets? Like Timeoutes on Servers Running over X time or iDRAC’s kicking you out and going incomplete etc…

5

Yes possible or could be like: dacs.aggregated_credential_status_id IN (‘1’, ‘2’) – 1=No credentials, 2=Credential failure
OR dcs.credential_status_description ILIKE ‘%fail%’
OR dcs.credential_status_description ILIKE ‘%denied%’
OR dcs.credential_status_description ILIKE ‘%invalid%’

6

Thats 2 different concepts, an incomplete asset wont show up.. period.. You would need to manually review, Export your incomplete assets and rescan… you may, and i say may be able to use the v3 API under Site Scans to return those incomplete assets and automate pulling the incompletes and then rescanning. ( Havent tried that )

The manual process would be to export your incompletes each week to monitor and evaluate, if its a connectivity issue, a resource issue, credential permissions issue etc.. For those pesky assets Agents and Scan Assistants may be the better option for consistency.

Also:
A Credentialed scan is just that, you will get:
Success
Partial Success
Credential Failed
Unknown
No Credential Supplied

but that scan has to be run, completed, and integrate in order to capture these records..

hope that all makes sense, may not be the answer your looking for… however if you need a Credential Query, i have plenty of those.

7

So i can confirm that scanning is completed and if i manually check i can see failed assets there, but instead of checking this manually, wanted to check if there could be a SQL query which can do this and check asset under same site for last 3 weeks and if same asset if failed, we should have the report.

8

so you mean " Credential Failed "

9

Sorry for replying late. Yes “Credential failed” only

10

you would need to set this up in the SQL reporting, run it weekly on the site or DAG in question etc..

This checks the latest known credential scan result for each asset
It filters only where the aggregated credential status is Credential Failure

Try this…

SELECT 
  ds.name AS "Site Name",
  da.ip_address AS "IP Address",
  da.host_name AS "Hostname",
  dos.description AS "Operating System",
  dacs.aggregated_credential_status_description AS "Credential Status"
FROM fact_asset fa
JOIN dim_aggregated_credential_status dacs
  ON fa.aggregated_credential_status_id = dacs.aggregated_credential_status_id
JOIN dim_asset da 
  ON fa.asset_id = da.asset_id
JOIN dim_operating_system dos 
  ON da.operating_system_id = dos.operating_system_id
JOIN dim_site_asset dsa 
  ON da.asset_id = dsa.asset_id
JOIN dim_site ds 
  ON dsa.site_id = ds.site_id
WHERE dacs.aggregated_credential_status_description = 'Credential Failure'
ORDER BY ds.name, da.ip_address;
11

I tried running this on the site but got the blank report. Tried running on DAG as well but that also came blank, even ran the scan on site and last weekend scan data that is also came blank