Title
#general
t

Thomas Stromberg

10/12/2022, 7:22 PM
Is there a way to run all queries in a JSON pack file via stdin or arbitrary path? I'm looking for the spiritual equivalent of
echo query | osqueryi
today. I found https://github.com/osquery/osquery/pull/2093 which added
--pack
, but it appears to only run ones that are already defined as part of your config. As a workaround I was considering dynamically generating a JSON config file and passing it in via
--config_path
, but it seems pretty hacky & the sort of thing that someone probably found a better solution for.
7:24 PM
I've been working on a packer program to generate JSON pack files from a series of arbitrary SQL statements, but would like to make sure that the resulting output is run through a validation stage.
7:34 PM
As a temporary workaround, I was reading the JSON in myself and running the queries by hand, but I've discovered that osquery's JSON deserialization diverges a bit from others (Python, Go), particularly with how escaping is handled.
s

seph

10/12/2022, 9:24 PM
I’m not sure anything exists that you didn’t already find.
9:24 PM
I think you could reasonable do the hacky thing and generate a config. But 🤷
t

Thomas Stromberg

10/12/2022, 9:26 PM
Thanks for the confirmation! I was able to use it and conclude that Kolide's frontend ingester is the source of JSON deserialization divergence 😞 I'm going to work on open-sourcing this tool & generating a reduced test case to open a bug w/ support.
s

seph

10/12/2022, 9:37 PM
Which kolide front end ingester? (I’m heading off for the day, but that sounds odd)
t

Thomas Stromberg

10/13/2022, 12:44 PM
FWIW, I ended up reporting it via support, but the main problem appears to be how Kolide's web frontend handles importing packs that include queries with double quotes in them. Here's a reduced example pack:
{
  "queries": {
    "waldo": {
      "query": "SELECT * FROM chrome_extensions WHERE name IN (\"Where's waldo?\", \"Google Docs Offline\");",
      "interval": "3600"
    }
  }
}
s

seph

10/13/2022, 12:59 PM
Reporting through support is :thumbsup_all:
12:59 PM
Might be worth trying single quotes inside — SQL prefers single quotes, though osquery handles either. But it’s an easy way to handled nested quotes
1:00 PM
Ah, I see you mentioned that
t

Thomas Stromberg

10/13/2022, 1:01 PM
I tried, but wasn't able to figure out how to handle queries referencing fields with single quotes in them. I know there's got to be a way to escape them, but I failed. I'll look into it again 😞
s

seph

10/13/2022, 1:01 PM
Nesting quotes is such a hassle 😕
1:03 PM
Reading the internet, and testing at an interactive prompt. Doubled quotes works. eg:
osquery> select 'hello''s';
+------------+
| 'hello''s' |
+------------+
| hello's    |
+------------+
t

Thomas Stromberg

10/13/2022, 1:06 PM
Hmm, that's true - I did do that elsewhere already for double quotes. Let me see if that passes the JSON importer. It does break the ability to cut and paste strings (like Chrome Extension extensions) into SQL, but there's only an apostrophe in about 1 out of 100 of those, so I can just deal with that.
s

seph

10/13/2022, 1:06 PM
I agree it’s imperfect, the double quote thing ought work. But I don’t know how quickly that will get tracked down and fixed.
1:07 PM
Mitigation better than none.
t

Thomas Stromberg

10/13/2022, 1:14 PM
Thanks so much @seph - this workaround appears to work fantastically! I'll work on adjusting our queries back to single quotes. Here's a sneak peak at what I've been up to:https://github.com/chainguard-dev/osquery-defense-kit
s

seph

10/13/2022, 1:15 PM
I’d say leave the support note open, since I think there’s a bug somewhere. But I’m glad you’re not blocked
1:19 PM
Speaking of query packs… One of the uncomfortable truths about osquery, is that the packs that osquery ships are old enough to be at best pointless. We’ve talked about how to approach this over the years, but never really found a good answer. Our current plan is to stop shipping packs and move all of that over into the docs as examples of what one can do.
1:24 PM
It’s probably super out of date, but https://github.com/teoseller/osquery-attck is interesting if you haven’t seen it
t

Thomas Stromberg

10/13/2022, 1:24 PM
I personally like that there is some sort of reference pool to pull from, but that could easily be moved into it's own repo (
osquery/packs
). Both as an idea of best practices, and as an idea of what data sources are relevant. didn't find the detection queries to be that useful, but the
incident-response
gave me additional ideas I hadn't considerde.
s

seph

10/13/2022, 1:25 PM
Exactly — there’s a lot of value there as ideas, and possibility, and historic interest. But shipping in the official osquery packages is a stronger endorsement than they merit. (Though if you’re using Kolide packages, you don’t get them shipped anyhow)
t

Thomas Stromberg

10/13/2022, 1:26 PM
I had seen that repo, and took some inspiration from it. The main difference in our approach is that the queries in
detect/
are designed to be usable as a source of alerts. In fact, we use them that way. It does mean an insane amount of false-positive management though.
1:26 PM
I don't think our current approach is the right one long-term, but it's at least proved useful for us over the last couple of months.
s

seph

10/13/2022, 1:27 PM
The thing I liked about the attck stuff, was that it used the common MITRE ATT&CK language. I always feel like there’s a couple axis of categorization. Or tags. Or something like that
Mike Myers

Mike Myers

10/13/2022, 3:16 PM
I read over on Stack Exchange that RFC7159 or maybe RFC8259 does not allow any control characters in JSON texts and requires they be escaped. Maybe these packs are not valid JSON? But they pass as valid in a JSON linter I tried...