Combining two documents by aggregating common field

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 \n

A 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: \n<a href="http://support.microsoft.com/kb/4019108">http://support.microsoft.com/kb/4019108

",
"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 \n

A 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: \n<a href="http://support.microsoft.com/kb/4019108">http://support.microsoft.com/kb/4019108

",
"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 \n

A 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: \nhttp://support.microsoft.com/kb/4019108

",
"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"
]
},
}

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 {}

}

You could combine the records using an aggregate filter.

Hello Badger,

I'm looking at the link below and I think my situation falls under example 4. Can you confirm?

elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html#plugins-filters-aggregate-push_previous_map_as_event

I'll give it a try and report back.

I appreciate your help!

Badger,

Under the aggregate filter you need to specify a time out. I'm not sure in what order the results from my SQL calls are coming in but I'm wondering if the data needs to be sorted in my SELECT statement to prevent data from expiring outside the timeout window? If this is necessary, I'll probably do it based on IP address and vulnerability title.

Thanks!

If the data is sorted then example 4 applies. Otherwise example 3 applies and your timeout would have to be long enough to allow the first and last events to be aggregated, and also for the aggregate filter to contain the entire data set in memory.

Sorting is going to be a huge optimization, one a database is well placed to perform.

Hi Badger,

The aggregation is not quite working as expected. There is aggregation but it seems incomplete. Two events below....

Event #1

> {
>   "_index": "test_vulnerability_report",
>   "_type": "_doc",
>   "_id": "YQp-aW8BKC_Gpwy8VZJW",
>   "_version": 1,
>   "_score": 0,
>   "_source": {
>     "mac_address": "00:50:56:a7:50:87",
>     "protocol": null,
>     "vulnerability_source": [
>       {
>         "source": "CVE",
>         "reference": "CVE-2016-7287"
>       },
>       {
>         "source": "CVE",
>         "reference": "CVE-2016-3390"
>       },
>       {
>         "source": "CVE",
>         "reference": "CVE-2016-3391"
>       },
>       {
>         "source": "CVE",
>         "reference": "CVE-2016-7283"
>       },
>       {
>         "source": "MS",
>         "reference": "MS16-135"
>       },
>       {
>         "source": "BID",
>         "reference": "93356"
>       },
>       {
>         "source": "MSKB",
>         "reference": "3178465"
>       },
>       {
>         "source": "BID",
>         "reference": "93393"
>       },
>       {
>         "source": "BID",
>         "reference": "93396"
>       }
>     ],
>     "credential_status": "All credentials successful",
>     "asset_id": 1089,
>     "port": null,
>     "sites": "test-GPNET",
>     "description": "\n    \n<p>Multiple remote code execution vulnerabilities exist in the way that Internet Explorer accesses objects in memory. The vulnerabilities could corrupt memory in a way that could allow an attacker to execute arbitrary code in the context of the current user. An attacker who successfully exploited the vulnerabilities could gain the same user rights as the current user. If the current user is logged on with administrative user rights, the attacker could take control of an affected system. An attacker could then install programs; view, change, or delete data; or create new accounts with full user rights.</p>\n  ",
>     "os_family": "Windows",
>     "cvss_v3_score": null,
>     "proof": "<p><p>Vulnerable OS: Microsoft Windows 10 Professional Edition<p></p></p><p>Based on the following 2 results:<ol><li><p><ul><li>HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows NT\\CurrentVersion<ul><li>CurrentBuild - contains 10240</li></ul></li></ul></p></li><li><p><ul><li>HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows NT\\CurrentVersion<ul><li>UBR - contains 16384</li></ul></li></ul></p></li></ol></p></p>",
>     "type": "test",
>     "severity": "Critical",
>     "os_name": "Windows 10 Professional Edition",
>     "last_assessed_for_vulnerabilities": "2019-12-12T08:31:01.167Z",
>     "service": null,
>     "@version": "1",
>     "cvss_v3_vector": null,
>     "ip_address": "1.2.3.4/32",
>     "title": "MS16-118: Cumulative Security Update for Internet Explorer (3192887)",
>     "@timestamp": "2020-01-03T03:41:24.603Z",
>     "os_version": null,
>     "host_name": "DESKTOP-P3TC7DR",
>     "fix": "\n<p>Download and apply the patch from: \n<a href=\"http://support.microsoft.com/kb/3210720\">http://support.microsoft.com/kb/3210720</a></p>"
>   },
>   "fields": {
>     "last_assessed_for_vulnerabilities": [
>       "2019-12-12T08:31:01.167Z"
>     ],
>     "@timestamp": [
>       "2020-01-03T03:41:24.603Z"
>     ]
>   },
> }

Event #2

