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
1SELECT asset_id, scan_id, date, name, credential_status_description2FROM fact_asset_scan_service3JOIN dim_credential_status USING(credential_status_id)4JOIN 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
1SELECT asset_id, name, credential_status_description2FROM dim_asset_service_credential3JOIN dim_credential_status USING(credential_status_id)4JOIN 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
1SELECT asset_id, aggregated_credential_status_description2FROM fact_asset3JOIN 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
1SELECT asset_id, scan_id, scan_finished, aggregated_credential_status_description2FROM fact_asset_scan3JOIN 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
Did this page help you?