Does json_extract work with queries? I am trying to pull Vscode extension and each extension directory has a package.json file that contains metadata about each extension. so i was trying to pull a few keys out of it to create a list. what i have so far but get a

*Compatible with:Estimated compatiblity based onthe tables used in the query.*No platforms (check your query for a possible syntax error)

SELECT
    path
    json_extract(path, '$.displayName') as displayName,
  FROM
    file
  WHERE
    path LIKE '%/%/.vscode/extensions/%/package.json'
    AND type = 'regular';

I looks like SQLite's

json_extract()

would need the actual json data given as its first argument, not a path to a file that contains the json. https://www.sqlite.org/json1.html#jex

ah okay thank you. let me give that a try

AFAICS osquery core doesn't have a table to load file contents. Some ideas: 1. You could use File carving. 2. Use Orbit which comes with the

file_lines

table (though it might be tricky to get the data given it's splitted line by line).

ty @Lucas Rodriguez, that was another idea I had or making an extension to grab this data. would be a nice table to have as well since some extensions are open source and not always too sure what it does in the background

I tried playing around with

augeas

, but I'm not having any luck with the built-in lenses.

An extension makes sense. Also here are the Fleet extension tables for osquery (packaged with Orbit): https://github.com/fleetdm/fleet/tree/main/orbit/pkg/table

same @Kathy Satterlee lol

If adding the

file_lines

table is an option for you, this query worked nicely for me on Mac:

SELECT path, split(line, ":", 1) as displayName FROM file_lines WHERE path LIKE "/Users/%%/.vscode/extensions/%/package.json" AND line LIKE '%"displayName"%' ;

Kathy's guide to useful extensions when?

Lol.

@Arsenio If you are using Kolide's launcher extension you can also use the

kolide_json

table to query json formatted files.

In case it's useful, I recently created an extension for this https://github.com/nachorpaez/osquery-vscode-extension