does osquery support declaring variables that are the result of a select statement, and then using that variable in another select statement?

like a subquery?

SELECT pid, name FROM processes WHERE pid IN (SELECT parent FROM processes WHERE name = 'docker');

Not with variables directly, but you can use sub selects. E.g.

osquery> select directory, mode from file where path = (select "/etc/passwd");
+-----------+------+
| directory | mode |
+-----------+------+
| /etc      | 0644 |
+-----------+------+

There's also: https://www.sqlite.org/lang_with.html

Yeah, CTEs are commonly used for this too. Good point.

hmm, looking into it. The use case is parsing /Users/%%/Library/Preferences/MobileMeAccounts.plist which has a funny data structure

if you can provide an example query it will make it more likely to get assistance.

yup, working on it 🙂

you can leverage something like regex or str replace, without knowing what shape of data you are looking for it is hard to know the right solution

so

SELECT sub.*
FROM (select subkey,value FROM plist path like "/Users/%%/Library/Preferences/MobileMeAccounts.plist") sub
WHERE subkey like 'Service/1/%'

will actually get me different services on each machine, because apple just numbers them numerically or something

in your sub query you are missing "WHERE" between plist and path fwiw

yeah, i was copying by hand from work computer to home computer for purposes of posting in slack

syntactically this should be equivilant

WITH blah AS (SELECT subkey,value FROM plist WHERE path LIKE "/Users/%/Library/Preferences/MobileMeAccounts.plist") SELECT * FROM blah WHERE blah.subkey LIKE "/Service/1/%";

ok so:

WITH service_number AS (SELECT sub.*
FROM (select subkey,value FROM plist WHERE path LIKE "/Users/%/Library/Preferences/MobileMeAccounts.plist" sub
where value="MOBILE_DOCUMENTS" select subkey from service_number;

what are you trying to actually get here? is it an issue that it looks to be an array? or you only want it to return "Name" ?

I am so sorry, i think i am just missing the glue where the result of that query gets shoved into:

SELECT sub.*
FROM (select subkey,value FROM plist where path like "/Users/%/Library/Preferences/MobileMeAccounts.plist") sub
where subkey like "{result of that first search}"

if it is the latter issue, you could do something like SPLIT(result, "/",2) or whatever that syntax is

(i'll figure out chopping /Name off)

where the result gets shoved into is completely unique to your environment so how it is consumed i don't know, in my case, we stream all of our results to a pubsup topic and everything is parsed and plopped into a data lake so can only speak to that

in english its "figure out on this computer which service number MOBILE_DOCUMENTS has, then give me all the keys related to the Service number for MOBILE_DOCUMENTS"

you still most likely want to use the WITH pattern, this creates a virtual table that you can subsequently work off of. so create your virtual table as above and then build your where constraint accordingly

yeah, I think i am just having trouble visualizing this. Thank you so much for helping me so far :)

SELECT SPLIT(subkey, "/", 1) FROM plist WHERE path LIKE '/Users/%/Library/Preferences/MobileMeAccounts.plist' AND value="MOBILE_DOCUMENTS";

Should return you a table with all the service numbers, you can put that in a CTE

yeah, i apologize for being kinda dense here, i think i've figured out a few ways to pull out the thing i need, its shoving it into a new query that I am just derping on

SELECT * FROM plist WHERE path LIKE '/Users/%/Library/Preferences/MobileMeAccounts.plist' AND subkey LIKE CONCAT('%/', (SELECT SPLIT(subkey, "/", 1) FROM plist WHERE path LIKE '/Users/%/Library/Preferences/MobileMeAccounts.plist' AND value="MOBILE_DOCUMENTS"), '/%');

This is with a subquery

Oh ok, thats basically it, i just threw in parenthesis and OR subkey="AccountID" to grab the icloud account being used

For that table, and similar EAV style structured data, the usual recommendation is to pivot it. (The kolide blog has a good write up)

Yup! I discovered that after I accidentally returned 34523452296251 results