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