Channels
  • z

    Zach Zeid

    2 years ago
    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

    sundsta

    2 years ago
    Order of the joins matters. Select from users first and join on shell_history
  • f

    fritz

    2 years ago
    You can force this behavior by utilizing a
    CROSS JOIN

    https://blog.kolide.com/running-osquery-as-sudo-root-vs-user-4fcfc698c45e#7681
  • sundsta

    sundsta

    2 years ago
    @fritz Good to know. Is there any performance penalty for
    CROSS JOIN
    ?
  • f

    fritz

    2 years ago
    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

    zwass

    2 years ago
    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

    2 years ago
    TIL regarding limiting the
    JOIN
    . Thanks for the SQL lesson @zwass!
  • z

    Zach Zeid

    2 years ago
    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 || "%");

    or should I be using
    CROSS JOIN
    in this case?
  • f

    fritz

    2 years ago
    @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:
    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

    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

    2 years ago
    how are you getting that
    Run Time:
    ?
  • f

    fritz

    2 years ago
    When you are in osqueryi prompt type:
    .timer ON

    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
  • zwass

    zwass

    2 years ago
    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

    2 years ago
    @Zach Zeid
  • z

    Zach Zeid

    2 years ago
    I see, that makes sense, so like this?
    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);
  • zwass

    zwass

    2 years ago
    Yes!
    I wrote up a little blog post explaining this: https://dactiv.llc/blog/osquery-join-users-table/