SQL Query Export Example: Credential status

Document created by maria_d Employee on Sep 30, 2014Last modified by maria_d Employee on Oct 1, 2014
Version 2Show Document
  • View in full screen mode

Nexpose users can now use SQL Query Export to create reports containing information whether the credentials provided by them for different services where correct and what level of privileges they provided for each asset.

 

The tables charts provide descriptions of the related tables and their columns:

 

fact_asset_scan_service

Level of Grain: A service detected on an asset in a scan.

Fact Type: transaction

Description: The  fact_asset_scan_service  fact table provides the services detected during a scan of an asset. If an asset had no services enumerated in a scan there will be no records in this fact

Column

Data Type

Nullable

Description

Associated Dimension

asset_id

bigint

No

The identifier of the asset.

dim_asset

scan_id

bigint

No

The identifier of the scan.

dim_scan

date

timestamp without timezone

No

The date and time at which the service was enumerated.

 

service_id

integer

No

The identifier of the service.

dim_service

protocol_id

smallint

No

The identifier of the protocol the service was utilizing.

dim_protocol

port

integer

No

The port the service was running on.

 

service_fingerprint_id

bigint

No

The identifier of the fingerprint of the service describing the configuration of the service.

dim_service_fingerprint

credential_status_id

smallint

No

The result of the user-provided credentials per asset per scan per service. Services for which

credential status is assessed are: SNMP, SSH, Telnet and CIFS.

dim_credential_status

 

fact_asset_scan

Level of Grain: A summary of a completed scan of an asset.

Fact Type: transaction

Description: The fact_asset_scan transaction fact provides summary information of the results of a scan for an asset. A fact record will be present for every asset and scan in which the asset was fully scanned in. Only assets configured within the scope of the report and vulnerabilities filtered within the report will take part in the accumulated totals. If no vulnerabilities checks were performed during the scan, for example as a result of a discovery scan, the vulnerability related counts will be zero.

Column

Data Type

Nullable

Description

Associated Dimension

asset_id

bigint

No

The identifier of the asset.

dim_asset

scan_id

bigint

No

The identifier of the scan.

dim_scan

scan_started

timestamp without timezone

No

The time at which the scan for the asset was started.

 

scan_finished

timestamp without timezone

No

The time at which the scan for the asset completed.

 

vulnerabilities

bigint

No

The number of vulnerabilities found on the asset during the scan.

 

critical_vulnerabilities

bigint

No

The number of critical vulnerabilities found on the asset during the

scan.

 

severe_vulnerabilities

bigint

No

The number of severe vulnerabilities found on the asset during the scan.

 

moderate_vulnerabilities

bigint

No

The number of moderate vulnerabilities found on the asset during the scan.


malware_kitsintegerNo

The number of malware kits associated with vulnerabilities discovered during the scan.

exploitsintegerNo

The number of exploits associated with vulnerabilities discovered during the scan.

vulnerabilities_with_malwareintegerNo

The number of vulnerabilities with a known malware kit discovered during the scan.

vulnerabilities_with_exploitsintegerNo

The number of vulnerabilities with a known exploit discovered during the scan.

vulnerability_instancesbigintNo

The number of vulnerability instances found discovered during the  scan.

riskscoredouble precisionNoThe risk score for the scan.
pci_statustextNoThe PCI compliance status; either Pass or Fail.
aggregated_credential_status_idintegerNo

The status aggregated across all available services for the given dim_aggregated_credential_status

asset in the given scan.

dim_aggregared_credential_status

 

fact_asset

Level of Grain: An asset and its current summary information.

Fact Type: accumulating snapshot

Description: The  fact_asset  fact table provides the most recent information for each asset within the scope of the report. For every asset in scope there will be one record in the fact table.

Column

Data Type

Nullable

Description

Associated Dimension

asset_id

bigint

No

The identifier of the asset.

dim_asset

last_scan_id

bigint

No

The identifier of the scan with the most recent information being summarized

dim_scan

scan_started

timestamp without timezone

No

The time at which the scan for the asset was started.

 

scan_finished

timestamp without timezone

No

The time at which the scan for the asset completed.

 

vulnerabilities

bigint

No

The number of vulnerabilities found on the asset during the scan.

 

critical_vulnerabilities

bigint

No

The number of critical vulnerabilities found on the asset during the

scan.

 

severe_vulnerabilities

bigint

No

The number of severe vulnerabilities found on the asset during the scan.

 

moderate_vulnerabilities

bigint

No

The number of moderate vulnerabilities found on the asset during the scan.


malware_kitsintegerNo

The number of malware kits associated with vulnerabilities discovered during the scan.

exploitsintegerNo

The number of exploits associated with vulnerabilities discovered during the scan.

vulnerabilities_with_malwareintegerNo

The number of vulnerabilities with a known malware kit discovered during the scan.

vulnerabilities_with_exploitsintegerNo

The number of vulnerabilities with a known exploit discovered during the scan.

vulnerability_instancesbigintNo

The number of vulnerability instances found discovered during the  scan.

riskscoredouble precisionNoThe risk score for the scan.
pci_statustextNoThe PCI compliance status; either Pass or Fail.
aggregated_credential_status_idintegerNo

The status aggregated across all available services for the given dim_aggregated_credential_status

asset in the given scan.

dim_aggregared_credential_status

 

dim_asset_service_credential

Description: Dimension that presents the most recent credential statuses asserted for services on an asset in the latest scan.

Type: slowly changing

Column

Data Type

Nullable

Description

Associated Dimension

asset_id

bigint

No

The identifier of the asset.

dim_asset

service_id

integer

No

The identifier of the service.

dim_service

credential_status_idsmallintNoThe identifier of the credential status for the service credential.dim_credential_status

 

dim_credential_status

Description: Dimension for the scan service credential status in human-readable form.

Type: normal

Column

Data Type

Nullable

Description

Associated Dimension

credential_status_id

smallint

No

The credential status ID associated with the fact_asset_scan_service.

 

credential_status_status

text

No

The human-readable description of the credential status.

 

 

dim_aggregated_credential_status

Description: Dimension the containing the status aggregated across all available services for the given asset in the given scan.

Type: normal

Column

Data Type

Nullable

Description

Associated Dimension

aggregated_credential_status_id

smallint

No

The credential status ID associated with the fact_asset_scan and fact_asset.

 

aggregated_credential_status_status

text

No

The human-readable description of the credential status.

 

 

The credential status feature can be used to create a CSV report containing information whether the credentials provided by them for different services where correct and what level of privileges they provided for each asset. To retrieve this information use the following query in the SQL Query Export feature:

SELECT asset_id, scan_id, date, name, credential_status_description  
FROM fact_asset_scan_service  
JOIN dim_credential_status USING(credential_status_id)
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:

SELECT asset_id, name, credential_status_description 
FROM dim_asset_service_credential
JOIN dim_credential_status USING(credential_status_id)
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:

SELECT asset_id, aggregated_credential_status_description 
FROM fact_asset  
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:

SELECT asset_id, scan_id, scan_finished, aggregated_credential_status_description 
FROM fact_asset_scan  
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
4 people found this helpful

Attachments

    Outcomes