https://github.com/osquery/osquery logo
Title
n

Nabil Schear

08/26/2020, 3:58 PM
hi, i’d like to construct a query that uses curl_certificate on all locally listening ports. I envision a query like:
select c.serial_number from curl_certificate as c where hostname="localhost" and port in (Select port from listening_ports where path="");
but i instead need to combine “localhost” and port into one field for hostname. Is there a way of doing this in SQL without the curl_certificate table having support for a separate port field? thx
s

Seth Hanford

08/26/2020, 5:21 PM
You can use || to concatenate. I do this for files like so:
SELECT path, mtime, size, sha256, (select directory from users where directory like '/Users/%') u FROM file JOIN hash USING (path) WHERE path like u || '/tmp/%/'
 
|| 'c.txt';
n

Nabil Schear

08/26/2020, 7:39 PM
sorry trying to unpack your query to understand how it works. is this looking for files in /Users/[username]/tmp/c.txt (for all usernames?)
s

Seth Hanford

08/26/2020, 7:40 PM
It is looking for all c.txt in /Users/(all usernames)/tmp/(all subdirectories nonrecursively)/c.txt So it will find e.g. /Users/shanford/tmp/ABCD/c.txt and /Users/shanford/tmp/EFGH/c.txt
n

Nabil Schear

08/26/2020, 7:41 PM
oh i see % is. wildcard
s

Seth Hanford

08/26/2020, 7:42 PM
/%% would be recursive, so tmp/ABCD/EFGH/c.txt. In my experience, it was incredibly problematic for that query
👍 1
n

Nabil Schear

08/26/2020, 8:44 PM
ok, it ended up being this:
select c.* from curl_certificate as c where hostname in (Select distinct "localhost:"||port from listening_ports where protocol=6 and (address!="127.0.0.1" and address!="::1") ORDER BY port);