sql
  • m

    Mustafa

    08/01/2018, 2:40 PM
    … “/home/%/.ssh/%%” there is only one % char in middle… in this case, what does that mean? it must be only one directory between .ssh directory and home directory ?
  • s

    shadejinx

    08/01/2018, 4:23 PM
    for example, if I throw a random file in a SIP protected directory, is it now protected by SIP?
  • a

    alessandrogario

    08/01/2018, 5:07 PM
    I have no idea why, but I initially read "seismic integrity protection" and that image looked like a map
  • y

    yuvalapidot

    08/16/2018, 7:35 AM
    I have just tried the following query (works for mac only):
    SELECT MAX(asl.time) boot_time FROM asl WHERE asl.sender = 'bootlog';
    This will select the latest boot time from the Apple System Log data structure. Any thoughts on this query? will it always work?
  • f

    fritz

    01/22/2019, 5:17 PM
    SELECT * from system_info
    WHERE 
        NOT EXISTS (SELECT *
            FROM processes
            WHERE  name LIKE "%auditd%");
  • r

    R0n

    01/23/2019, 7:11 PM
    SELECT * FROM file WHERE filename LIKE "%example%"
    does not return any results
  • f

    fritz

    01/24/2019, 7:21 PM
    @R0n I wonder if you could create an augeas lens to parse:
    %userprofile%\AppData\Roaming\Microsoft\Windows\PowerShell\PSReadline\ConsoleHost_history.txt
  • 8p8c

    8p8c

    02/25/2019, 10:53 PM
    has anybody come upon the need for a row generation function that splits a string by a delimiter and returns the pieces as separate rows so they could be easily used with the IN clause ? there doesn't exist one in sqlite but may be an useful addition like the current
    split
    is.
  • r

    R0n

    01/10/2020, 8:00 PM
    does anyone here have a query how to detect possible malware for crypto-mining?
  • c

    Chris Benninger

    05/13/2020, 3:29 PM
    Does anyone know if there is a way to have “CREATE TABLE …” stuff persist in the DB?
  • z

    Zach Zeid

    05/14/2020, 4:55 PM
    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,?
  • z

    Zach Zeid

    05/15/2020, 5:32 PM
    If I'm doing
    select * from shell_history
    on a schedule, does that read the whole
    .bash_history
    every time, or does it diff it in some way?
  • f

    fritz

    05/28/2020, 1:25 PM
    @stefanmaerz sorry I missed this, but yes, you can infix single wildcards, which so long as you have a predictable nesting structure (which it appears you might) you should have no problem! Glad you were able to make it work 🙂
  • z

    Zach Zeid

    05/29/2020, 6:56 PM
    has anyone dealt with collecting listening_ports on machines with a large number of connections?
  • z

    Zach Zeid

    06/02/2020, 2:54 PM
    This should work correctly right?
    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");
    looking at getting process name for anything in
    listening_ports
    that's not 80 or 443?
  • l

    lvferdi

    07/24/2020, 11:35 AM
    I can share. I could make it shorter by using like statements but I actually wanted to be very specific. The query itself has 4490 characters (including white space)
  • j

    Julian Scala

    10/15/2020, 8:29 PM
    Any example of a query to get the filevault service status on MacOS?
  • f

    fritz

    11/02/2020, 3:16 PM
    Your SQL's intent is not immediately clear to me. Can you describe the output you are trying to get? Do you want to return results of only
    rpm_packages
    where an
    install_time
    exists?
  • zwass

    zwass

    02/11/2021, 11:10 PM
    I do not think it is possible from a technical perspective to dynamically create the columns based on the data returned by the query. It's just not supported by sqlite (and probably SQL standard?). It should be possible to use recursive queries to generate something with a schema like
    (key, value)
    .
  • w

    WS

    02/23/2021, 7:33 PM
    does anyone have a query to pull the mac address of the default network interface for windows + mac
  • f

    fritz

    04/12/2021, 3:35 PM
    I haven't verified your path but it would be something like: (sorry reposted because I left out a wildcard)
    SELECT * FROM registry WHERE path LIKE 'HKEY_USERS\%\Software\Microsoft\Office\%'
  • d

    Divya

    06/15/2021, 8:10 AM
    Hi, I am enabling osquery on a linux system, and Iooking for logs on mount and umount of file systems. I see those logs on /var/log/messages file but when I check syslog_events table I dont get these logs. Did anyone face a similar issue?
  • w

    Will Sheldon

    10/27/2021, 8:57 PM
    Say I wanted to write a detection for "one user logged into 10k hosts at one time over SSH", how would I do that in OSQuery? https://osquery.io/schema/5.0.1/#logged_in_users https://osquery.io/schema/5.0.1/#users
  • w

    Wes

    11/10/2021, 7:34 PM
    Hey all, I'm diving in to the implementation details of SQL in osquery to solve #7314. I see there's both some mention of sqlite, as well as
    list<map<string,string>>
    data structures that represent the tables independently of sqlite. In particular, I'm looking into how
    JOIN
    works. Any pointers would be much appreciated! Is there a primer on how tables work under the hood?
  • d

    Divya

    01/04/2022, 1:29 PM
    Hi, Is there a query I can use to list all the externally open sockets along with processes using them?
  • j

    Jon Semon

    03/09/2022, 1:30 AM
    Hey all, not sure if this is possible but figured this was a good place to ask.. Looking to create a query to view the contents of a .zip file that has no password.. anyone heard of something like this? 🙂
  • c

    Chris Delaney

    04/12/2022, 7:50 PM
    Hola, folks! I was curious if it is currently possible to query TCC statuses on macOS devices. Any insight on this is greatly appreciated 🍻
  • Andreas Piening

    Andreas Piening

    05/30/2022, 2:29 PM
    I’m using the
    ROUND
    statement in some queries where I need to calculate the percentage. For example
    SELECT path, type, ROUND((blocks_available * blocks_size * 10e-10), 2) AS free_gb, ROUND ((blocks_available * 1.0 / blocks * 1.0) * 100, 2) AS free_perc FROM mounts WHERE path = '/';
    I expect the percentage to have
    two digits
    after the decimal point, but instead I get values like
    51.020000000000003
    . Can anyone tell why this is or how I can fix this? These long numbers are hard to read for percentage values.
  • s

    seph

    05/31/2022, 2:07 PM
    I would expect that to work, it does on my machine. Where are you seeing those numbers? could your data pipeline be adding them? Is this osqueryi?
  • t

    TimBo

    06/09/2022, 8:46 PM
    Hello - I'm completely new to SQL and OSquery - I'm confused how I join 3 disparate tables if there's no key to join/pivot off of? for example these 3
    select hostname from system_info; 
    select address from interface_addresses;
    select version from kernel_info;
    sorry for the newb question