欢迎来到成都欧利德电子科技有限公司官网!

服务热线:028-87532808

13308024651 15928846598

联系我们

电话: 13308024651 15928846598
手机:028-87532808
邮箱:912733050@qq.com
地址:成都市蜀明路3号A-3-5C
当前位置:主页 > 新闻资讯 > 行业新闻 >

行业新闻

Nexpose中基于SQL查询创建定制报告

作者:cdold 时间:2020-01-13 14:40

为Rapid7 Nexpose产品的亮点之一,我们给用户提供了直接运行SQL语句查询来输出定制化报告的功能.

这样使得访问共享资产,漏洞,策略,结果等数据的工作非常灵活.

利用CSV格式的各种功能,您可以创建数据图表,图形以操控查询您想要的输出结果.

 

前提条件:

您需要有SQL的实际工作经验,包括编写及查询和理解数据类型等能力,才能使用SQL语句查询导出您想要的结果.

 

与现有报告有何区别:

基于Nexpose已经支持各种类型的导出格式,下面的表格描述将显示两者的差异和相似之处:

 

 

您能得到哪些数据:

资产元数据:主机名,ip地址/别名,mac地址,操作系统,设备类型,用户,组,服务,软件

扫描结果:漏洞调查结果,实例,以及站点,资产,资产组

漏洞例外情况:已批准和待处理的例外,以及它们对应的漏洞

漏洞定义:摘要,说明,外部参考

脆弱性解决方案:摘要,补救步骤,Support信息

以及更多更多……
 

暂时不能得到哪些数据:

以下是我们计划在不久的将来在报告数据模型中新增的几个:

策略和配置合规性结果

扫描引擎

扫描模版

扫描计划

 

定义查询并运行报告:

1:点击安全控制台界面左侧的”报告”图标

2:在创建报告页面,选择”导出”选项,选择SQL Query Export模版.

(生成报告的时候选择相应站点或坏扫描任务即可,SQL会自动添加条件)

可选项:

如果您想把查询集中到某个固定资产,则在Scope部分选择相应的扫描或者站点.

如果您想限制漏洞过滤结果,则在Scope部分添加相应的过滤条件.

3:点击定义查询的文本框,安全控制台将显示用于定义查询的页面.

 


 

4:在此文本框中输入查询语句.点击帮助小图标,查看示例.

5:点击验证按钮,查看和更新查询的任务错误,验证过程会很快.

6:点击预览按钮,验证查询输出是否符合您想要的内容.

运行预览所需要的时间取决于数据量和查询的复杂程度.

7:如有需要,可根据结果做相应调整和改动.点击”完成”则保存查询并运行报告.

SQL查询导出示例:

所有漏洞:

SELECT nexpose_id, title,proofAsText(description) AS description, date_published, cvss_vector,

   severity_score, severity,pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score,

   round(cvss_score::numeric, 2) AS cvss_score,exploits, malware_kits

FROM dim_vulnerability

ORDER BY title ASC

最近发布的漏洞:

SELECT nexpose_id, title,proofAsText(description) AS description, date_published, cvss_vector,

   severity_score,severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) ASrisk_score,

  round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits

FROM dim_vulnerability

WHERE now() -date_published < INTERVAL '2 weeks'

ORDER BY title ASC

 

最近微软发布的漏洞:

SELECT nexpose_id, title,proofAsText(description) AS description, date_published, cvss_vector,

  severity_score, severity, pci_severity_score, pci_status,round(riskscore::numeric, 0) AS risk_score,

  round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits

FROM dim_vulnerability

WHERE now() -date_published < INTERVAL '2 weeks' AND vulnerability_id IN (

   SELECTDISTINCT vulnerability_id

   FROMdim_vulnerability_category

   WHERElower(category_name) LIKE '%microsoft%')

ORDER BY title ASC

 

近日微软发布的严重漏洞:

SELECT nexpose_id, title,proofAsText(description) AS description, date_published, cvss_vector,

  severity_score, severity, pci_severity_score, pci_status,round(riskscore::numeric, 0) AS risk_score,

   round(cvss_score::numeric,2) AS cvss_score, exploits, malware_kits

FROM dim_vulnerability

