Heads up it looks we’re waiting a little longer for 4.4.0 due to wanting for https://github.com/osquery/osquery/pull/6280 to be solid. I fully support taking our time on that.

Alessandro should have some time tomorrow to give that attention, he said

We have finished with the second pass on the PR, and here's a summary of what we have changed: 1. The 'keywords' column has been changed from BIGINT to TEXT. This is because SQLite does not support std::uint64_t and Windows events sometimes return values that can't be represented with an std::int64_t 2. The tests have been changed: we have exported several different event types from different event channels. Those samples are used to compare the output we get with the output we expect 3. The

data

column has been slightly changed; instead of adding the fields directly inside a single object (example:

{ "key": "value"}

), we now have a root "Data" object (example:

{ "Data": { "key": "value" } }

). This allows us to extend this with additional data from the Windows Event Log XML without breaking compatibility

Ha. I have also been thwarted by sqlite’s lack of

uint64_t

so the XML event has an EventData object, which is just a small part

I guess having

{data: {...}}

means you can add

{metadata: {}}

or something

and inside EventData, we have Data tags + other things

Cool. Makes sense. Thanks.

if we decide we want to add additional stuff the data column, it's easier if we keep things separat

Yes, makes sense. You’re not adding new data fields, you’re adding something next to data.

Yeah, I also thought having JSON in colums to be weird

Nice! The new features are good. They do break existing functionality though. I would recommend having FLAG's to enable this new functionality? Typically, a schema would have new added columns rather than changing existing? Also, I think most servers flatten result structures, why not go with columns with a 'data_' prefix rather than adding the data object level?

Yeah, the problem there is that they are property bags written in different formats. Sometimes it's an array, sometimes a key-value

yeah, I did a POC at one point, and used data_0, data_1, etc. when it was an array

there was no way to keep the compatibility because we would have to reproduce the parsing bugs

sqlite has some json functions. You can get direct access to the json subfields, but it’s pretty ugly.