This discussion has been locked.

You can no longer post new replies to this discussion. If you have a question you can start a new discussion

"Looping" through list in Live Query

Kyle Parrish over 3 years ago

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!

This thread was automatically locked due to age.

Top Replies

GlennSen over 3 years ago in reply to GlennSen +1 suggested

Hello Kyle Parrish , I would like to ask your assistance if you could also create the same post on this forum https://community.sophos.com/intercept-x-endpoint/i/query-forum In it would be this forum…

Parents

0 Sophos User930 over 3 years ago

Does this example help:

select json_extract(line,'$.timeStamp')as TimeStamp,json_extract(line,'$.serviceName') as ServiceName,json_extract(line,'$.resourceId')as ResourceID from grep where pattern ="{" and path in (select path from file where directory = 'C:\ProgramData\Sophos\Health\Event Store\Trail')

This pulls out a few values from the json files in a directory.
Cancel
Vote Up 0 Vote Down

Cancel
0 Kyle Parrish over 3 years ago in reply to Sophos User930

This helps very much and works as is. However, when I try to apply it to my use case I get nothing. This is what I have so far:

select json_extract(line,'$.account_info.email') as state, path 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')
Cancel
Vote Up 0 Vote Down

Cancel
0 Kyle Parrish over 3 years ago in reply to Kyle Parrish

I keep getting:

finished – errors – malformed JSON

Anything odd about the Chrome Preferences JSON?
Cancel
Vote Up 0 Vote Down

Cancel
0 Sophos User930 over 3 years ago in reply to Kyle Parrish

I carried out some testing, it seems there is a limit of 1000 characters per line. That file is one great big long line
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 Sophos User930 over 3 years ago in reply to Kyle Parrish

I carried out some testing, it seems there is a limit of 1000 characters per line. That file is one great big long line
Cancel
Vote Up 0 Vote Down

Cancel

Children

0 Kyle Parrish over 3 years ago in reply to Sophos User930

Well that's a bummer! I would like to request an increase please! lol

Is this a limit for json_extract only? I am able to use grep in general to pull data...
Cancel
Vote Up 0 Vote Down

Cancel
0 Sophos User930 over 3 years ago in reply to Kyle Parrish

It seems to be in grep. If you create a couple of text files:
C:\1000.txt
C:\1001.txt
Add 1000 and 1001 'a' characters on the first line as per the name suggests.
Then run the query:

SELECT * FROM grep WHERE path = C:\1000.txt and pattern ="a"
SELECT * FROM grep WHERE path = C:\1001.txt and pattern ="a"
Only the 1000 query will return a result for me.
Cancel
Vote Up 0 Vote Down

Cancel
0 Kyle Parrish over 3 years ago in reply to Sophos User930

Interesting. The file I am working has a ton of lines but looks like the one with the highest amount of characters is only 470.
Cancel
Vote Up 0 Vote Down

Cancel