https://github.com/osquery/osquery logo
Title
a

Arsenio

04/19/2023, 3:28 PM
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';
j

John Speno

04/19/2023, 3:34 PM
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
a

Arsenio

04/19/2023, 3:35 PM
ah okay thank you. let me give that a try
l

Lucas Rodriguez

04/19/2023, 5:00 PM
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).
a

Arsenio

04/19/2023, 5:03 PM
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
k

Kathy Satterlee

04/19/2023, 5:07 PM
I tried playing around with
augeas
, but I'm not having any luck with the built-in lenses.
l

Lucas Rodriguez

04/19/2023, 5:07 PM
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
a

Arsenio

04/19/2023, 5:08 PM
same @Kathy Satterlee lol
k

Kathy Satterlee

04/19/2023, 5:28 PM
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"%' ;
image.png
Note to self: take a few minutes to install some useful extensions.
j

John Speno

04/19/2023, 5:39 PM
Kathy's guide to useful extensions when?
k

Kathy Satterlee

04/19/2023, 5:49 PM
Lol.
f

fritz

04/19/2023, 7:59 PM
@Arsenio If you are using Kolide's launcher extension you can also use the
kolide_json
table to query json formatted files.
i

Ignacio

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