Help extracting and paring nested JSON for table

[dwodeyla_bit9]

I'm indexing some JSON data that describes an AWS security group. Inside this JSON are nested pairs of port combinations (so a "from port" and a "to port" field) that are paired up with a set of allowed IP addresses. These JSON blobs can contain multiple sets of port pairs, with an arbitrary number of their own unique IP addresses.

I'm attempting to take this JSON data and separate out results into their IP, and port pairs into a table.

I've attempt the following search, which does properly expand my results to return three events for the three separate pairs of ports (22, 8301, 8301 for the below example), but now I'm stuck trying to separate out the IPs to match up properly as well.

| rename rules{}.to_port as to_port, rules{}.from_port as from_port, rules{}.grants{}.cidr_ip as cidr_ip
| eval a = mvzip(from_port,to_port,"|")
| mvexpand a
| rex field=a "(?<from_port>[^\|]+)\|(?<to_port>[^\|]+)"
| table id,name,description,attach,cidr_ip,from_port,to_port

End result I'm looking for would be something like the below table.

cidr_ip        from_port     to_port
1.1.1.1/32     22            22
1.1.1.2/32     22            22
10.9.0.0/16    8301           8301

Below is a below example of the JSON data.

 {
  "instances": [
    {
      "id": "i-03"
    }
  ],
  "region": "us-east-1",
  "id": "sg-51111111",
  "name": "example",
  "description": "example",
  "account_id": "111111111111",
  "owner_id": "111111111111",
  "rules_egress": [
    {
      "from_port": null,
      "ipRanges": "",
      "grants": [
        {
          "owner_id": null,
          "name": null,
          "group_id": null,
          "cidr_ip": "0.0.0.0/0"
        }
      ],
      "groups": "",
      "ip_protocol": "-1",
      "to_port": null
    }
  ],
  "vpc_id": "vpc-7132332321a",
  "tags": {},
  "rules": [
    {
      "from_port": "22",
      "ipRanges": "",
      "grants": [
        {
          "owner_id": null,
          "name": null,
          "group_id": null,
          "cidr_ip": "1.1.1.7/32"
        },
        {
          "owner_id": null,
          "name": null,
          "group_id": null,
          "cidr_ip": "1.1.1.1/32"
        },
        {
          "owner_id": null,
          "name": null,
          "group_id": null,
          "cidr_ip": "1.1.1.2/32"
        },
        {
          "owner_id": null,
          "name": null,
          "group_id": null,
          "cidr_ip": "1.1.1.3/32"
        },
        {
          "owner_id": null,
          "name": null,
          "group_id": null,
          "cidr_ip": "1.1.1.4/32"
        },
        {
          "owner_id": null,
          "name": null,
          "group_id": null,
          "cidr_ip": "1.1.1.5/32"
        },
        {
          "owner_id": null,
          "name": null,
          "group_id": null,
          "cidr_ip": "1.1.1.6/32"
        }
      ],
      "groups": "",
      "ip_protocol": "tcp",
      "to_port": "22"
    },
    {
      "from_port": "8301",
      "ipRanges": "",
      "grants": [
        {
          "owner_id": null,
          "name": null,
          "group_id": null,
          "cidr_ip": "10.9.0.0/16"
        }
      ],
      "groups": "",
      "ip_protocol": "udp",
      "to_port": "8301"
    },
    {
      "from_port": "8301",
      "ipRanges": "",
      "grants": [
        {
          "owner_id": null,
          "name": null,
          "group_id": null,
          "cidr_ip": "10.9.0.0/16"
        }
      ],
      "groups": "",
      "ip_protocol": "tcp",
      "to_port": "8301"
    }
  ]

[stephen_p_brown · Accepted solution]

I had the same issue along with multiple instance{}.id's i needed to break out. Here is what i did to accomplish what i needed, let me know if it works for you.

| dedup name

| spath path=rules{} output=r
| spath path=instances{}

| mvexpand "instances{}.id"
| eval instance_id='instances{}.id'
|mvexpand r
|spath input=r

|eval rule=mvzip('grants{}.cidr_ip','grants{}.group_id')
|eval rule=mvzip(rule, 'grants{}.name')
|eval rule=mvzip(rule, 'grants{}.owner_id')
|mvexpand rule
|makemv rule delim=","

|eval cidr_ip=mvindex(rule,0)
|eval group_id=mvindex(rule,1)
|eval name=mvindex(rule,2)
|eval owner_id=mvindex(rule,3)

| table instance_id from_port cidr_ip group_id name owner_id ip_protocol to_port

[dwodeyla_bit9]

Works perfectly, exactly what I was looking for, thanks!

[stephen_p_brown]

I am also working on extending the search to pull in the hit counts per SG rule utilizing the vpc flow logs, if i ever figure it out i will post it here as well. Glad it worked for you

[DalJeanis]

You can use mvzip as many times as needed to stitch various fields together before the mvexpand, and then use one rex to unzip them after the mvexpand. That would probably look like this...

 | rename rules{}.to_port as to_port, rules{}.from_port as from_port, rules{}.grants{}.cidr_ip as cidr_ip
 | eval a = mvzip(mvzip(from_port,to_port,"|"),cidr_ip,"|")
 | mvexpand a
 | rex field=a "(?<from_port>[^\|]+)\|(?<to_port>[^\|]+)\|(?<cidr_ip>[^\|]+)"
 | table id,name,description,attach,cidr_ip,from_port,to_port

You can also do it this way...

 | rename rules{}.to_port as to_port, rules{}.from_port as from_port, rules{}.grants{}.cidr_ip as cidr_ip
 | eval a = mvrange(0,mvcount(to_port))
 | mvexpand a
 | eval to_port=mvindex(to_port,a)
 | eval from_port=mvindex(from_port,a)
 | eval cidr_ip=mvindex(cidr_ip,a)
 | table id,name,description,attach,cidr_ip,from_port,to_port

What this version did was (assuming there were 5 values) create five records where a was 0 to 4, and assign the a -th value of each of the multivalue fields to the name of the multivalue field.

[dwodeyla_bit9]

I did actually try a second mvzip with the cidr_ip, but that doesn't look to work due to the uneven number of IPs in each grant.

So in the case of the example JSON, I'm only getting three stitched together results for the first three IPs in the grant for port 22, instead of getting 7 results for port 22, 1 result for 8301 tcp, and 1 result for 8301 udp (see below for what is generated with the second mvzip)

22|22|tcp|1.1.1.7/32
8301|8301|tcp|1.1.1.1/32
8301|8301|udp|1.1.1.2/32

Generated via this snip-it

| eval a = mvzip(mvzip(mvzip(from_port,to_port,"|"),ip_protocol,"|"),cidr_ip,"|") 
| mvexpand a