"Looping" through list in Live Query

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

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?

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

Not Found (#404)

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

Hello Kyle Parrish, 
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?

Interesting JeramyKopacko. 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!

Hi Kyle Parrish - 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. 

Hi Kyle Parrish - 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. 

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

Not Found (#404)

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

Hello Kyle Parrish, 
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?

Interesting JeramyKopacko. 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!