Browse Live Discover and Response Queries by Category
Complete

NOTE: SQLite does not support looping, instead consider using WITH RECURSIVE.

"Looping" through list in Live Query

Good afternoon!

I am working on a query where I would like to essentially perform a "For Each Loop" on the results.

I am not sure what the SQL equivalent is.

Can someone assist me and/or direct me to the proper syntax?

Based on the following query, I would like to "grep" each of the file paths for a string.

SELECT path, directory, filename FROM file WHERE directory LIKE 'C:\users\%\AppData\Local\Google\Chrome\User Data\Default%%' AND filename IS 'Preferences'

Example grep query to be run on each discovered file path:

SELECT line FROM grep WHERE pattern = 'test' AND path = 'C:\users\bobjoe\AppData\Local\Google\Chrome\User Data\Default\Preferences'

Thanks!

Kyle Parrish
Parents

  • Hi Kyle,

    You could create this by applying multiple virtual tables in the pathing locations and then use a union join across the tables. 

    EX: 

    WITH table_name AS (SELECT line FROM grep WHERE pattern = 'test' AND path = 'C:\users\bobjoe\AppData\Local\Google\Chrome\User Data\Default\Preferences')

    My colleague wrote an example using virtual tables that will help you here: https://community.sophos.com/intercept-x-endpoint/i/query-forum/load-a-local-csv-file-or-remote-csv-file-as-a-virtual-table

    Does that make sense? Happy to assist in the query if you need it. Just let me know what paths you're searching on.

    -Jeramy

  • in reply to JeramyKopacko

    Hey @jeramy_kopacko for the feedback. That makes sense. So I think I have two options at least to do what I am looking for. I was able to get this started using the "WHERE path in (select..." method. Now, I am stuck on the main part, extracting the JSON I am interested in.

    Here is what I have so far (does not work due to apparent malformed JSON. Not sure why...):

    SELECT json_extract(line,'$.account_info.email') FROM grep WHERE pattern = "{" AND path in (select path from file where directory LIKE 'C:\users\%\AppData\Local\Google\Chrome\User Data\Default%%' AND filename IS 'Preferences')

    I can confirm that the grep data is being populated if I remove the json_extract(...

    Are you aware of any oddities with the Google Chrome preferences file?

  • in reply to RaviSoni

    Well that is rude lol. It really is just a stock/default file. Nothing modified for the profile.

  • in reply to RaviSoni

    Sure thing. I just tested again and I can use the query you posted and return results based on the preferences file below. Pretty much stock with no customizations...

    https://pastebin.com/jGmwuZ02

  • in reply to Kyle Parrish

    Hello , 
    There is a limitation for the grep table to read the characters per line i.e 1k chars/line. If the limit exceeds it returns no data. I could see the preference file has more than 10k chars in a single line(may differ from machine to machine).

    This query didn't return any data for me.
    SELECT line from grep where pattern = '{' and path in (select path from file where path LIKE 'C:\users\%\AppData\Local\Google\Chrome\User Data\Default%%' AND filename IS 'Preferences')

    However, I see you are able to get the data for the same, Could you share your preference file(to check the JSON format) or just the snippet of it?

  • in reply to JeramyKopacko

    Interesting . I was able to return data with grep. Here is a sample:

    SELECT split(replace(line, '"',''),' ',1) AS email from grep where pattern = 'email":' and path in (select path from file where path LIKE 'C:\users\%\AppData\Local\Google\Chrome\User Data\Default%%' AND filename IS 'Preferences')

    As well as:

    SELECT line from grep where pattern = '{' and path in (select path from file where path LIKE 'C:\users\%\AppData\Local\Google\Chrome\User Data\Default%%' AND filename IS 'Preferences')

    Now, without querying JSON direct, this matches "any" line that contains email. So, the result returned in this test was actually not the field i was looking for. 

    I am curious why this file seems to be a pain to interact with though.

    Thanks for taking a look!

  • in reply to Kyle Parrish

    Hi - I was able to return the path using (SELECT path...) but when I combined it with the grep statement (without JSON), it returned nothing. I'm struggling to get the JSON file parsed. It's ~48k characters and I'm trying to determine if Osquery has a max count that is stopping us. I'm reviewing more. I even tried to use ATTACH DATABASE to load it since its comma delineated. 

Comment
  • in reply to Kyle Parrish

    Hi - I was able to return the path using (SELECT path...) but when I combined it with the grep statement (without JSON), it returned nothing. I'm struggling to get the JSON file parsed. It's ~48k characters and I'm trying to determine if Osquery has a max count that is stopping us. I'm reviewing more. I even tried to use ATTACH DATABASE to load it since its comma delineated. 

Children
  • in reply to RaviSoni

    Well that is rude lol. It really is just a stock/default file. Nothing modified for the profile.

  • in reply to RaviSoni

    Sure thing. I just tested again and I can use the query you posted and return results based on the preferences file below. Pretty much stock with no customizations...

    https://pastebin.com/jGmwuZ02

  • in reply to Kyle Parrish

    Hello , 
    There is a limitation for the grep table to read the characters per line i.e 1k chars/line. If the limit exceeds it returns no data. I could see the preference file has more than 10k chars in a single line(may differ from machine to machine).

    This query didn't return any data for me.
    SELECT line from grep where pattern = '{' and path in (select path from file where path LIKE 'C:\users\%\AppData\Local\Google\Chrome\User Data\Default%%' AND filename IS 'Preferences')

    However, I see you are able to get the data for the same, Could you share your preference file(to check the JSON format) or just the snippet of it?

  • in reply to JeramyKopacko

    Interesting . I was able to return data with grep. Here is a sample:

    SELECT split(replace(line, '"',''),' ',1) AS email from grep where pattern = 'email":' and path in (select path from file where path LIKE 'C:\users\%\AppData\Local\Google\Chrome\User Data\Default%%' AND filename IS 'Preferences')

    As well as:

    SELECT line from grep where pattern = '{' and path in (select path from file where path LIKE 'C:\users\%\AppData\Local\Google\Chrome\User Data\Default%%' AND filename IS 'Preferences')

    Now, without querying JSON direct, this matches "any" line that contains email. So, the result returned in this test was actually not the field i was looking for. 

    I am curious why this file seems to be a pain to interact with though.

    Thanks for taking a look!

Unfiltered HTML