This should work correctly right?
```osquery> s...
# sqlz
Zach Zeid
06/02/2020, 2:54 PMThis should work correctly right?
Copy code
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");listening_portsf
fritz
06/02/2020, 3:12 PMThis looks to be producing a cartesian product:
Copy code
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 PMwhat is a cartesian product?
Zach Zeid
06/02/2020, 3:14 PMI assume, contextually it's not good, but what does it mean and how can I craft queries to avoid it?
Zach Zeid
06/02/2020, 3:15 PMis 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?
Zach Zeid
06/02/2020, 3:25 PMI see, it is something similar.
Zach Zeid
06/02/2020, 3:25 PMI thought cross join would remove this issue 🤔
Zach Zeid
06/02/2020, 3:33 PMThis doesn't seem to get the values I want either
Copy code
osquery> select * from listening_ports lp join processes using (pid) where lp.pid = processes.pid;Zach Zeid
06/02/2020, 4:06 PMand this seems to work better when I run
sudo osqueryi Copy code
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;Zach Zeid
06/02/2020, 4:15 PMso there doesn't seem to be a way to get the pid, process name if I don't run osquery as root?
Zach Zeid
06/02/2020, 4:56 PMRather, what is a good way to get process names that doesn't produce a cartesian effect here?
28 Views