SELECT * FROM file WHERE filename LIKE "%example%"

does not return any results

Hey @R0n it looks like you probably already read the Spotlight article (based on your reference of the "file table" blog artlce), but in case you haven't, it discusses how to search for files on macOS using mdfind as Zach suggested: https://blog.kolide.com/spotlight-search-across-every-mac-in-your-fleet-with-osquery-55789c765986

SELECT f.path FROM file AS f
JOIN mdfind ON mdfind.path = f.path
AND mdfind.query = "kMDItemFSName == '*backup*'"

this would work for Mac, i was looking into windows hosts. I had to be more specific to get the file list from the folder i specified in the query.