WHERE now() -date_published < INTERVAL '2 weeks' AND severity = 'Critical' ANDvulnerability_id IN (

   SELECTDISTINCT vulnerability_id

   FROMdim_vulnerability_category

   WHERElower(category_name) LIKE '%microsoft%')

ORDER BY title ASC

 

最近微软发布的漏洞CVE:

SELECT nexpose_id, title, proofAsText(description) AS description,date_published, cvss_vector,

  severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric,0) AS risk_score,

  round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits,

   (SELECTarray_to_string(array_agg(reference), ',') FROM dim_vulnerability_referenceWHERE source = 'CVE' AND vulnerability_id = dv.vulnerability_id) AS cves

FROM dim_vulnerability dv

WHERE now() -date_published < INTERVAL '2 weeks' AND vulnerability_id IN (

   SELECTDISTINCT vulnerability_id

   FROMdim_vulnerability_category

   WHERElower(category_name) LIKE '%microsoft%')

ORDER BY title ASC
 

Join方法:

SELECT nexpose_id, title,proofAsText(description) AS description, date_published, cvss_vector,

  severity_score, severity, pci_severity_score, pci_status,round(riskscore::numeric, 0) AS risk_score,

  round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits, vcves.cves

  FROM dim_vulnerability dv

  JOIN (SELECT vulnerability_id, array_to_string(array_agg(reference), ',') AS cves

  FROM dim_vulnerability_reference

  WHERE source = 'CVE'

  GROUP BY vulnerability_id  ) AS vcvesUSING (vulnerability_id)

  WHERE now() -date_published < INTERVAL '2 weeks' AND vulnerability_id IN (

  SELECTDISTINCT vulnerability_id

  FROMdim_vulnerability_category

  WHERElower(category_name) LIKE '%microsoft%')

  ORDER BY title ASC

 

With方法:

WITH

  vulnerability_cves AS ( SELECT vulnerability_id, array_to_string(array_agg(reference), ',') AS cves

  FROM dim_vulnerability_referenceWHERE source = 'CVE' GROUP BY vulnerability_id )

SELECT nexpose_id, title,proofAsText(description) AS description, date_published, cvss_vector,

  severity_score, severity, pci_severity_score, pci_status,round(riskscore::numeric, 0) AS risk_score,

  round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits, vcves.cves

FROM dim_vulnerability dv

   JOINvulnerability_cves vcves USING (vulnerability_id)

WHERE now() -date_published < INTERVAL '2 weeks' AND vulnerability_id IN (

   SELECTDISTINCT vulnerability_id

   FROMdim_vulnerability_category

   WHERE lower(category_name)LIKE '%microsoft%)

ORDER BY title ASC

SQL查询凭证状态举例:

Nexpose用户现在可以使用SQL查询导出一个关于凭证状态是否正常执行等相关信息的报告.

关于字段的具体意义,请到社区查找对应的内容:

https://community.rapid7.com/docs/DOC-2897

使用CSV报告导出凭证状态,其中包含不同服务提供的凭证是否正常及每个资产提供的权限级别信息:

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) 

上面语句将会导出资产id,扫描id,扫描日期,服务名称,以及凭证状态.

要在资产上的最新扫描中执行凭证状态查找方法如下:

SELECT asset_id, name,credential_status_description  

FROMdim_asset_service_credential 

JOIN dim_credential_statusUSING(credential_status_id) 

JOIN dim_serviceUSING(service_id)

SQL查询软件数据及列表举例:

如下语句提供资产数量,软件厂商,软件名称,软件系列,软件版本.

SELECT count(da.asset_id)as asset_count, ds.vendor, ds.name as software_name,  ds.family,ds.version

FROM dim_asset_softwaredas

  JOIN dim_softwareds using (software_id)

  JOIN dim_asset daon da.asset_id = das.asset_id

GROUP BY ds.vendor,ds.name, ds.family, ds.version, ds.cpe

ORDER BY asset_count DESC

如果你想知道有多少资产在运行特定软件,通过Where子句可达到你的目的,如下例子将找到.NET FrameWork SP2版本:

SELECT da.ip_address,da.host_name, ds.vendor, ds.name as software_name,  ds.family, ds.version

FROM dim_asset_softwaredas

  JOIN dim_softwareds using (software_id)

  JOIN dim_asset daon da.asset_id = das.asset_id

