Does anyone have a sense of where most of the resource utilization in osquery comes from when using event-based tables with a high volume of events? Is it in the RocksDB read/write?

Is this about a specific platform? In Audit/BPF, there's a lot of stuff to parse, then there's a lot of state that need updating and finally some RocksDB too

I'm just trying to get an intuitive sense of what's going on when people talk about resource consumption issues with evented tables.

for macOS, I haven't tried but I'm expecting Endpoint Security to be working really well; events seems rather rich in metadata and didn't require too much parsing/state handling

When I talk about performance issues with evented tables, its usually two things: • osquery itself is struggling with the volume of events - REF: https://osquery.slack.com/archives/C08V7KTJB/p1647970028338159 • my backend system is struggling with the volume of events that is aggregated from a bunch of osquery endpoints sending evented data. Other agents (like Sysinternals Sysmon) allow you to have complex filters at the endpoint, before the events are shipped off the box Im actually presenting at bisdes ft. wayne next week on the 2nd issue - automatically generating osquery filters from sysmon configs. (Something @fritz worked with me on last year)

We have many customers collecting

windows_events

to forward to our SIEM and we hit every time issues with the watchdog limits as well as the rocksdb limitation mentioned in the thread above. I think those are fairly common scenarios in bigger companies where EPS in the same box goes up to at least 500 EPS. I am not sure i can say where the memory consumption comes at a lower level, but we use to send the data using the

tls

logger and i think that the performance seems better when using

filesystem

(which we can combine with some other sw for remote send like fluentbit). Now i have been testing with a logger developed by us with ingestion levels near 1000 EPS aprox that seems stable but it just works in memory for now (no rocksdb). Just sharing this info in case it helps.

In the distant past, Uptycs was talking about how performance in that kind of log shipping situation suffered because the data flows through the sql engine. Honestly, it always felt a bit weird? Like if you don’t want and of the SQL side, why use osquery as your high volume log shipper?

we had a cool concept from packetzero that traded data integrity for performance

Ah yeah I remember both of those conversations. Would it be crazy to try to use SQLite?

I think there are different things in the discussion 1. Going through sqlite to compute the results 2. Where results are stored (currently rocksdb) 3. Where buffered log lines are stored (also rocksdb, from the buffered log forwarder)

I’m generally against bypassing (1). Mostly coming from a “what else is osquery” stance. I’m willing to be convinced. And I have no deep feelings about (2) and (3). To me, those are implementation details, and I’ll roll with whatever y’all tell me makes sense

Totally agree with the above sentence

I'm most curious about whether it could make sense to use sqlite to do 2 and 3. But it doesn't matter if RocksDB is only a small portion of the resource utilization for the event based tables.

before the integrity was relaxed again, it was taking a significant toll on cpu/memory

Does our SQLite have any kind of disk persistence mechanism?

IIRC we open the DB in memory, so even when you do things like

create table

they go away on restart.

Kinda what I mean… So using sqlite for (2) or (3) has data loss questions

Ah yeah but I would think if we did it we would use disk-backed sqlite.

Could be an interesting experiment. Though I wonder why. It feels like a deepish change, and hitting disk is hitting disk.

Partly I'm interested in the idea of "sqlite all the way down". Getting some of the data living in an actual sqlite table seems like it could bring osquery's behavior closer to what many folks expect.

Maaaybe. I am somewhat sketpcial.

Also I'm still scarred from seeing so many DB corruption issues in the past with RocksDB. I'm not sure that's been an issue as much lately though?

For (2) and (3) I think we’d end up needing to do a lot that subverts sqlite expectations.

Many (most?) tables aren't conducive to that pattern because there's no API for events. Osquery as an api translation layer works really well for a lot of apis, but less so for events IMO.

I agree! I don't have a simple model for events.

joins are also really weird when using evented tables

Thinking aloud…. 1. Events feed a table 2. That table has some max events cleanup 3. Events have a unique Id 4. join select, whatever. 5. The magic thing would track last id, and add an implicit “where id > last” But that's a lot of overhead, and I'm not sure it would be performant.

I think the problem is that you can for example add/remove users while events are generated