This should work correctly right ```osquery gt select distin

Question

This should work correctly right ```osquery gt select distinct lp pid p name lp port lp protocol lp family from listening ports lp cross join processes p where lp family lt gt and lp port gt 0 and lp port not in 80 443 ``` looking at getting process name for anything in `listening ports` that s not 80 or 443

fritz · Answer

This looks to be producing a cartesian product ```osquery gt select COUNT from listening ports lp cross join processes p where lp family lt gt and lp port gt 0 and lp port not in 80 443 + + | COUNT | + + | 58968 | + + osquery gt select COUNT from listening ports + + | COUNT | + + | 955 | + + osquery gt select COUNT from processes + + | COUNT | + + | 758 | + + osquery gt select COUNT from listening ports processes USING pid + + | COUNT | + + | 956 | + +```

Zach Zeid · Answer

what is a cartesian product

Zach Zeid · Answer

I assume contextually it s not good but what does it mean and how can I craft queries to avoid it

Zach Zeid · Answer

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

Zach Zeid · Answer

I see it is something similar

Zach Zeid · Answer

I thought cross join would remove this issue thinking face

Zach Zeid · Answer

This doesn t seem to get the values I want either ```osquery gt select from listening ports lp join processes using pid where lp pid = processes pid ``` As it only returns the osqueryd processes

Zach Zeid · Answer

and this seems to work better when I run `sudo osqueryi` ```osquery gt SELECT processes pid processes name address port FROM listening ports LEFT JOIN processes ON processes pid = listening ports pid WHERE address lt gt AND port lt gt 443 ```

Zach Zeid · Answer

so there doesn t seem to be a way to get the pid process name if I don t run osquery as root

Zach Zeid · Answer

Rather what is a good way to get process names that doesn t produce a cartesian effect here