topic Re: Break up multi line event in Getting Data In

Break up multi line event

JPaule — Mon, 25 Feb 2019 16:23:37 GMT

I'm trying to query for which ports are open on IP ranges, although the data has multiline information. Below is an example of one event. So if I wanted to query what is open to 0.0.0.0/0, right now my query returns 22, 443, 1515 because of how the event is structured.

How can I break up this event so I get the correct result that 22 is the only one open to 0.0.0.0/0.

Also, how can I show 443 has the 2 IP's open 11.11.55.0/24 and 12.35.12.0/26?

(Goes without saying but the data/IP's below are made up)

Acct               Port       CIDR
77812              22        0.0.0.0/0
                   443       11.11.55.0/24
                             12.35.12.0/26
                   1515      11.0.0.0/10

Re: Break up multi line event

FrankVl — Tue, 26 Feb 2019 10:55:31 GMT

Is that what your raw data looks like, or is this a representation of it after field extraction and formatting it as a table with certain search commands?

In the latter case, can you please share also the raw data and the search you had to come to this table?

Re: Break up multi line event

FrankVl — Tue, 26 Feb 2019 12:04:48 GMT

Edit: wrong assumption that the tabular data was the raw data.

Re: Break up multi line event

JPaule — Tue, 26 Feb 2019 14:06:45 GMT

Frank - Arg, sorry I didn't give you the _raw log. Here is the _raw log below, would what you gave me work for the _raw log? Attached is an image of the _raw log tabled out which is what I gave you in the first post. (I manually changed the data (i.e. IP's, acct #'s, etc, to hide secure info)

{"account_id": "2345625234", "vpc_id": "vpc-xxxxxx", "id": "sg-xxxxxx", "rules": [{"from_port": null, "groups": "\n                    ", "to_port": null, "grants": [{"owner_id": "323256661429", "group_id": "sg-xxxxx", "cidr_ip": null, "name": null}, {"owner_id": "253452345", "group_id": "sg-xxxxxx", "cidr_ip": null, "name": null}], "ipRanges": "", "ip_protocol": "-1"}, {"from_port": "22", "groups": "", "to_port": "22", "grants": [{"owner_id": null, "group_id": null, "cidr_ip": "11.233.34.0/24", "name": null}], "ipRanges": "\n                    ", "ip_protocol": "tcp"}, {"from_port": "443", "groups": "\n                    ", "to_port": "443", "grants": [{"owner_id": "2345432223", "group_id": "sg-770f1213", "cidr_ip": null, "name": null}, {"owner_id": null, "group_id": null, "cidr_ip": "12.45.50.0/24", "name": null}, {"owner_id": null, "group_id": null, "cidr_ip": "0.0.0.0/0", "name": null}], "ipRanges": "\n                    ", "ip_protocol": "tcp"}], "tags": {"aws:cloudformation:stack-id": "arn:aws-us:cloudformation:xxxx", "aws:cloudformation:stack-name": "xxx", "aws:cloudformation:logical-xx": "rSecurityGroup"}, "instances": [{"id": "i-0fqjwoi34f"}], "name": "SG-seccenter", "rules_egress": [{"from_port": null, "groups": "", "to_port": null, "grants": [{"owner_id": null, "group_id": null, "cidr_ip": "0.0.0.0/0", "name": null}], "ipRanges": "\n                    ", "ip_protocol": "-1"}], "region": "us-gov-west-1", "owner_id": "3234635623", "description": "SG for seccenter"}

Re: Break up multi line event

JPaule — Tue, 26 Feb 2019 18:34:37 GMT

I replied below, but shows up as an answer. I'm somewhat new to using this forum!

Re: Break up multi line event

FrankVl — Wed, 27 Feb 2019 08:15:38 GMT

Ok, so your raw data is actually JSON. In that case, you can use several iterations of spath and mvexpand to pull out the rules, split those, pull out the port and ranges and split those.

|  makeresults
|  eval _raw="{\"account_id\": \"2345625234\", \"vpc_id\": \"vpc-xxxxxx\", \"id\": \"sg-xxxxxx\", \"rules\": [{\"from_port\": null, \"groups\": \"\n                    \", \"to_port\": null, \"grants\": [{\"owner_id\": \"323256661429\", \"group_id\": \"sg-xxxxx\", \"cidr_ip\": null, \"name\": null}, {\"owner_id\": \"253452345\", \"group_id\": \"sg-xxxxxx\", \"cidr_ip\": null, \"name\": null}], \"ipRanges\": \"\", \"ip_protocol\": \"-1\"}, {\"from_port\": \"22\", \"groups\": \"\", \"to_port\": \"22\", \"grants\": [{\"owner_id\": null, \"group_id\": null, \"cidr_ip\": \"11.233.34.0/24\", \"name\": null}], \"ipRanges\": \"\n                    \", \"ip_protocol\": \"tcp\"}, {\"from_port\": \"443\", \"groups\": \"\n                    \", \"to_port\": \"443\", \"grants\": [{\"owner_id\": \"2345432223\", \"group_id\": \"sg-770f1213\", \"cidr_ip\": null, \"name\": null}, {\"owner_id\": null, \"group_id\": null, \"cidr_ip\": \"12.45.50.0/24\", \"name\": null}, {\"owner_id\": null, \"group_id\": null, \"cidr_ip\": \"0.0.0.0/0\", \"name\": null}], \"ipRanges\": \"\n                    \", \"ip_protocol\": \"tcp\"}], \"tags\": {\"aws:cloudformation:stack-id\": \"arn:aws-us:cloudformation:xxxx\", \"aws:cloudformation:stack-name\": \"xxx\", \"aws:cloudformation:logical-xx\": \"rSecurityGroup\"}, \"instances\": [{\"id\": \"i-0fqjwoi34f\"}], \"name\": \"SG-seccenter\", \"rules_egress\": [{\"from_port\": null, \"groups\": \"\", \"to_port\": null, \"grants\": [{\"owner_id\": null, \"group_id\": null, \"cidr_ip\": \"0.0.0.0/0\", \"name\": null}], \"ipRanges\": \"\n                    \", \"ip_protocol\": \"-1\"}], \"region\": \"us-gov-west-1\", \"owner_id\": \"3234635623\", \"description\": \"SG for seccenter\"}"
|  spath input=_raw path=rules{} output=rules
|  table rules
|  mvexpand rules
|  spath input=rules path=from_port output=port
|  spath input=rules path=grants{} output=grants
|  mvexpand grants
|  table grants port
|  spath input=grants path=cidr_ip output=cidr
|  table port cidr

Re: Break up multi line event

JPaule — Wed, 27 Feb 2019 14:14:28 GMT

Awesome, this was a huge help!

Re: Break up multi line event

JPaule — Tue, 29 Sep 2020 23:30:35 GMT

Sorry, one last question. How do I associate port cidr with account_id? When I table it account_id is blank.

Re: Break up multi line event

FrankVl — Wed, 27 Feb 2019 15:29:40 GMT

Assuming that field is already extracted: make sure to include in all the table commands (or get rid of the intermediate table commands, those were mostly there for my own use to step by step build up the query).