I m running this sql from a conversation earlier in this cha

Question

I m running this sql from a conversation earlier in this channel ``` WITH forbidden commands cmd AS SELECT FROM values rsync ngrok curl scp nc SELECT username uid command FROM shell history JOIN forbidden commands on shell history command LIKE || forbidden commands cmd || JOIN users USING uid ``` but I m getting this error ```W0514 12 24 23 284471 17017 virtual table cpp 959 The shell history table returns data based on the current user by default consider JOINing against the users table``` It looks like it is joining against the users table

sundsta · Answer

Order of the joins matters Select from users first and join on shell history

fritz · Answer

You can force this behavior by utilizing a `CROSS JOIN`

fritz · Answer

sundsta · Answer

< fritz> Good to know Is there any performance penalty for `CROSS JOIN`

fritz · Answer

I believe in general `CROSS JOIN` s are treated with trepidation because they can be used to produce cartesian products in other SQL contexts I am unsure of the cartesian pitfall potential within osquery Maybe < zwass> would be able to shed some light

zwass · Answer

This is a misconception `CROSS JOIN` just like `JOIN` will create a cartesian product if you don t appropriately limit the join with `ON` `USING` or the `WHERE` clause

zwass · Answer

The only difference with `CROSS JOIN` is that it prevents the optimizer from reordering the query This is a desired effect in cases in which the order of the joins matter like these tables that require a join to users In these cases `CROSS JOIN` should always be used

zwass · Answer

Now with the above query the joins will have to be reordered so that `users` is the first table

fritz · Answer

TIL regarding limiting the `JOIN` Thanks for the SQL lesson < zwass>

Zach Zeid · Answer

Thanks for the input I redid the query minus a few values for readability and I think this works ```with forbidden commands cmd as select from values curl select username uid shell history command from users join shell history using uid join forbidden commands on shell history command like || forbidden commands cmd || ```

Zach Zeid · Answer

or should I be using `CROSS JOIN` in this case

fritz · Answer

< Zach Zeid> That appears to work for me as well The reason is likely because you are calling the `username` column from the `users` table before shell history is being polled

fritz · Answer

I love the usage of cte s to do the `LIKE` `IN` combo

fritz · Answer

The other good news is that it appears to be no slower than doing a straight `LIKE` when there is only one variable at play ```osquery gt with forbidden commands cmd AS gt select from values curl gt select username gt uid gt shell history command gt FROM users gt JOIN shell history USING uid gt JOIN forbidden commands ON shell history command LIKE || forbidden commands cmd || gt gt gt Run Time real 0 570 user 0 418604 sys 0 049183```

fritz · Answer

```osquery gt SELECT u username u uid sh command FROM users u CROSS JOIN shell history sh USING uid gt WHERE command LIKE curl gt gt gt Run Time real 0 557 user 0 411912 sys 0 046748```

Zach Zeid · Answer

how are you getting that `Run Time `

fritz · Answer

When you are in osqueryi prompt type ` timer ON`

fritz · Answer

```osquery gt help Welcome to the osquery shell Please explore your OS You are connected to a transient in memory virtual database all TABLE Select all from a table bail ON|OFF Stop after hitting an error echo ON|OFF Turn command echo on or off exit Exit this program features List osquery s features and their statuses headers ON|OFF Turn display of headers on or off help Show this message mode MODE Set output mode where MODE is one of csv Comma separated values column Left aligned columns see width line One value per line list Values delimited by separator string pretty Pretty printed SQL results default nullvalue STR Use STRING in place of NULL values print STR Print literal STRING quit Exit this program schema TABLE Show the CREATE statements separator STR Change separator used by output mode socket Show the osquery extensions socket path show Show the current values for various settings summary Alias for the show meta command tables TABLE List names of tables types SQL Show result of getQueryColumns for the given query width NUM1 + Set column widths for column mode timer ON|OFF Turn the CPU timer measurement on or off```

zwass · Answer

I would advise using CROSS JOIN otherwise you are at the risk of the optimizer reordering it and no longer having the users table called first

fritz · Answer

point up < Zach Zeid>

Zach Zeid · Answer

I see that makes sense so like this ```osquery gt WITH forbidden commands cmd AS SELECT FROM values rsync ngrok curl scp nc SELECT username uid command FROM shell history CROSS JOIN forbidden commands on shell history command LIKE || forbidden commands cmd || JOIN users USING uid ```

zwass · Answer

Yes

zwass · Answer

I wrote up a little blog post explaining this <https dactiv llc blog osquery join users table >

Zach Zeid · Answer

This was incredibly easy to digest thank you for writing it up