Getting Data In

Break up multi line event

Explorer

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
Tags (1)
0 Karma
1 Solution

Ultra Champion

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

View solution in original post

0 Karma

Ultra Champion

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

View solution in original post

0 Karma

Explorer

Awesome, this was a huge help!

0 Karma

Explorer

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

0 Karma

Ultra Champion

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).

0 Karma

Explorer

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

alt text

0 Karma

Ultra Champion

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

0 Karma

Ultra Champion

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?

Explorer

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

0 Karma