> {
>   "_index": "test_vulnerability_report",
>   "_type": "_doc",
>   "_id": "Ywp-aW8BKC_Gpwy8VZJW",
>   "_version": 1,
>   "_score": 0,
>   "_source": {
>     "mac_address": "00:50:56:a7:50:87",
>     "protocol": null,
>     "vulnerability_source": [
>       {
>         "source": "MSKB",
>         "reference": "3204063"
>       },
>       {
>         "source": "CVE",
>         "reference": "CVE-2016-7189"
>       },
>       {
>         "source": "CVE",
>         "reference": "CVE-2016-7190"
>       },
>       {
>         "source": "CVE",
>         "reference": "CVE-2016-7281"
>       },
>       {
>         "source": "CVE",
>         "reference": "CVE-2016-7238"
>       },
>       {
>         "source": "MSKB",
>         "reference": "3193227"
>       },
>       {
>         "source": "MS",
>         "reference": "MS16-124"
>       },
>       {
>         "source": "CVE",
>         "reference": "CVE-2016-3300"
>       },
>       {
>         "source": "CVE",
>         "reference": "CVE-2016-3298"
>       },
>       {
>         "source": "CVE",
>         "reference": "CVE-2016-3331"
>       }
>     ],
>     "credential_status": "All credentials successful",
>     "asset_id": 1089,
>     "port": null,
>     "sites": "test-GPNET",
>     "description": "\n    \n<p>Multiple remote code execution vulnerabilities exist in the way that Internet Explorer accesses objects in memory. The vulnerabilities could corrupt memory in a way that could allow an attacker to execute arbitrary code in the context of the current user. An attacker who successfully exploited the vulnerabilities could gain the same user rights as the current user. If the current user is logged on with administrative user rights, the attacker could take control of an affected system. An attacker could then install programs; view, change, or delete data; or create new accounts with full user rights.</p>\n  ",
>     "os_family": "Windows",
>     "cvss_v3_score": null,
>     "proof": "<p><p>Vulnerable OS: Microsoft Windows 10 Professional Edition<p></p></p><p>Based on the following 2 results:<ol><li><p><ul><li>HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows NT\\CurrentVersion<ul><li>CurrentBuild - contains 10240</li></ul></li></ul></p></li><li><p><ul><li>HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows NT\\CurrentVersion<ul><li>UBR - contains 16384</li></ul></li></ul></p></li></ol></p></p>",
>     "type": "test",
>     "severity": "Critical",
>     "os_name": "Windows 10 Professional Edition",
>     "last_assessed_for_vulnerabilities": "2019-12-12T08:31:01.167Z",
>     "service": null,
>     "@version": "1",
>     "cvss_v3_vector": null,
>     "ip_address": "1.2.3.4/32",
>     "title": "MS16-118: Cumulative Security Update for Internet Explorer (3192887)",
>     "@timestamp": "2020-01-03T03:41:24.604Z",
>     "os_version": null,
>     "host_name": "DESKTOP-P3TC7DR",
>     "fix": "\n<p>Download and apply the patch from: \n<a href=\"http://support.microsoft.com/kb/3210720\">http://support.microsoft.com/kb/3210720</a></p>"
>   },
>   "fields": {
>     "last_assessed_for_vulnerabilities": [
>       "2019-12-12T08:31:01.167Z"
>     ],
>     "@timestamp": [
>       "2020-01-03T03:41:24.604Z"
>     ]
>   },
> }

I was expecting all the vulnerability sources and references to be part of the one event. Not sure why. Is it possible it's due to time stamps? When I did a compare to both JSON outputs, other than the vulnerability source and reference fields, the time stamps differ. Some events don't have aggregation at all so I'm missing something.

Logstash config...

> input {
>     jdbc {
>         jdbc_connection_string => "jdbc:postgresql://192.168.65.240:5432/test"
>         jdbc_user => "nexpose"
>         jdbc_password => "mysecretpassword"
>         jdbc_driver_class => "org.postgresql.Driver"
>         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.source,
>         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'
>         ORDER BY ip_address, E.title"
>         type => "test"
>     }
> }
> 
> filter {
>   aggregate {
>     task_id => "%{ip_address}-%{sites}"
>     code =>
>     "
>       map ['asset_id'] = event.get('asset_id')
>       map ['mac_address'] = event.get('mac_address')
>       map ['sites'] = event.get('sites')
>       map ['host_name'] = event.get('host_name')
>       map ['ip_address'] = event.get('ip_address')
>       map ['os_name'] = event.get('os_name')
>       map ['os_version'] = event.get('os_version')
>       map ['os_family'] = event.get('os_family')
>       map ['credential_status'] = event.get('credential_status')
>       map ['last_assessed_for_vulnerabilities'] = event.get('last_assessed_for_vulnerabilities')
>       map ['title'] = event.get('title')
>       map ['severity'] = event.get('severity')
>       map ['cvss_v3_score'] = event.get('cvss_v3_score')
>       map ['cvss_v3_vector'] = event.get('cvss_v3_vector')
>       map ['description'] = event.get('description')
>       map ['vulnerability_source'] ||= []
>       map ['vulnerability_source'] << {
>         'source' => event.get('source'),
>         'reference' => event.get('reference')
>       }
>       map ['fix'] = event.get('fix')
>       map ['proof'] = event.get('proof')
>       map ['service'] = event.get('service')
>       map ['port'] = event.get('port')
>       map ['protocol'] = event.get('protocol')
>       map ['type'] = event.get('type')
>       event.cancel()
>     "
>     push_previous_map_as_event => true
>     timeout => 20
>     }
> }
> 
> output {
> 
>     if [type] == "test" {
>       elasticsearch {
>       hosts => ["192.168.65.240:9200"]
>       index => "test_vulnerability_report" }
>     }
> }

You have pipeline.workers set to 1 right? Or do you have 2 CPUs, 2 worker threads, and half the events aggregated in each thread?

Works like a charm. Just so I understand, since each thread is handling creating it's own aggregate map, events are being wrongly split?

I have one last issue.... maybe I need to move this to the Kibana sub forum?

Currently my aggregate field looks like the following (as intended, thanks to you):

"Vulnerability Advisories": [
      {
        "Source": "NVD",
        "Reference": "CVE-2019-2537"
      },
      {
        "Source": "UBUNTU",
        "Reference": "3867-1"
      }
    ]

I'm trying to generate a CSV report from Kibana and I'm receiving the following error.... which I assume is due to how the field is formatted...

[illegal_argument_exception] field [Vulnerability Advisories] isn't a leaf field

Any ideas?

No, I have never seen that error, but then I do not run Kibana :slight_smile:

I've seen bug mentions on it but it stems from having an indexed field nested into another indexed field. If I convert the field to a string, it fixes the issue but I lose the ability drill down into those nested fields on my searches.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.