Hello! Does anyone know how can I parse a json file in osquery to convert it to a table if possible? Thanks in advance!

I don’t think osquery has a native table that can parse json. We ship one as part of the kolide product. You could make an extension.

Can you use ATC with the built in sqlite json functionality?

I don’t think so. ATC is really about opening sqlite db files. So if you had a db with json in it…

Oh, right. 🤦🏻‍♂️

@Rafa Bono can you be a little more specific about what you are trying to do?

Hi @Brock Walters! I was trying to parse files like

/Users/%/Library/Application Support/Firefox/Profiles/%/extension-preferences.json

I know there are tables that allow us to check Firefox extensions. But I took this as an example as there are other json files that could be useful to parse so I was wondering if there's a way to specify a json file and parse it as a table, which could be really useful. In Snowflake SQL is possible to flatten data given an input json https://docs.snowflake.com/en/sql-reference/functions/flatten and I was looking for a similar feature. Thanks!

I have had this same idea & raised it to Zach. I am going to continue to bring it up. 🙂 In the mean time, one interesting capability that already exists is the plist table. I wonder if you might have any luck converting that file to a plist with plutil & then hitting the converted file with osquery?

I’m not sure if this is something we’d bring into osquery itself. It feels wrong, but we do already have

plist

so it’s a little unclear what we should do. I’d have that conversation. This is something that exists in the Kolide product. I cannot speak for the Fleet product.

So in Kolide (not super familiar with features but I am loving the blog articles!!) there is something similar to the plist table for json files?

Yes. I wrote, and Kolide ships, a

kolide_json

and

kolide_jsonl

table. (I try not to advertise here, but… Kolide is not a drop in replacement for osquery, our focus is on ensuring devices meet various security standards before they can access SSO protected apps. Peruse https://www.kolide.com/ for background)

Love Kolide, but not using it atm... Hopefully in a near future 🤞