It looks like the process_open_sockets joined with...
# windowsn
nobody 162
05/28/2019, 5:25 PMIt looks like the process_open_sockets joined with processes query is the biggest contributor. When does osquery dump the results to the db when there is a join? Given https://github.com/facebook/osquery/issues/5379 the tables may end up being called a large number of times (~170 processes and ~80 sockets). Would osquery write entries for each call to the table?
p
packetzero
05/28/2019, 5:27 PMhow are you determining this?
packetzero
05/28/2019, 5:28 PMdid you do an sst_dump on the database?
packetzero
05/28/2019, 5:29 PMIf you set disable_events=true , and restart osqueryd I would expect the IO stats to be smaller. I'm guessing it's windows_events writing lots to disk
n
nobody 162
05/28/2019, 5:30 PMI haven't, I was using procmon to see where all the writes were going - this just showed most of the calls going to the .log and .sst files. I then changed my configured scheduled queries to remove everything except the joined query and still got the large numbers vice versa with the windows_events table saw the numbers come down
nobody 162
05/28/2019, 5:30 PMI'll give it a go with the events disabled
nobody 162
05/28/2019, 5:34 PMI'll have to check that tomorrow, thanks for your help. I'll also try dumping the db and see what's actually going into it
nobody 162
05/29/2019, 10:51 AMTurns out its a combination of things (but mostly you were right about it being events).
First the new process table causes some verbose error messages about trying to get cwd for system processes - which when verbose is on those messages ended up being written to the database. So when my join happened the process table ended being called 17 times (the number of ports open) leading to a bunch of entries in the db .
Second the processes table now seems to cause a large number of calls to enumerate users local group membership - which in turn leads to lots of windows events - which get written to the database. I saw ~900 events appear after running the suspect query.
For comparison when I run the same query with 3.3.2 (on the same machine) I only saw 8 entries...
nobody 162
05/29/2019, 11:20 AMLooks like in the changes to processes in experimental the constraint checking on pid was removed so now every time the processes table is called it runs for every process whereas previously it only collected data for specified pids (from the join)
p
packetzero
05/29/2019, 2:17 PMWhat logger are you using? It could be buffering results and status logs in DB.
packetzero
05/29/2019, 2:19 PMYou are right, the new processes table does not implement pid index, which is costly. It's not hard to add. https://github.com/osql/osql/pull/3
n
nobody 162
05/29/2019, 2:25 PMAh nice, I hadn't seen that PR.
nobody 162
05/29/2019, 2:27 PMI was using aws_kinesis which the docs suggest would buffer and flush based on aws_kinesis_period. I didn't realise the buffering was done in the DB
2 Views