WHERE ds.name like'%.NETFramework 3.0%' and ds.version like '%SP2'

SQL查询证书明细信息:

SELECT DISTINCT

   da.ip_address AS "Host IP Address",

   da.host_name AS "Hostname",

   da.mac_address AS "MAC Address",

   json_certs.port AS "Port",

   json_certs.cert->>'ssl.cert.issuer.dn' AS "Issuer",

   json_certs.cert->>'ssl.cert.subject.dn' AS "Subject",

   json_certs.cert->>'ssl.cert.key.alg.name' AS "Algorithm",

   json_certs.cert->>'ssl.cert.key.rsa.modulusBits' AS "Key Size",

   json_certs.cert->>'ssl.cert.not.valid.before' AS "Invalid Before",

   json_certs.cert->>'ssl.cert.not.valid.after' AS "InvalidAfter",

   (CAST(json_certs.cert->>'ssl.cert.not.valid.after' AS DATE) -CURRENT_DATE) AS "Expires In (days)"

FROM (

   SELECTasset_id, service_id, port, json_object_agg(name, replace(value::text,'"', '')) as cert

   FROMdim_asset_service_configuration

   WHERElower(name) like 'ssl.cert.%'

   GROUP BY 1,2, 3

    ) asjson_certs

JOIN dim_asset AS da USING(asset_id)

如果您只想查看90天内到期或者90天内过期的证书,则可以用下面的语句:

SELECT DISTINCT

   da.ip_address AS "Host IP Address",

   da.host_name AS "Hostname",

   da.mac_address AS "MAC Address",

   json_certs.port AS "Port",

   json_certs.cert->>'ssl.cert.issuer.dn' AS "Issuer",

   json_certs.cert->>'ssl.cert.subject.dn' AS "Subject",

   json_certs.cert->>'ssl.cert.key.alg.name' AS "Algorithm",

   json_certs.cert->>'ssl.cert.key.rsa.modulusBits' AS "Key Size",

   json_certs.cert->>'ssl.cert.not.valid.before' AS "InvalidBefore",

   json_certs.cert->>'ssl.cert.not.valid.after' AS "InvalidAfter",

   (CAST(json_certs.cert->>'ssl.cert.not.valid.after' AS DATE) -CURRENT_DATE) AS "Expires In (days)"

FROM (

   SELECTasset_id, service_id, port, json_object_agg(name, replace(value::text,'"', '')) as cert

   FROMdim_asset_service_configuration

   WHERE lower(name)like 'ssl.cert.%'

   GROUP BY 1,2, 3

    ) asjson_certs

JOIN dim_asset AS da USING(asset_id)

WHERE(CAST(json_certs.cert->>'ssl.cert.not.valid.after' AS DATE) -CURRENT_DATE) <= 90

SQL查询凭证是否正常执行:

(生成报告的时候选择相应站点或坏扫描任务即可,SQL会自动添加条件)

SELECT da.ip_address,dim_site.name AS site_name, dos.description AS operating_system,dim_service.name AS service_name, credential_status_description  

FROMfact_asset_scan_service  

JOIN dim_credential_statusUSING(credential_status_id)

JOIN dim_serviceUSING(service_id)

JOIN dim_site_asset USING(asset_id)

JOIN dim_site USING(site_id)

JOIN dim_asset da USING(asset_id)

JOIN dim_operating_systemdos USING (operating_system_id)

JOIN dim_asset_ip_addressUSING (asset_id)

WHERE (dim_service.name ='SSH' OR dim_service.name = 'CIFS')

GROUP BY dim_site.name,dim_service.name, da.ip_address, dos.description,dim_credential_status.credential_status_description

效果如下:

如果想看整个扫描中的Credential情况:

SELECTasset_id, ip_address, dim_site.name AS site_name, scan_id, date,dim_service.name AS service_name,credential_status_description   

FROMfact_asset_scan_service   

JOIN dim_credential_statusUSING(credential_status_id) 

JOIN dim_serviceUSING(service_id) 

JOIN dim_site_asset USING(asset_id)

JOIN dim_site USING(site_id)

JOIN dim_asset_ip_addressUSING (asset_id)

效果如下: