osquery #sql

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?

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

🎊 2

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?

fritz

01/22/2019, 5:17 PM

Copy code

SELECT * from system_info
WHERE 
    NOT EXISTS (SELECT *
        FROM processes
        WHERE  name LIKE "%auditd%");

R0n

01/23/2019, 7:11 PM

SELECT * FROM file WHERE filename LIKE "%example%"

does not return any results

fritz

01/24/2019, 7:21 PM

@R0n I wonder if you could create an augeas lens to parse:

Copy code

%userprofile%\AppData\Roaming\Microsoft\Windows\PowerShell\PSReadline\ConsoleHost_history.txt

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.

R0n

01/10/2020, 8:00 PM

does anyone here have a query how to detect possible malware for crypto-mining?

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?

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,?

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?

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 🙂

🍻 1

Zach Zeid

05/29/2020, 6:56 PM

has anyone dealt with collecting listening_ports on machines with a large number of connections?

Zach Zeid

06/02/2020, 2:54 PM

This should work correctly right?

Copy code

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?

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)

Julian Scala

10/15/2020, 8:29 PM

Any example of a query to get the filevault service status on MacOS?

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

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)

02/23/2021, 7:33 PM

does anyone have a query to pull the mac address of the default network interface for windows + mac

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)

Copy code

SELECT * FROM registry WHERE path LIKE 'HKEY_USERS\%\Software\Microsoft\Office\%'

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?

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

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?

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?

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? 🙂

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

05/30/2022, 2:29 PM

I’m using the

ROUND

statement in some queries where I need to calculate the percentage. For example

Copy code

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.

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?

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

Copy code

select hostname from system_info; 
select address from interface_addresses;
select version from kernel_info;

sorry for the newb question

Ronald Cardoso

11/07/2023, 3:25 PM

@Ronald Cardoso has left the channel