Title
#sql
z

Zach Zeid

06/02/2020, 2:54 PM
This should work correctly right?
osquery> select distinct lp.pid, p.name, lp.port, lp.protocol, lp.family from listening_ports lp cross join processes p where lp.family <> '' and lp.port > 0 and lp.port not in ("80", "443");
looking at getting process name for anything in
listening_ports
that's not 80 or 443?
f

fritz

06/02/2020, 3:12 PM
This looks to be producing a cartesian product:
osquery> select COUNT(*) from listening_ports lp cross join processes p where lp.family <> '' and lp.port > 0 and lp.port not in ("80", "443");
+----------+
| COUNT(*) |
+----------+
| 58968    |
+----------+

osquery> select COUNT(*) from listening_ports;
+----------+
| COUNT(*) |
+----------+
| 955      |
+----------+

osquery> select COUNT(*) from processes;
+----------+
| COUNT(*) |
+----------+
| 758      |
+----------+

osquery> select COUNT(*) from listening_ports, processes USING (pid);
+----------+
| COUNT(*) |
+----------+
| 956      |
+----------+
z

Zach Zeid

06/02/2020, 3:14 PM
what is a cartesian product?
3:14 PM
I assume, contextually it's not good, but what does it mean and how can I craft queries to avoid it?
3:15 PM
is it sort of like a matrix, where there are so many different combinations, that osquery will try to account for them all in a given query?
3:25 PM
I see, it is something similar.
3:25 PM
I thought cross join would remove this issue 🤔
3:33 PM
This doesn't seem to get the values I want either
osquery> select * from listening_ports lp join processes using (pid) where lp.pid = processes.pid;
As it only returns the osqueryd processes.
4:06 PM
and this seems to work better when I run
sudo osqueryi
osquery> SELECT processes.pid, processes.name, address, port FROM listening_ports LEFT JOIN processes ON processes.pid = listening_ports.pid WHERE address <> '' AND port <> 443;
4:15 PM
so there doesn't seem to be a way to get the pid, process name if I don't run osquery as root?
4:56 PM
Rather, what is a good way to get process names that doesn't produce a cartesian effect here?