Hello I wrote a couple extensions for OsQuery that have long

Question

Hello I wrote a couple extensions for OsQuery that have long run times as much as 30 seconds When scheduling them to both run every 300 seconds in a pack they sometimes run at the same time or at least overlap Is there a way to guarantee ALL scheduled queries are run serially and never at the same time Thank you

alessandrogario · Answer

i don t think it is possible you could try using different timers but it s not guaranteed it s enough to avoid that situation

alessandrogario · Answer

it would be best not to have long query processing times and instead do it in another thread and return whatever is ready at query time

alessandrogario · Answer

i e one thread fills a list of rows and the generate returns them

TonyC · Answer

I may have found a fix by instead of using a pack with multiple individual queries into a pack that calls a single combined query By combining all the individual queries int a single query they each run in series This is obviously not optimal as it will only work if you want all the queries to run in the same interval of say every 5 minutes If you want an addition query to run every 1 minute there is potential for that 1 minute query to run at the same time as a 5 minute combined query IMO there should be logic in OsQuery to not execute a query in a pack until any other queries have already completed IE a Queue I know most of what OsQuery does out of the box is pretty instant and this problem I have is pretty much non existent however OsQuery is designed to run custom extensions and users like me need to run longer running queries

alessandrogario · Answer

I think this is a design issue inside the extension additionally I m not sure that SQLite itself actually likes when tables locks inside virtual tables it s not something it was made for

alessandrogario · Answer

I don t think it makes sense to support extensions that have long query times

TonyC · Answer

I don t think there is a way for the extension to check if another query is already in progress

alessandrogario · Answer

queries should return data that is ready

alessandrogario · Answer

or at least data that can be easily gathered without locking inside the SQLite callbacks

alessandrogario · Answer

I would suggest to fix the extension to only return data ready to be returned back to osquery and move the worker outside the main thread

alessandrogario · Answer

this would be similar to the event based tables that are already present in osquery

alessandrogario · Answer

you can also use writable tables if you wish to control which data is produced from SQL

alessandrogario · Answer

i e INSERT INTO your extension table lt job details gt

TonyC · Answer

If you have multiple records with millions of records and you have to do joins it will take time to gather the data Having a long running querie is no different IMO I m not asking for OsQuery to support longer running queries I m stating that OsQuery should not run queries at the same time That would just happen to resolve my issue slightly smiling face

alessandrogario · Answer

and in another table save the data that has been produced

alessandrogario · Answer

that reason is exactly why no heavy work should happen inside generate

alessandrogario · Answer

it s not always 100 clear how many times you are hitting each table when a complex query is run

alessandrogario · Answer

you can take a look at event based queries and replicate that functionality within your extension

alessandrogario · Answer

if you want to control the component producing data with INSERTs then you can also look at the extension examples

alessandrogario · Answer

this way correctness will always be enforced

TonyC · Answer

The extensions I m writing are not the standard use case We use OsQuery to report the health of the Client including network access IE the client is currently utilizing CPU Mem HDD and running a speedtest has so much bandwidth available We compare what clients are doing at each site to determine if there is a problem at a specific site as compared to others Yes this technically goes beyond the scope of OsQuery but again OsQuery is designed to be extended

alessandrogario · Answer

otherwise even if the schedule is serialized the user can still lock your extension osquery if more than one heavy table is referenced in the same SQL query

alessandrogario · Answer

osquery is designed to be extended but there are rules in place to provide correctness and robustness

alessandrogario · Answer

additionally there s SQLite behind the tables and this also adds additional constraints

alessandrogario · Answer

SQLite is being used in serialized mode default configuration

alessandrogario · Answer

it is bad to lock everything inside its callbacks

TonyC · Answer

It s not a lock if it s scheduled IE before query is executed from a pack ensure no other queries are currently active

TonyC · Answer

OsQuery is managing when a query is made to the SQLite service I m just stating that OsQuery should make sure no other execution requests have been made until the others have completed

alessandrogario · Answer

i don t think it matters whether the query is scheduled or not regardless when the query hits the table everything is inside SQLite

alessandrogario · Answer

remembering to not perform too many queries against specific tables is a really bad user experience design

TonyC · Answer

Again OsQuery manages when a query hits the table It manages the schedule of a pack and when the query in that pack runs IE every 300 seconds It can certain have logic to state it s time for the next interval to execute Are all other complete If so run

