Title
#sql
d

Divya

01/04/2022, 1:29 PM
Hi, Is there a query I can use to list all the externally open sockets along with processes using them?
koo

koo

01/04/2022, 5:11 PM
Hey Divya. Mind elaborating a bit on what ‘externally open sockets’ mean to you?
d

Divya

01/04/2022, 5:21 PM
a port which can be accessed outside the host.
s

seph

01/05/2022, 2:11 AM
Are you looking for the
process_open_sockets
table? https://osquery.io/schema/5.0.1#process_open_sockets
d

Divya

01/05/2022, 4:07 AM
I am looking for o/p of
netstat -ntlp | grep -vEe "\s+127[.]|::1"
4:08 AM
This command give me only 15 ports, while the tables listening_ports or process_open_sockets give me a 106 entries.
s

seph

01/05/2022, 11:41 AM
Well that's interesting. What's an example missing one? Are you running osquery as root?
d

Divya

01/06/2022, 5:25 AM
Yes I am running osquery as root.
5:34 AM
Here is the o.p of the netstat command for me:
netstat -ntlp | grep -vEe "\s+127[.]|::1"
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:3943            0.0.0.0:*               LISTEN      12080/nginx: master
tcp        0      0 0.0.0.0:2222            0.0.0.0:*               LISTEN      984/sshd
tcp        0      0 0.0.0.0:6002            0.0.0.0:*               LISTEN      2414/X
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      2796/sshd
tcp        0      0 0.0.0.0:3939            0.0.0.0:*               LISTEN      12080/nginx: master
tcp        0      0 0.0.0.0:3940            0.0.0.0:*               LISTEN      12080/nginx: master
tcp        0      0 0.0.0.0:3942            0.0.0.0:*               LISTEN      12080/nginx: master
tcp6       0      0 :::3944                 :::*                    LISTEN      12027/till-discover
tcp6       0      0 :::31337                :::*                    LISTEN      7746/docker-proxy
tcp6       0      0 :::2222                 :::*                    LISTEN      984/sshd
tcp6       0      0 :::6002                 :::*                    LISTEN      2414/X
tcp6       0      0 :::22                   :::*                    LISTEN      2796/sshd
tcp6       0      0 :::8087                 :::*                    LISTEN      7498/docker-proxy
tcp6       0      0 :::8888                 :::*                    LISTEN      27851/docker-proxy
tcp6       0      0 :::9369                 :::*                    LISTEN      12051/pushprox-clie
tcp6       0      0 :::8093                 :::*                    LISTEN      10615/docker-proxy
tcp6       0      0 :::8095                 :::*                    LISTEN      15096/docker-proxy
tcp6       0      0 :::3941                 :::*                    LISTEN      12066/prometheus
5:36 AM
But when I fire a query
select distinct port from listening_ports where address='0.0.0.0' and protocol=6;
I see 106 and ports. An example is port 3000. I am wondering if it is blocked at the iptables level and is there a way I can filter using that table
s

seph

01/06/2022, 4:43 PM
Those should both be using the same underlying api. I'm surprised if they're different. But it's not clear to me if your various options and filters are the same.
4:43 PM
I think you should identify one discrepancy, and then understand.
4:44 PM
Osquery can read the ip tables rules, but correlating is likely difficult.
4:44 PM
Osquery is not doing anything like sending tcp probes.
d

Divya

01/10/2022, 4:52 AM
Here is a sample response: 2222 is an open port while 6127 is not. I see no difference in the entries for both of them:
osquery> select * from listening_ports where port=2222;
+-----+------+----------+--------+---------+----+--------+------+---------------+
| pid | port | protocol | family | address | fd | socket | path | net_namespace |
+-----+------+----------+--------+---------+----+--------+------+---------------+
| 971 | 2222 | 6        | 2      | 0.0.0.0 | 3  | 24323  |      | 4026531956    |
| 971 | 2222 | 6        | 10     | ::      | 4  | 24325  |      | 4026531956    |
+-----+------+----------+--------+---------+----+--------+------+---------------+
osquery> select * from listening_ports where port=6127;
+------+------+----------+--------+-----------+----+--------+------+---------------+
| pid  | port | protocol | family | address   | fd | socket | path | net_namespace |
+------+------+----------+--------+-----------+----+--------+------+---------------+
| 5946 | 6127 | 6        | 2      | 127.0.0.1 | 4  | 56796  |      | 4026531956    |
| 7186 | 6127 | 6        | 2      | 0.0.0.0   | 19 | 112366 |      | 4026534232    |
+------+------+----------+--------+-----------+----+--------+------+---------------+
s

seph

01/10/2022, 7:06 PM
Does netstat report those differently? Is iptables in the mix?
d

Divya

01/11/2022, 5:16 AM
netstat does not consider 6127 as an exposed open port. Looks like there is someone else blocking these. I assumed iptables is the place. Will see if I can dig out something
s

seph

01/11/2022, 12:48 PM
Netstat should show the ports regardless of ip tables.
12:49 PM
I would expect netstat and osquery to be in agreement
d

Divya

01/12/2022, 9:38 AM
I would be glad if that was the case, its not
s

seph

01/12/2022, 2:42 PM
So. As far as I know, they are both using the same underlying API calls. So it seems very unusual if you're seeing different values. And, to be honest, I'm not sure you've given me a really clear example of the difference. Take
6127
for example. Can you provide the osquery output for it, and the netstat output for it. Make sure both commands are running as root.