"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)

Not Found (#404)

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