alessandrogario · Answer

delaying queries like that is just a hack that will fall short as soon as multiple ad hoc queries are run

alessandrogario · Answer

or a single SQL query joins multiple tables that perform too much work in their own generate functions

TonyC · Answer

That is true ad hoc queries would throw a wrench in that scenario

alessandrogario · Answer

yeah same for SELECT FROM heavy table1 JOIN heavy table2 JOIN heavy table3

alessandrogario · Answer

You could try opening a feature request to see what everyone would think about implementing this

alessandrogario · Answer

It could be maybe useful but I think that as it is it s the wrong answer to a wrong question

alessandrogario · Answer

there are some additional hacks that can be done easy sh though

TonyC · Answer

My use case is pretty unique I doubt others are facing the same issue otherwise there would probably already be a feature request My initial question has been answered that OsQuery will run scheduled queries at the same time I was hoping the answer was no

alessandrogario · Answer

like providing a new table that contains a boolean for each running generate in your extension

alessandrogario · Answer

then decrease the schedule time

alessandrogario · Answer

and use that table to control whether the query ends up inside the big table

TonyC · Answer

hmm that s an interesting approach

alessandrogario · Answer

so that metadata table would contain a row for each busy table

alessandrogario · Answer

that lives inside the extension

alessandrogario · Answer

can probably also be hacked together with discovery queries

alessandrogario · Answer

it s not a unique use case though

alessandrogario · Answer

i have written a bpftrace integration that has a generate function that runs forever

alessandrogario · Answer

but the table exposed to osquery contains jobs

alessandrogario · Answer

that can be added removed with INSERT and DELETE

TonyC · Answer

I can just add a variable in my extension that keeps track Would be easier if there was a way to query OsQuery for running queries slightly smiling face

alessandrogario · Answer

and everything else is handled as events

TonyC · Answer

Well again in my case I am doing speedtests The reason I want it serialized is I don t want 2+ speedtests running at the same time That affects the total throughput available If it was just a case of long running queries it wouldn t matter as the query would just report when it completed

alessandrogario · Answer

this is usually handle with caching

alessandrogario · Answer

cache expires every X minutes and a hidden column to the table is added

alessandrogario · Answer

so that it can be forcefully cleared

alessandrogario · Answer

`SELECT FROM speed test ` would use the cache automatically it not expired

alessandrogario · Answer

```SELECT FROM speed test WHERE force cache invalidation=1 ```

alessandrogario · Answer

would force it from scratch

alessandrogario · Answer

we already have tables that work kind of like this

TonyC · Answer

caching wouldn t help if I have a pack with 5+ individual queries that are scheduled to run every 300 seconds Each speed test would run at the same time which would cause invalid results

alessandrogario · Answer

the HIDDEN column will not show up in SELECT statements unless directly queried

alessandrogario · Answer

it could return no rows if there is no measure and it can t be taken

TonyC · Answer

To be clear I am running 5 different tests in my queries IE select from speedtest where dc = 1 and select from speedtest where dc = 2 When executed it runs an iperf like test to the specified DC If both of these queries are running at the same time the throughput for each query is cut in half

alessandrogario · Answer

if the table is the same a more sqlite osquery friendly approach would be to remove the WHERE

alessandrogario · Answer

pass the DC list via configuration

alessandrogario · Answer

so that the 5 DCs are tested in a single SELECT FROM speedtest

TonyC · Answer

We want to monitor the bandwidth the client has to each DC When scheduling one to run every 300 seconds and the second every 330 there is still potential for overlap

alessandrogario · Answer

so one row per DC passed via configuration

alessandrogario · Answer

and still use caching when possible this would be user friendly and hard to break

alessandrogario · Answer

from SQL

TonyC · Answer

I have a workaround by having a single query select from speedtest where dc = 1 select from speedtest where dc = 2 With this single saved query each is executed in series and resolves the problem of having them in separate saved queries which would be called at the same time by the pack

TonyC · Answer

I actually have a temporary table with all the DCs defined and run select from speedtest where dc in dctable

TonyC · Answer

This runs everything in series but causes a problem if I wanted to have different speedtests run at different times IE dcs 1 2 at every 300 seconds and dcs 3 4 at every 600 seconds There will be overlap and multiple speedtests will run at the same time

TonyC · Answer

I will just have to keep track in the extension when tests are being run and don t execute the pack if in progress