https://github.com/osquery/osquery logo
Powered by
#sql
Title
# sql
z

Zach Zeid

05/14/2020, 4:55 PM
I'm running this sql from a conversation earlier in this channel
Copy code
"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
Copy code
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,?
s

sundsta

05/14/2020, 5:09 PM
Order of the joins matters. Select from users first and join on shell_history
f

fritz

05/14/2020, 5:21 PM
You can force this behavior by utilizing a 
CROSS JOIN
s

sundsta

05/14/2020, 5:22 PM
@fritz Good to know. Is there any performance penalty for 
CROSS JOIN
?
f

fritz

05/14/2020, 5:27 PM
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.
z

zwass

05/14/2020, 5:28 PM
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.
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.
Now with the above query the joins will have to be reordered so that 
users
is the first table.
f

fritz

05/14/2020, 5:41 PM
TIL regarding limiting the 
JOIN
. Thanks for the SQL lesson @zwass!
z

Zach Zeid

05/15/2020, 12:57 PM
Thanks for the input, I redid the query (minus a few values for readability, and I think this works?
Copy code
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 || "%");
or should I be using 
CROSS JOIN
in this case?
f

fritz

05/15/2020, 2:36 PM
@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.
I love the usage of cte's to do the 
LIKE
/ 
IN
combo
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:
Copy code
osquery> 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 || "%");

>>>

Run Time: real 0.570 user 0.418604 sys 0.049183
Copy code
osquery> SELECT u.username, u.uid, sh.command FROM users u CROSS JOIN shell_history sh USING (uid)
    ...> WHERE command LIKE '%curl%';

>>>

Run Time: real 0.557 user 0.411912 sys 0.046748
z

Zach Zeid

05/15/2020, 2:40 PM
how are you getting that 
Run Time:
?
f

fritz

05/15/2020, 2:41 PM
When you are in osqueryi prompt type: 
.timer ON
Copy code
osquery> .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
z

zwass

05/15/2020, 3:05 PM
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.
f

fritz

05/15/2020, 3:08 PM
☝️ @Zach Zeid
z

Zach Zeid

05/15/2020, 3:10 PM
I see, that makes sense, so like this?
Copy code
osquery> 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);
z

zwass

05/15/2020, 3:11 PM
Yes!
I wrote up a little blog post explaining this: https://dactiv.llc/blog/osquery-join-users-table/
👍 2
z

Zach Zeid

05/20/2020, 12:31 PM
This was incredibly easy to digest, thank you for writing it up!
7 Views
Powered by