How to Pull Unique Records from JSON

[morgantay96]

I have read a lot of different threads and docs but still having trouble pulling what I need out of the below JSON. Essentially want a condensed list of the vulnerabilities data but this JSON nests the vulnerabilities based on the "Package". I would like a table that lists all the applicable vulns and for each image.

Table I am trying to get

Image	Name (CVE)	NVD_Score	Description	etc...
Image_name	CVE-2022-0530	4.3	A flaw was found....

Image of JSON example

I can include raw data if that would help.

[morgantay96]

  "FileHashEncoding": "zlib",
  "data_date": 1657231001,
  "digest": "sha256:fa4b861d9ef72bb594ee6546f2356ed871e5c585e9306de92a4cf54fc",
  "function_metadata"
  "image": "HIDDEN",
  "image_id": 13496,
  "image_size": 875346670,
  "initiating_user": "scheduler",
  "internal_digest_id": {
    "id": 13857
  },
  "os": "rhel",
  "previous_digest": "sha256:fa4b861d9ef72bb594ee6546f2356ed871e5c585e9306de92a4cf54fc",
  "pull_name": "HIDDEN",
  "pull_skipped": true,
  "registry": "HIDDEN",
  "required_image_platform": "amd64:::",
  "resources": [
    {
      "resource": {
        "arch": "x86_64",
        "cpe": "pkg:/rhel:8.5:pcre2:10.32-2.el8",
        "format": "rpm",
        "issue_type": "{}",
        "layer_digest": "sha256:54e56e6f85721741ee7bf0336de8ad3bf138a56769a6d0097b600",
        "license": "BSD",
        "name": "pcre2",
        "sensitive_name": "{}",
        "src_name": "pcre2",
        "src_version": "10.32-2.el8",
        "version": "10.32-2.el8"
      },
      "scanned": true,
      "vulnerabilities": [
        {
          "already_acknowledged": true,
          "aqua_score": 7.1,
          "aqua_score_classification": "Vendor CVSS V3 Score: 7.1",
          "aqua_scoring_system": "CVSS V3",
          "aqua_severity": "medium",
          "aqua_severity_classification": "Vendor Severity: moderate",
          "aqua_vectors": "CVSS:3.1/AV:N/AC:L/PR:N/UI:R/S:U/C:L/I:N/A:H",
          "description": "An out-of-bounds read vulnerability was discovered in the PCRE2 library in the compile_xclass_matchingpath() function of the pcre2_jit_compile.c file. This involves a unicode property matching issue in JIT-compiled regular expressions. The issue occurs because the character was not fully read in case-less matching within JIT.An out-of-bounds read vulnerability was discovered in the PCRE2 library in the compile_xclass_matchingpath() function of the  pcre2_jit_compile.c file. This involves a unicode property matching issue in JIT-compiled regular expressions. The issue occurs because the character was not fully read in case-less matching within JIT.",
          "fix_version": "any in rhel 9",
          "heuristic_ref_id": 2179679,
          "modification_date": "2022-06-02",
          "name": "CVE-2022-1586",
          "nvd_score": 6.4,
          "nvd_score_v3": 9.1,
          "nvd_score_version": "CVSS v2",
          "nvd_severity": "medium",
          "nvd_severity_v3": "critical",
          "nvd_url": "https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2022-1586",
          "nvd_vectors": "AV:N/AC:L/Au:N/C:P/I:N/A:P",
          "nvd_vectors_v3": "CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:N/A:H",
          "publish_date": "2022-05-16",
          "solution": "Upgrade operating system to rhel version 9 (includes fixed versions of pcre2)",
          "vendor_score_v3": 7.1,
          "vendor_score_version": "CVSS v2",
          "vendor_severity": "moderate",
          "vendor_url": "https://access.redhat.com/security/cve/CVE-2022-1586",
          "vendor_vectors_v3": "CVSS:3.1/AV:N/AC:L/PR:N/UI:R/S:U/C:L/I:N/A:H"
        }
      ]
    },
    {
      "resource": {
        "arch": "x86_64",
        "cpe": "pkg:/rhel:8.5:unzip:6.0-45.el8_4",
        "format": "rpm",
        "issue_type": "{}",
        "layer_digest": "sha256:7d53446fc2f86b9129c8e42438d860ad572982eb28d61b58697fb7682274e252",
        "license": "BSD",
        "name": "unzip",
        "sensitive_name": "{}",
        "src_name": "unzip",
        "src_version": "6.0-45.el8_4",
        "version": "6.0-45.el8_4"
      },
      "scanned": true,
      "vulnerabilities": [
        {
          "aqua_score": 5.5,
          "aqua_score_classification": "Vendor CVSS V3 Score: 5.5",
          "aqua_scoring_system": "CVSS V3",
          "aqua_severity": "low",
          "aqua_severity_classification": "Vendor Severity: low",
          "aqua_vectors": "CVSS:3.1/AV:L/AC:L/PR:N/UI:R/S:U/C:N/I:N/A:H",
          "description": "A flaw was found in Unzip. The vulnerability occurs during the conversion of a wide string to a local string that leads to a heap of out-of-bound write. This flaw allows an attacker to input a specially crafted zip file, leading to a crash or code execution.A flaw was found in Unzip. The vulnerability occurs during the conversion of a UTF-8 string to a local string that leads to a segmentation fault. This flaw allows an attacker to input a specially crafted zip file, leading to a crash or code execution.",
          "fix_version": "any in rhel 9",
          "heuristic_ref_id": 2015547,
          "modification_date": "2022-05-17",
          "name": "CVE-2022-0530",
          "nvd_score": 4.3,
          "nvd_score_v3": 5.5,
          "nvd_score_version": "CVSS v2",
          "nvd_severity": "medium",
          "nvd_severity_v3": "medium",
          "nvd_url": "https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2022-0530",
          "nvd_vectors": "AV:N/AC:M/Au:N/C:N/I:N/A:P",
          "nvd_vectors_v3": "CVSS:3.1/AV:L/AC:L/PR:N/UI:R/S:U/C:N/I:N/A:H",
          "publish_date": "2022-02-09",
          "solution": "Upgrade operating system to rhel version 9 (includes fixed versions of unzip)",
          "vendor_score_v3": 5.5,
          "vendor_score_version": "CVSS v2",
          "vendor_severity": "low",
          "vendor_statement": "This package is affected by this flaw on this platform, but there is currently no intention to fix it",
          "vendor_url": "https://access.redhat.com/security/cve/CVE-2022-0530",
          "vendor_vectors_v3": "CVSS:3.1/AV:L/AC:L/PR:N/UI:R/S:U/C:N/I:N/A:H"
        },

[ITWhisperer]

Please can you include correct JSON e.g. "function_metadata" is not complete.

[morgantay96]

{
	"FileHashEncoding": "zlib",
	"data_date": 1657231001,
	"digest": "sha256:fa4b861d9ef72bb594ee6546f2356ed871e5c585e9306de92a4cf54fc",
	"function_metadata": {},
	"image": "HIDDEN",
	"image_id": 13496,
	"image_size": 875346670,
	"initiating_user": "scheduler",
	"internal_digest_id": {
		"id": 13857
	},
	"os": "rhel",
	"previous_digest": "sha256:fa4b861d9ef72bb594ee6546f2356ed871e5c585e9306de92a4cf54fc",
	"pull_name": "HIDDEN",
	"pull_skipped": true,
	"registry": "HIDDEN",
	"required_image_platform": "amd64:::",
	"resources": [{
			"resource": {
				"arch": "x86_64",
				"cpe": "pkg:/rhel:8.5:pcre2:10.32-2.el8",
				"format": "rpm",
				"issue_type": "{}",
				"layer_digest": "sha256:54e56e6f85721741ee7bf0336de8ad3bf138a56769a6d0097b600",
				"license": "BSD",
				"name": "pcre2",
				"sensitive_name": "{}",
				"src_name": "pcre2",
				"src_version": "10.32-2.el8",
				"version": "10.32-2.el8"
			},
			"scanned": true,
			"vulnerabilities": [{
				"already_acknowledged": true,
				"aqua_score": 7.1,
				"aqua_score_classification": "Vendor CVSS V3 Score: 7.1",
				"aqua_scoring_system": "CVSS V3",
				"aqua_severity": "medium",
				"aqua_severity_classification": "Vendor Severity: moderate",
				"aqua_vectors": "CVSS:3.1/AV:N/AC:L/PR:N/UI:R/S:U/C:L/I:N/A:H",
				"description": "An out-of-bounds read vulnerability was discovered in the PCRE2 library in the compile_xclass_matchingpath() function of the pcre2_jit_compile.c file. This involves a unicode property matching issue in JIT-compiled regular expressions. The issue occurs because the character was not fully read in case-less matching within JIT.An out-of-bounds read vulnerability was discovered in the PCRE2 library in the compile_xclass_matchingpath() function of the  pcre2_jit_compile.c file. This involves a unicode property matching issue in JIT-compiled regular expressions. The issue occurs because the character was not fully read in case-less matching within JIT.",
				"fix_version": "any in rhel 9",
				"heuristic_ref_id": 2179679,
				"modification_date": "2022-06-02",
				"name": "CVE-2022-1586",
				"nvd_score": 6.4,
				"nvd_score_v3": 9.1,
				"nvd_score_version": "CVSS v2",
				"nvd_severity": "medium",
				"nvd_severity_v3": "critical",
				"nvd_url": "https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2022-1586",
				"nvd_vectors": "AV:N/AC:L/Au:N/C:P/I:N/A:P",
				"nvd_vectors_v3": "CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:N/A:H",
				"publish_date": "2022-05-16",
				"solution": "Upgrade operating system to rhel version 9 (includes fixed versions of pcre2)",
				"vendor_score_v3": 7.1,
				"vendor_score_version": "CVSS v2",
				"vendor_severity": "moderate",
				"vendor_url": "https://access.redhat.com/security/cve/CVE-2022-1586",
				"vendor_vectors_v3": "CVSS:3.1/AV:N/AC:L/PR:N/UI:R/S:U/C:L/I:N/A:H"
			}]
		},
		{
			"resource": {
				"arch": "x86_64",
				"cpe": "pkg:/rhel:8.5:unzip:6.0-45.el8_4",
				"format": "rpm",
				"issue_type": "{}",
				"layer_digest": "sha256:7d53446fc2f86b9129c8e42438d860ad572982eb28d61b58697fb7682274e252",
				"license": "BSD",
				"name": "unzip",
				"sensitive_name": "{}",
				"src_name": "unzip",
				"src_version": "6.0-45.el8_4",
				"version": "6.0-45.el8_4"
			},
			"scanned": true,
			"vulnerabilities": [{
				"aqua_score": 5.5,
				"aqua_score_classification": "Vendor CVSS V3 Score: 5.5",
				"aqua_scoring_system": "CVSS V3",
				"aqua_severity": "low",
				"aqua_severity_classification": "Vendor Severity: low",
				"aqua_vectors": "CVSS:3.1/AV:L/AC:L/PR:N/UI:R/S:U/C:N/I:N/A:H",
				"description": "A flaw was found in Unzip. The vulnerability occurs during the conversion of a wide string to a local string that leads to a heap of out-of-bound write. This flaw allows an attacker to input a specially crafted zip file, leading to a crash or code execution.A flaw was found in Unzip. The vulnerability occurs during the conversion of a UTF-8 string to a local string that leads to a segmentation fault. This flaw allows an attacker to input a specially crafted zip file, leading to a crash or code execution.",
				"fix_version": "any in rhel 9",
				"heuristic_ref_id": 2015547,
				"modification_date": "2022-05-17",
				"name": "CVE-2022-0530",
				"nvd_score": 4.3,
				"nvd_score_v3": 5.5,
				"nvd_score_version": "CVSS v2",
				"nvd_severity": "medium",
				"nvd_severity_v3": "medium",
				"nvd_url": "https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2022-0530",
				"nvd_vectors": "AV:N/AC:M/Au:N/C:N/I:N/A:P",
				"nvd_vectors_v3": "CVSS:3.1/AV:L/AC:L/PR:N/UI:R/S:U/C:N/I:N/A:H",
				"publish_date": "2022-02-09",
				"solution": "Upgrade operating system to rhel version 9 (includes fixed versions of unzip)",
				"vendor_score_v3": 5.5,
				"vendor_score_version": "CVSS v2",
				"vendor_severity": "low",
				"vendor_statement": "This package is affected by this flaw on this platform, but there is currently no intention to fix it",
				"vendor_url": "https://access.redhat.com/security/cve/CVE-2022-0530",
				"vendor_vectors_v3": "CVSS:3.1/AV:L/AC:L/PR:N/UI:R/S:U/C:N/I:N/A:H"
			}]
		}
	]
}

[yuanliu]

This requirement is highly domain specific, so I have to make some speculation about the data and your intent. To start, the top-level field "image" has the value of "HIDDEN" in your sample, so I will ignore that.  Instead, the fields resources{}.resource.name, resources{}.resource.version, and so on, are relevant.

Secondly, the resources{}.vulnerabilities{} is an array on itself but the sample data only contain one vulnerability per resource, so the sample code below takes the shortcut of only handling a single vulnerability.  To handle real data, you'll need to have another mvmap for vulnerabilities{}.  Third, there are lots of fields of interest in vulnerabilities, but the technique used below requires custom coding.  So, I am just choosing select few to illustrate.

With this said, here is a proof of concept to see if this is where the requirement leads to.

| rename resources{}.vulnerabilities{}.* AS vulnerabilities_* resources{}.resource.* AS resource_*
| eval ind = mvrange(0, mvcount(resource_name))
| eval zipped = mvmap(ind, mvappend(zipped, "resource_name=" . mvindex(resource_name, ind) . "<>" . "resource_version=" . mvindex(resource_version, ind) . "<>" . "vulnerabilities_name=" . mvindex(vulnerabilities_name, ind) . "<>" . "vulnerabilities_description=" . mvindex(vulnerabilities_description, ind) . "<>" . "vulnerabilities_nvd_severity=" . mvindex(vulnerabilities_nvd_severity, ind) . "<>" . "vulnerabilities_nvd_score=" . mvindex(vulnerabilities_nvd_score, ind)))
| mvexpand zipped
| fields zipped
| rename zipped AS _raw
| kv pairdelim="<>" kvdelim="="
| table resource_* vulnerabilities_*

Using the sample data, I get something like

resource_name	resource_version	vulnerabilities_description	vulnerabilities_name	vulnerabilities_nvd_score	vulnerabilities_nvd_severity
pcre2	10.32-2.el8	An out-of-bounds read vulnerability was discovered in the PCRE2 library in the compile_xclass_matchingpath() function of the pcre2_jit_compile.c file. This involves a unicode property matching issue in JIT-compiled regular expressions. The issue occurs because the character was not fully read in case-less matching within JIT.An out-of-bounds read vulnerability was discovered in the PCRE2 library in the compile_xclass_matchingpath() function of the pcre2_jit_compile.c file. This involves a unicode property matching issue in JIT-compiled regular expressions. The issue occurs because the character was not fully read in case-less matching within JIT.	CVE-2022-1586	6.4	medium
unzip	6.0-45.el8_4	A flaw was found in Unzip. The vulnerability occurs during the conversion of a wide string to a local string that leads to a heap of out-of-bound write. This flaw allows an attacker to input a specially crafted zip file, leading to a crash or code execution.A flaw was found in Unzip. The vulnerability occurs during the conversion of a UTF-8 string to a local string that leads to a segmentation fault. This flaw allows an attacker to input a specially crafted zip file, leading to a crash or code execution.	CVE-2022-0530	4.3	medium

[morgantay96]

In this example I would need the image and image_id zipped up with this data as well. This is vuln data so identifying the image would be really important. I changed the name to HIDDEN to remove potentially sensitive data.

[yuanliu · Accepted solution]

Because image and image_id are single value, all that is needed is to add them to display, such as the table command, e.g.,

| table image image_id resource_* vulnerabilities_*

In my sample code, I mistakenly included "| fields zipped", which was a test technique for myself, not part of the PoC.  Take it out, so the code would look like

| rename resources{}.vulnerabilities{}.* AS vulnerabilities_* resources{}.resource.* AS resource_*
| eval ind = mvrange(0, mvcount(resource_name))
| eval zipped = mvmap(ind, mvappend(zipped, "resource_name=" . mvindex(resource_name, ind) . "<>" . "resource_version=" . mvindex(resource_version, ind) . "<>" . "vulnerabilities_name=" . mvindex(vulnerabilities_name, ind) . "<>" . "vulnerabilities_description=" . mvindex(vulnerabilities_description, ind) . "<>" . "vulnerabilities_nvd_severity=" . mvindex(vulnerabilities_nvd_severity, ind) . "<>" . "vulnerabilities_nvd_score=" . mvindex(vulnerabilities_nvd_score, ind)))
| mvexpand zipped
| rename zipped AS _raw
| kv pairdelim="<>" kvdelim="="
| table image image_id resource_* vulnerabilities_*

[morgantay96]

Sorry I have one more question.

I don't want to group by resource_name. Ideally each instance of (image, cve, resource) should be unique. that way I can assess the overall count of vulnerabilities in unique records if that makes sense.

[yuanliu]

Based on sample data, it is clear that each "image" contains multiple resources, and each resource can have multiple vulnerabilities.  If you only want to group by image, one solution is to just remove fields that you don't care about and preserve the array structure.  In this case, the illustrated table in your requirement will only make sense if you don't care about resources at all.

Here is one idea

| rename resources{}.vulnerabilities{}.* AS vulnerabilities_*
| table image image_id vulnerabilities_name vulnerabilities_nvd_score vulnerabilities_nvd_severity vulnerabilities_description

With sample data, output looks like

image	image_id	vulnerabilities_name	vulnerabilities_nvd_score	vulnerabilities_nvd_severity	vulnerabilities_description
HIDDEN	13496	CVE-2022-1586 CVE-2022-0530	6.4 4.3	medium medium	An out-of-bounds read vulnerability was discovered in the PCRE2 library in the compile_xclass_matchingpath() function of the pcre2_jit_compile.c file. This involves a unicode property matching issue in JIT-compiled regular expressions. The issue occurs because the character was not fully read in case-less matching within JIT.An out-of-bounds read vulnerability was discovered in the PCRE2 library in the compile_xclass_matchingpath() function of the pcre2_jit_compile.c file. This involves a unicode property matching issue in JIT-compiled regular expressions. The issue occurs because the character was not fully read in case-less matching within JIT. A flaw was found in Unzip. The vulnerability occurs during the conversion of a wide string to a local string that leads to a heap of out-of-bound write. This flaw allows an attacker to input a specially crafted zip file, leading to a crash or code execution.A flaw was found in Unzip. The vulnerability occurs during the conversion of a UTF-8 string to a local string that leads to a segmentation fault. This flaw allows an attacker to input a specially crafted zip file, leading to a crash or code execution.

You'll notice that, unless you add additional markers or ordinals, it can become difficult for the reader to discern the alignment, especially when number of vulnerabilities becomes large.

[morgantay96]

Thank you so much!