SQL example - vulnerability coverage
These example queries can give you an idea on how much vulnerability coverage the Security Console has and which vulnerabilities were recently added.
All Vulnerabilities
Vulnerabilities that the Security Console has defined are dimensional, and can be located within the dim_vulnerability dimension. This dimension exposes the common attributes of the vulnerabilities that the Security Console has coverage for. To get a listing of all vulnerabilities, you can perform a simple query against this dimension:
sql
1SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector,2severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score,3round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits4FROM dim_vulnerability5ORDER BY title ASC
The query above projects several of the columns available in the dimension, formatting and aliasing to make the output clear. The proofAsText function strips the HTML markup in the description column into a plain text format. The built-in round function is used to round the risk_score and cvss_score columns to two digits of precision. A simple ORDER BY expression sorts the results alphabetically by title in the ascending order.
Recently Published Vulnerabilities
To refine what vulnerabilities are returned, a WHERE clause can be added that only returns the vulnerabilities published in the last two weeks. To set the range for the date comparison, the interval data type is used. Refer to PostgreSQL Date/Time Types for more information on using custom date intervals.
sql
1SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector,2severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score,3round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits4FROM dim_vulnerability5WHERE now() - date_published < INTERVAL '2 weeks'6ORDER BY title ASC
Recently Published Microsoft Vulnerabilities
This query can be further customized to include filtering on vulnerability type. The dim_vulnerability_category dimension provides the associations between a vulnerability and its categories. This query returns the recently published vulnerabilities that are defined within Microsoft-related categories:
sql
1SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector,2severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score,3round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits4FROM dim_vulnerability5WHERE now() - date_published < INTERVAL '2 weeks' AND vulnerability_id IN (6SELECT DISTINCT vulnerability_id7FROM dim_vulnerability_category8WHERE lower(category_name) LIKE '%microsoft%'9)10ORDER BY title ASC
As the dim_vulnerability_category dimension contains all category associations, the sub-select returns only the distinct identifiers of the vulnerabilities which match (case insensitive) the term "microsoft". The sub-select technique is ideal to avoid returning duplicate rows that might have been returned if dim_vulnerability dimension was naturally joined with the dim_vulnerability_category dimension.
Recently Published Severe Microsoft Vulnerabilities
The search for vulnerabilities can be narrowed down even further by using the Security Console severity description:
sql
1SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector,2severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score,3round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits4FROM dim_vulnerability5WHERE now() - date_published < INTERVAL '2 weeks' AND severity = 'Critical' AND vulnerability_id IN (6SELECT DISTINCT vulnerability_id7FROM dim_vulnerability_category8WHERE lower(category_name) LIKE '%microsoft%'9)10ORDER BY title ASC
Recently Published Microsoft Vulnerabilities With CVEs
The relationship between a vulnerability and CVEs are stored as "references" in the dim_vulnerability_reference dimension. This is a one-to-many dimension, meaning that if a vulnerability has multiple references, it will have multiple records in this table. As a result, there are several ways in SQL to extract this data: (1) sub-select (2) JOIN with a GROUP BY expression, or (3) WITH expressions. The approach you choose is a design preference. In either situation, we will be collapsing multiple references into a single column, which means that we have to perform grouping aggregation either way. The aggregate function array_agg and the array_to_string function are invaluable to formatting the output when performing this denormalization. Let's see all of these options:
Using a sub-select
sql
1SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector,2severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score,3round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits,4(SELECT array_to_string(array_agg(reference), ',') FROM dim_vulnerability_reference WHERE source = 'CVE' AND vulnerability_id = dv.vulnerability_id) AS cves5FROM dim_vulnerability dv6WHERE now() - date_published < INTERVAL '2 weeks' AND vulnerability_id IN (7SELECT DISTINCT vulnerability_id8FROM dim_vulnerability_category9WHERE lower(category_name) LIKE '%microsoft%'10)11ORDER BY title ASC12
In this approach, a sub-select is used to find all references from the dim_vulnerability_reference dimension which have a matching source name of "CVE". To compare the vulnerability_id from the dim_vulnerability dimension, an alias called "dv" is to used to avoid ambiguity. As there are multiple potential CVE references per vulnerability, they need to be flattened into a single output string. This is accomplished by using the array_agg aggregate function. This takes individual values and puts them into an array. However, the array will be formatted with square brackets in the output, so that isn't very clean in the CSV format. To make the output even more presentable, that array can be parsed into a string with custom delimiters using the array_to_string function. In this case, the fields in the array are flattened into a string using the comma as the separator.
Using a JOIN
sql
1SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector,2severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score,3round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits, vcves.cves4FROM dim_vulnerability dv5JOIN (6SELECT vulnerability_id, array_to_string(array_agg(reference), ',') AS cves7FROM dim_vulnerability_reference8WHERE source = 'CVE'9GROUP BY vulnerability_id10) AS vcves USING (vulnerability_id)11WHERE now() - date_published < INTERVAL '2 weeks' AND vulnerability_id IN (12SELECT DISTINCT vulnerability_id13FROM dim_vulnerability_category14WHERE lower(category_name) LIKE '%microsoft%'15)16ORDER BY title ASC
In this approach a nested JOIN performs a search to find all CVEs for each vulnerability (with the same aggregation technique described above). This resulting data is then naturally joined to pull out the CVEs.
Using a WITH expression
sql
1WITH2vulnerability_cves AS (3SELECT vulnerability_id, array_to_string(array_agg(reference), ',') AS cves4FROM dim_vulnerability_reference5WHERE source = 'CVE'6GROUP BY vulnerability_id7)8SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector,9severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score,10round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits, vcves.cves11FROM dim_vulnerability dv12JOIN vulnerability_cves vcves USING (vulnerability_id)13WHERE now() - date_published < INTERVAL '2 weeks' AND vulnerability_id IN (14SELECT DISTINCT vulnerability_id15FROM dim_vulnerability_category16WHERE lower(category_name) LIKE '%microsoft%'17)18ORDER BY title ASC
In this approach we leverage the PostgresSQL WITH expressions, or more formally known as Common Table Expressions. This technique is simple and is expanded upon in the SQL Query Export examples article. The vulnerability_cves expression first performs a search to find all CVEs for each vulnerability (with the same aggregation technique described above). This output is then naturally joined against the dim_vulnerability dimension by vulnerability_id to pull out the cves column. As you can test yourself, the output is the same as the previous two approaches. When compared to the JOIN approach, you'll see some remarkable similarities, but many SQL developers would claim this approach is more readable.
Recently Published Severe Microsoft Vulnerabilities With KBs
Try expanding the example above for CVEs to pull out Microsoft KB references yourself.
As you can see, customizing the query to retrieve the vulnerabilities of interest is very simple and can be customized to your needs. Try looking at other dimensions that start with the name "dim_vulnerability_" to see what other metadata is available for filtering.