SQL示例 - 漏洞覆盖据span title="Copy link to clipboard">
这些示例查询可以让您了解Security Console有多少漏洞覆盖率,以及最近添加了哪些漏洞。据/p>
所有漏洞据span title="Copy link to clipboard">
安全控制台定义的漏洞是维度的,可以位于dim_vulnerability维度中。此维度公开了安全控制台覆盖的漏洞的公共属性。要获得所有漏洞的列表,可以对这个维度执行一个简单的查询:据/p>
sql据div class="code-copy" title="Copy to clipboard">
1据/div>
选择据/span>nexpose_id据/span>那据/span>标题据/span>那据/span>校对据/span>(据/span>描述据/span>)据/span>作为据/span>描述据/span>那据/span>date_published.据/span>那据/span>cvss_vector据/span>那据/span>
2据/div>
severity_score.据/span>那据/span>严重程度据/span>那据/span>pci_severity_score.据/span>那据/span>pci_status.据/span>那据/span>轮据/span>(据/span>riskscore::据/span>数字据/span>那据/span>0.据/span>)据/span>作为据/span>risk_score据/span>那据/span>
3.据/div>
轮据/span>(据/span>cvss_score ::据/span>数字据/span>那据/span>2据/span>)据/span>作为据/span>cvss_score据/span>那据/span>漏洞利用据/span>那据/span>malware_kits据/span>
4.据/div>
从据/span>dim_vulnerability.据/span>
5.据/div>
订单据/span>经过据/span>标题据/span>ASC据/span>
上面的查询投射维度、格式和别名中可用的几个列,以使输出清晰。proofAsText函数将描述栏中的HTML标记剥离为纯文本格式。内置的round函数用于将risk_score和cvss_score列四舍五入到两位精度。一个简单的ORDER BY表达式按标题的字母顺序升序对结果进行排序。据/p>
最近发表的漏洞据span title="Copy link to clipboard">
要细化返回哪些漏洞,可以添加WHERE子句,该子句只返回最近两周发布的漏洞。要设置日期比较的范围,可以使用间隔数据类型。指据a href="https://www.postgresql.org/docs/9.0/static/datatype-datetime.html">PostgreSQL日期/时间类型据/a>有关使用自定义日期间隔的更多信息。据/p>
sql据div class="code-copy" title="Copy to clipboard">
1据/div>
选择据/span>nexpose_id据/span>那据/span>标题据/span>那据/span>校对据/span>(据/span>描述据/span>)据/span>作为据/span>描述据/span>那据/span>date_published.据/span>那据/span>cvss_vector据/span>那据/span>
2据/div>
severity_score.据/span>那据/span>严重程度据/span>那据/span>pci_severity_score.据/span>那据/span>pci_status.据/span>那据/span>轮据/span>(据/span>riskscore::据/span>数字据/span>那据/span>0.据/span>)据/span>作为据/span>risk_score据/span>那据/span>
3.据/div>
轮据/span>(据/span>cvss_score ::据/span>数字据/span>那据/span>2据/span>)据/span>作为据/span>cvss_score据/span>那据/span>漏洞利用据/span>那据/span>malware_kits据/span>
4.据/div>
从据/span>dim_vulnerability.据/span>
5.据/div>
在哪里据/span>现在据/span>(据/span>)据/span>-据/span>date_published.据/span>据据/span>间隔据/span>'2周'据/span>
6.据/div>
订单据/span>经过据/span>标题据/span>ASC据/span>
最近发布的Microsoft漏洞据span title="Copy link to clipboard">
该查询可以进一步定制,以包括对漏洞类型的过滤。dim_vulnerity_category维度提供漏洞及其类别之间的关联。这个查询返回最近发布的在microsoft相关类别中定义的漏洞:据/p>
sql据div class="code-copy" title="Copy to clipboard">
1据/div>
选择据/span>nexpose_id据/span>那据/span>标题据/span>那据/span>校对据/span>(据/span>描述据/span>)据/span>作为据/span>描述据/span>那据/span>date_published.据/span>那据/span>cvss_vector据/span>那据/span>
2据/div>
severity_score.据/span>那据/span>严重程度据/span>那据/span>pci_severity_score.据/span>那据/span>pci_status.据/span>那据/span>轮据/span>(据/span>riskscore::据/span>数字据/span>那据/span>0.据/span>)据/span>作为据/span>risk_score据/span>那据/span>
3.据/div>
轮据/span>(据/span>cvss_score ::据/span>数字据/span>那据/span>2据/span>)据/span>作为据/span>cvss_score据/span>那据/span>漏洞利用据/span>那据/span>malware_kits据/span>
4.据/div>
从据/span>dim_vulnerability.据/span>
5.据/div>
在哪里据/span>现在据/span>(据/span>)据/span>-据/span>date_published.据/span>据据/span>间隔据/span>'2周'据/span>和据/span>vulnerability_id.据/span>在据/span>(据/span>
6.据/div>
选择据/span>清楚的据/span>vulnerability_id.据/span>
7.据/div>
从据/span>dim_vulnerability_category.据/span>
8.据/div>
在哪里据/span>降低据/span>(据/span>分类名称据/span>)据/span>就像据/span>“微软% %”据/span>
9.据/div>
)据/span>
10据/div>
订单据/span>经过据/span>标题据/span>ASC据/span>
由于DIM_VULNERABLATY_CATEGORY维度包含所有类别关联,所述子系统仅返回匹配(不区分大小写)术语“Microsoft”匹配的漏洞的不同标识符。子选择技术是理想的,避免返回可能已返回的重复行,如果dim_vulnerability维度与dim_vulnerability_category维度自然地连接。据/p>
最近发表了严重的Microsoft漏洞据span title="Copy link to clipboard">
通过使用安全控制台的严重性描述,可以进一步缩小漏洞的搜索范围:据/p>
sql据div class="code-copy" title="Copy to clipboard">
1据/div>
选择据/span>nexpose_id据/span>那据/span>标题据/span>那据/span>校对据/span>(据/span>描述据/span>)据/span>作为据/span>描述据/span>那据/span>date_published.据/span>那据/span>cvss_vector据/span>那据/span>
2据/div>
severity_score.据/span>那据/span>严重程度据/span>那据/span>pci_severity_score.据/span>那据/span>pci_status.据/span>那据/span>轮据/span>(据/span>riskscore::据/span>数字据/span>那据/span>0.据/span>)据/span>作为据/span>risk_score据/span>那据/span>
3.据/div>
轮据/span>(据/span>cvss_score ::据/span>数字据/span>那据/span>2据/span>)据/span>作为据/span>cvss_score据/span>那据/span>漏洞利用据/span>那据/span>malware_kits据/span>
4.据/div>
从据/span>dim_vulnerability.据/span>
5.据/div>
在哪里据/span>现在据/span>(据/span>)据/span>-据/span>date_published.据/span>据据/span>间隔据/span>'2周'据/span>和据/span>严重程度据/span>=据/span>'危急'据/span>和据/span>vulnerability_id.据/span>在据/span>(据/span>
6.据/div>
选择据/span>清楚的据/span>vulnerability_id.据/span>
7.据/div>
从据/span>dim_vulnerability_category.据/span>
8.据/div>
在哪里据/span>降低据/span>(据/span>分类名称据/span>)据/span>就像据/span>“微软% %”据/span>
9.据/div>
)据/span>
10据/div>
订单据/span>经过据/span>标题据/span>ASC据/span>
最近与CVES发布的Microsoft漏洞据span title="Copy link to clipboard">
漏洞和CVES之间的关系存储为DIM_VULNERABLATY_REFERY维度中的“引用”。这是一对多维度,这意味着如果漏洞有多个引用,则在此表中将有多个记录。因此,在SQL中有几种方法可以提取此数据:(1)子选项(2)通过表达式通过表达式或(3)与表达式连接。您选择的方法是设计偏好。在任何一种情况下,我们将崩溃到单个列中的多个引用,这意味着我们必须以任何一种方式执行分组聚合。聚合函数据a href="https://www.postgresql.org/docs/9.0/static/functions-aggregate.html">array_agg.据/a>和据a href="https://www.postgresql.org/docs/9.0/static/functions-array.html">array_to_string.据/a>函数对于执行此反规格化时格式化输出非常有用。让我们看看所有这些选项:据/p>
使用子选择据span title="Copy link to clipboard">
sql据div class="code-copy" title="Copy to clipboard">
1据/div>
选择据/span>nexpose_id据/span>那据/span>标题据/span>那据/span>校对据/span>(据/span>描述据/span>)据/span>作为据/span>描述据/span>那据/span>date_published.据/span>那据/span>cvss_vector据/span>那据/span>
2据/div>
severity_score.据/span>那据/span>严重程度据/span>那据/span>pci_severity_score.据/span>那据/span>pci_status.据/span>那据/span>轮据/span>(据/span>riskscore::据/span>数字据/span>那据/span>0.据/span>)据/span>作为据/span>risk_score据/span>那据/span>
3.据/div>
轮据/span>(据/span>cvss_score ::据/span>数字据/span>那据/span>2据/span>)据/span>作为据/span>cvss_score据/span>那据/span>漏洞利用据/span>那据/span>malware_kits据/span>那据/span>
4.据/div>
(据/span>选择据/span>array_to_string.据/span>(据/span>array_agg.据/span>(据/span>参考据/span>)据/span>那据/span>”、“据/span>)据/span>从据/span>dim_vulnerability_reference据/span>在哪里据/span>源据/span>=据/span>“CVE”据/span>和据/span>vulnerability_id.据/span>=据/span>DV.据/span>。据/span>vulnerability_id.据/span>)据/span>作为据/span>c据/span>
5.据/div>
从据/span>dim_vulnerability dv据/span>
6.据/div>
在哪里据/span>现在据/span>(据/span>)据/span>-据/span>date_published.据/span>据据/span>间隔据/span>'2周'据/span>和据/span>vulnerability_id.据/span>在据/span>(据/span>
7.据/div>
选择据/span>清楚的据/span>vulnerability_id.据/span>
8.据/div>
从据/span>dim_vulnerability_category.据/span>
9.据/div>
在哪里据/span>降低据/span>(据/span>分类名称据/span>)据/span>就像据/span>“微软% %”据/span>
10据/div>
)据/span>
11据/div>
订单据/span>经过据/span>标题据/span>ASC据/span>
12据/div>
在此方法中,子选择用于查找来自DIM_VULNERABLAY_REFIRED维度的所有引用,该维度具有匹配的“CVE”的源名称。要从DIM_VULNERIALY维度进行比较漏洞,称为“DV”的别名即可避免歧义。由于每个漏洞都有多个潜在的CVE引用,因此他们需要展平到单个输出串。这是通过使用array_agg聚合函数来完成的。这需要单个值并将它们放入数组中。但是,阵列将在输出中使用方括号格式化,因此CSV格式不得很干净。要使输出更加呈现,可以使用array_to_string函数将该数组解析为具有自定分隔符的字符串。在这种情况下,阵列中的字段使用逗号作为分隔符趋化为字符串。据/p>
使用加入据span title="Copy link to clipboard">
sql据div class="code-copy" title="Copy to clipboard">
1据/div>
选择据/span>nexpose_id据/span>那据/span>标题据/span>那据/span>校对据/span>(据/span>描述据/span>)据/span>作为据/span>描述据/span>那据/span>date_published.据/span>那据/span>cvss_vector据/span>那据/span>
2据/div>
severity_score.据/span>那据/span>严重程度据/span>那据/span>pci_severity_score.据/span>那据/span>pci_status.据/span>那据/span>轮据/span>(据/span>riskscore::据/span>数字据/span>那据/span>0.据/span>)据/span>作为据/span>risk_score据/span>那据/span>
3.据/div>
轮据/span>(据/span>cvss_score ::据/span>数字据/span>那据/span>2据/span>)据/span>作为据/span>cvss_score据/span>那据/span>漏洞利用据/span>那据/span>malware_kits据/span>那据/span>vcf据/span>。据/span>c据/span>
4.据/div>
从据/span>dim_vulnerability dv据/span>
5.据/div>
加入据/span>(据/span>
6.据/div>
选择据/span>vulnerability_id.据/span>那据/span>array_to_string.据/span>(据/span>array_agg.据/span>(据/span>参考据/span>)据/span>那据/span>”、“据/span>)据/span>作为据/span>c据/span>
7.据/div>
从据/span>dim_vulnerability_reference据/span>
8.据/div>
在哪里据/span>源据/span>=据/span>“CVE”据/span>
9.据/div>
集团据/span>经过据/span>vulnerability_id.据/span>
10据/div>
)据/span>作为据/span>vcf据/span>使用据/span>(据/span>vulnerability_id.据/span>)据/span>
11据/div>
在哪里据/span>现在据/span>(据/span>)据/span>-据/span>date_published.据/span>据据/span>间隔据/span>'2周'据/span>和据/span>vulnerability_id.据/span>在据/span>(据/span>
12据/div>
选择据/span>清楚的据/span>vulnerability_id.据/span>
13据/div>
从据/span>dim_vulnerability_category.据/span>
14据/div>
在哪里据/span>降低据/span>(据/span>分类名称据/span>)据/span>就像据/span>“微软% %”据/span>
15据/div>
)据/span>
16据/div>
订单据/span>经过据/span>标题据/span>ASC据/span>
在这种方法中,嵌套JOIN执行搜索,为每个漏洞找到所有cve(使用上面描述的相同聚合技术)。然后,将得到的数据自然地连接起来,取出cve。据/p>
用表达式使用据span title="Copy link to clipboard">
sql据div class="code-copy" title="Copy to clipboard">
1据/div>
和据/span>
2据/div>
vulnerability_cves据/span>作为据/span>(据/span>
3.据/div>
选择据/span>vulnerability_id.据/span>那据/span>array_to_string.据/span>(据/span>array_agg.据/span>(据/span>参考据/span>)据/span>那据/span>”、“据/span>)据/span>作为据/span>c据/span>
4.据/div>
从据/span>dim_vulnerability_reference据/span>
5.据/div>
在哪里据/span>源据/span>=据/span>“CVE”据/span>
6.据/div>
集团据/span>经过据/span>vulnerability_id.据/span>
7.据/div>
)据/span>
8.据/div>
选择据/span>nexpose_id据/span>那据/span>标题据/span>那据/span>校对据/span>(据/span>描述据/span>)据/span>作为据/span>描述据/span>那据/span>date_published.据/span>那据/span>cvss_vector据/span>那据/span>
9.据/div>
severity_score.据/span>那据/span>严重程度据/span>那据/span>pci_severity_score.据/span>那据/span>pci_status.据/span>那据/span>轮据/span>(据/span>riskscore::据/span>数字据/span>那据/span>0.据/span>)据/span>作为据/span>risk_score据/span>那据/span>
10据/div>
轮据/span>(据/span>cvss_score ::据/span>数字据/span>那据/span>2据/span>)据/span>作为据/span>cvss_score据/span>那据/span>漏洞利用据/span>那据/span>malware_kits据/span>那据/span>vcf据/span>。据/span>c据/span>
11据/div>
从据/span>dim_vulnerability dv据/span>
12据/div>
加入据/span>vulnerability_cves vcves.据/span>使用据/span>(据/span>vulnerability_id.据/span>)据/span>
13据/div>
在哪里据/span>现在据/span>(据/span>)据/span>-据/span>date_published.据/span>据据/span>间隔据/span>'2周'据/span>和据/span>vulnerability_id.据/span>在据/span>(据/span>
14据/div>
选择据/span>清楚的据/span>vulnerability_id.据/span>
15据/div>
从据/span>dim_vulnerability_category.据/span>
16据/div>
在哪里据/span>降低据/span>(据/span>分类名称据/span>)据/span>就像据/span>“微软% %”据/span>
17据/div>
)据/span>
18据/div>
订单据/span>经过据/span>标题据/span>ASC据/span>
在这种方法中,我们利用了据a href="https://www.postgresql.org/docs/9.0/static/queries-with.html">久违的问题据/a>表达式,或更正式地称为公共表表达式。该技术很简单,并在据a href="//m.gcpym.com/insightvm/sql-query-export-examples">SQL查询导出示例据/a>文章。漏洞表达式首先执行搜索以查找每个漏洞的所有CVE(具有上述相同的聚合技术)。然后,该输出通过vUSTNEASIBLE_ID自然地与DIM_VULERNATY维度加入DIM_VULNERABLY维度,以拔出CVE列。正如您可以自己测试的那样,输出与前两种方法相同。与加入方法相比,您会看到一些显着的相似之处,但许多SQL开发人员将声称这种方法更可读。据/p>
最近KBs发表了《严重的微软漏洞》据span title="Copy link to clipboard">
请尝试为cve扩展上面的示例,以便自己提取Microsoft KB引用。据/p>
如您所见,定制查询以检索感兴趣的漏洞非常简单,并且可以根据您的需要进行定制。尝试查看以名称“dim_vulnerity_”开头的其他维度,以查看可供过滤的其他元数据。据/p>