SQL example - credential status

This group of example queries return information on whether the credentials provided for different services were correct and what level of privileges they had on each asset.

sql
1
SELECT asset_id, scan_id, date, name, credential_status_description
2
FROM fact_asset_scan_service
3
JOIN dim_credential_status USING(credential_status_id)
4
JOIN dim_service USING(service_id)

This query will return:

  • the asset id
  • scan id
  • date of the scan
  • name of service (e.g. SNMP)
  • credential status in a human readable form (e.g. Login as local admin)

Similarly, to create the report on credential status in the latest scan performed on an asset, use the following query:

sql
1
SELECT asset_id, name, credential_status_description
2
FROM dim_asset_service_credential
3
JOIN dim_credential_status USING(credential_status_id)
4
JOIN dim_service USING(service_id)

This query will return:

  • the asset id
  • name of service (e.g. SNMP)
  • credential status in a human readable form (e.g. Login as local admin)

For a snapshot report of credential status across services in the latest scan on the asset, the following query can be executed:

sql
1
SELECT asset_id, aggregated_credential_status_description
2
FROM fact_asset
3
JOIN dim_aggregated_credential_status USING(aggregated_credential_status_id)

It will output:

  • asset id
  • aggregated credential status (e.g Credentials partially successful)

For the more detailed report containing the data from the historical scans as well, the following query can be used:

sql
1
SELECT asset_id, scan_id, scan_finished, aggregated_credential_status_description
2
FROM fact_asset_scan
3
JOIN dim_aggregated_credential_status USING(aggregated_credential_status_id)

This query will return:

  • the asset id
  • scan id
  • the time at which the asset completed scanning
  • aggregated credential status