Getting Data In

Break up multi line event

JPaule
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

FrankVl
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

FrankVl
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
0 Karma

JPaule
Explorer

Awesome, this was a huge help!

0 Karma

JPaule
Explorer

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

0 Karma

FrankVl
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

JPaule
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

FrankVl
Ultra Champion

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

0 Karma

FrankVl
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?

JPaule
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...