Hello,
I'm using the JDBC input plugin to ingest vulnerability data from a Rapid7 postgres database. Everything is fine, but one of the tables I'm referencing has many rows which is causing significant duplication of my data. My goal is to have one IP address mapped to one unique vulnerability. Unfortunately the field "Vulnerability Reference IDs" contains the multiple rows and I"m hoping to have them combined. Below are two examples which I'm hoping to combine into a single record. I can't do anything on the database side unfortunately as it's proprietary.
I'd appreciate any help!!!
Record #1
{
"_index": "idx_test",
"_type": "_doc",
"_id": "KgegNG8BKC_Gpwy8RA2U",
"_version": 1,
"_score": 0,
"_source": {
"Vulnerability CVSSv3 Score": null,
"Asset IP Address": "1.2.3.4/32",
"Vulnerability Reference IDs": "CVE-2016-3262",
"Asset OS Version": "SP1",
"Vulnerability Severity": "Critical",
"Vulnerability Description": "\n \nA remote code execution vulnerability exists due to the way the Windows GDI component handles objects in the memory. An attacker who successfully exploited this vulnerability could take control of the affected system. An attacker could then install programs; view, change, or delete data; or create new accounts with full user rights. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights.
\n ",
"service": null,
"credential_status": "All credentials successful",
"type": "TEST",
"@timestamp": "2019-12-23T21:18:35.850Z",
"asset_id": 1090,
"port": null,
"tag": "TEST",
"Asset OS Family": "Windows",
"fix": "\nDownload and apply the patch from: \n<a href="http://support.microsoft.com/kb/4019108\">http://support.microsoft.com/kb/4019108</a></p>",
"protocol": null,
"Site Name": "TEST-SITE",
"mac_address": "00:50:56:a7:c2:a1",
"last_assessed_for_vulnerabilities": "2019-12-12T08:31:07.107Z",
"Vulnerability CVSSv3 Vector": null,
"Asset Names": "TARGET-WIN764",
"Vulnerability Proof": "Vulnerable OS: Microsoft Windows 7 Professional Edition SP1
Based on the following 2 results:
",
- Found an applicable package: HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Component Based Servicing\Packages\Microsoft-Windows-Client-Features-Package~31bf3856ad364e35~amd64~~6.1.7601.17514.
- HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\SideBySide\Winners\amd64_Microsoft-Windows-WebDAVRedir-ClientOnly_31bf3856ad364e35_none_d672e50a093eb855 - key exists
- The above CBS component is currently version 6.1.7600.16385, expected version 6.1.7601.23542 or higher
- Fix for KB3192391 is applicable for this CBS component
- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion
- UBR - defaults to 0
"@version": "1",
"Asset OS Name": "Windows 7 Professional Edition",
"Vulnerability Title": "MS16-120: Security Update for Microsoft Graphics Component (3192884)"
},
"fields": {
"last_assessed_for_vulnerabilities": [
"2019-12-12T08:31:07.107Z"
],
"@timestamp": [
"2019-12-23T21:18:35.850Z"
]
},
}
Record #2
{
"_index": "idx_test",
"_type": "_doc",
"_id": "LAegNG8BKC_Gpwy8RA2U",
"_version": 1,
"_score": 0,
"_source": {
"Vulnerability CVSSv3 Score": null,
"Asset IP Address": "1.2.3.4/32",
"Vulnerability Reference IDs": "CVE-2016-3270",
"Asset OS Version": "SP1",
"Vulnerability Severity": "Critical",
"Vulnerability Description": "\n \nA remote code execution vulnerability exists due to the way the Windows GDI component handles objects in the memory. An attacker who successfully exploited this vulnerability could take control of the affected system. An attacker could then install programs; view, change, or delete data; or create new accounts with full user rights. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights.
\n ",
"service": null,
"credential_status": "All credentials successful",
"type": "TEST",
"@timestamp": "2019-12-23T21:18:35.850Z",
"asset_id": 1090,
"port": null,
"tag": "TEST",
"Asset OS Family": "Windows",
"fix": "\nDownload and apply the patch from: \n<a href="http://support.microsoft.com/kb/4019108\">http://support.microsoft.com/kb/4019108</a></p>",
"protocol": null,
"Site Name": "TEST-SITE",
"mac_address": "00:50:56:a7:c2:a1",
"last_assessed_for_vulnerabilities": "2019-12-12T08:31:07.107Z",
"Vulnerability CVSSv3 Vector": null,
"Asset Names": "TARGET-WIN764",
"Vulnerability Proof": "Vulnerable OS: Microsoft Windows 7 Professional Edition SP1
Based on the following 2 results:
",
- Found an applicable package: HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Component Based Servicing\Packages\Microsoft-Windows-Client-Features-Package~31bf3856ad364e35~amd64~~6.1.7601.17514.
- HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\SideBySide\Winners\amd64_Microsoft-Windows-WebDAVRedir-ClientOnly_31bf3856ad364e35_none_d672e50a093eb855 - key exists
- The above CBS component is currently version 6.1.7600.16385, expected version 6.1.7601.23542 or higher
- Fix for KB3192391 is applicable for this CBS component
- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion
- UBR - defaults to 0
"@version": "1",
"Asset OS Name": "Windows 7 Professional Edition",
"Vulnerability Title": "MS16-120: Security Update for Microsoft Graphics Component (3192884)"
},
"fields": {
"last_assessed_for_vulnerabilities": [
"2019-12-12T08:31:07.107Z"
],
"@timestamp": [
"2019-12-23T21:18:35.850Z"
]
},
}
What I'd like to do is combine the strings in "Vulnerability Reference IDs" so I end up with a single record. In doing a compare of both records only this field as well as the document ID are unique. Some vendors like Microsoft link multiple KB's to the same vulnerability for different version of Windows - I have instances where I have over 100 records for the same IP to Vulnerability record. The only downfall that I can see in combining this, it may be more taxing to the db to perform queries against this string if it's concatenated with another.
Combined Record...
{
"_index": "idx_test",
"_type": "_doc",
"_id": "KgegNG8BKC_Gpwy8RA2U",
"_version": 1,
"_score": 0,
"_source": {
"Vulnerability CVSSv3 Score": null,
"Asset IP Address": "1.2.3.4/32",
"Vulnerability Reference IDs": "CVE-2016-3262, CVE-2016-3270"
"Asset OS Version": "SP1",
"Vulnerability Severity": "Critical",
"Vulnerability Description": "\n \nA remote code execution vulnerability exists due to the way the Windows GDI component handles objects in the memory. An attacker who successfully exploited this vulnerability could take control of the affected system. An attacker could then install programs; view, change, or delete data; or create new accounts with full user rights. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights.
\n ",
"service": null,
"credential_status": "All credentials successful",
"type": "TEST",
"@timestamp": "2019-12-23T21:18:35.850Z",
"asset_id": 1090,
"port": null,
"tag": "TEST",
"Asset OS Family": "Windows",
"fix": "\n
Download and apply the patch from: \nSecurity Only update for the .NET Framework 3.5.1, 4.5.2, 4.6, 4.6.1, and 4.6.2 updates for Windows 7 Service Pack 1 and Windows Server 2008 R2 Service Pack 1: May 9, 2017 - Microsoft Support",
"protocol": null,
"Site Name": "TEST-SITE",
"mac_address": "00:50:56:a7:c2:a1",
"last_assessed_for_vulnerabilities": "2019-12-12T08:31:07.107Z",
"Vulnerability CVSSv3 Vector": null,
"Asset Names": "TARGET-WIN764",
"Vulnerability Proof": "
Vulnerable OS: Microsoft Windows 7 Professional Edition SP1Based on the following 2 results:
Found an applicable package: HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Component Based Servicing\Packages\Microsoft-Windows-Client-Features-Package~31bf3856ad364e35~amd64~~6.1.7601.17514.
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\SideBySide\Winners\amd64_Microsoft-Windows-WebDAVRedir-ClientOnly_31bf3856ad364e35_none_d672e50a093eb855 - key exists
The above CBS component is currently version 6.1.7600.16385, expected version 6.1.7601.23542 or higher
Fix for KB3192391 is applicable for this CBS component
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion
UBR - defaults to 0
",
"@version": "1",
"Asset OS Name": "Windows 7 Professional Edition",
"Vulnerability Title": "MS16-120: Security Update for Microsoft Graphics Component (3192884)"
},
"fields": {
"last_assessed_for_vulnerabilities": [
"2019-12-12T08:31:07.107Z"
],
"@timestamp": [
"2019-12-23T21:18:35.850Z"
]
},
}
Below is my logstash configuration (lab config). "F.reference" is the column in question.
input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://192.168.65.240:5432/nexpose"
jdbc_user => "nexpose"
jdbc_password => "mysecretpassword"
jdbc_driver_class => "org.postgresql.Driver"
#jdbc_paging_enabled => true
#jdbc_page_size => "5"
#jdbc_fetch_size => 10
clean_run => true
statement => "SELECT
A.asset_id,
CAST(mac_address AS varchar),
A.sites,
A.host_name,
CAST(ip_address AS varchar),
A.os_name,
A.os_version,
A.os_family,
A.credential_status,
A.last_assessed_for_vulnerabilities,
C.name AS Tag,
E.title,
E.severity,
E.cvss_v3_score,
E.cvss_v3_vector,
E.description,
F.reference,
K.fix,
I.proof,
I.service,
I.port,
I.protocol
from dim_asset A
LEFT JOIN dim_asset_tag B ON A.asset_id = B.asset_id
LEFT JOIN dim_tag C on B.tag_id = C.tag_id
LEFT JOIN fact_asset_vulnerability_finding_exploit_remediation D on A.asset_id = D.asset_id
LEFT JOIN dim_vulnerability E ON D.vulnerability_id = E.vulnerability_id
LEFT JOIN dim_vulnerability_reference F ON D.vulnerability_id = F.vulnerability_id
LEFT JOIN fact_asset_vulnerability_instance I ON E.vulnerability_id = I.vulnerability_id AND A.asset_id = I.asset_id
LEFT JOIN dim_asset_vulnerability_finding_rollup_solution J ON A.asset_id = J.asset_id AND E.vulnerability_id = J.vulnerability_id
LEFT JOIN dim_solution K ON J.solution_id = K.solution_id
where C.name = 'TEST'"
type => "TEST"
}
}filter {
mutate {
rename => ["sites", "Site Name" ]
rename => ["host_name", "Asset Names"]
rename => ["ip_address", "Asset IP Address" ]
rename => ["os_name", "Asset OS Name" ]
rename => ["os_version", "Asset OS Version" ]
rename => ["os_family", "Asset OS Family" ]
rename => ["vulnerability_id", "Vulnerability ID"]
rename => ["title", "Vulnerability Title"]
rename => ["severity", "Vulnerability Severity"]
rename => ["cvss_v3_score", "Vulnerability CVSSv3 Score"]
rename => ["cvss_v3_vector", "Vulnerability CVSSv3 Vector"]
rename => ["description", "Vulnerability Description"]
rename => ["source", "Vulnerability Source"]
rename => ["reference", "Vulnerability Reference IDs"]
rename => ["proof", "Vulnerability Proof"]
}
}output {
if [type] == "TEST" { elasticsearch { hosts => ["192.168.65.240:9200"] index => "idx_test" } }
stdout {}
}