Monitoring and managing the impact of query performance on Osquery

Performance testing and bench-marking Osquery queries

The Osquery watchdog

Scheduled query may have failed: <query_name>

Osquery watchdog command line options

Osquery profiler: profile.py

pip3 install psutil
git
clone git@github.com:osquery/osquery.git
{
"schedule": {
"proc1": {
"query": "SELECT * FROM processes;",
"interval": 60
},
"proc2": {
"query": "SELECT * FROM processes WHERE pid > 1000;",
"interval": 60
}
}
}
./tools/analysis/profile.py --shell `which osqueryi` --config test.confProfiling query: SELECT * FROM processes;
U:2 C:1 M:3 F:0 D:2 proc1 (1/1): utilization: 43.95 cpu_time: 0.44 memory: 26013696 fds: 4 duration: 1.0
Profiling query: SELECT * FROM processes WHERE pid > 1000;
U:2 C:1 M:3 F:0 D:2 proc2 (1/1): utilization: 43.95 cpu_time: 0.44 memory: 26013696 fds: 4 duration: 1.0
./tools/analysis/profile.py --shell `which osqueryi` --query "SELECT * FROM processes;" --rounds 3 --count 10Profiling query: SELECT * FROM processes; U:3  C:2  M:3  F:0  D:3  manual (1/3): utilization: 78.66 cpu_time: 2.37 memory: 26435584 fds: 4 duration: 3.0 
U:3 C:2 M:3 F:0 D:3 manual (2/3): utilization: 84.87 cpu_time: 3.41 memory: 26210304 fds: 4 duration: 4.0
U:3 C:2 M:3 F:0 D:2 manual (3/3): utilization: 75.25 cpu_time: 2.27 memory: 25980928 fds: 4 duration: 2.5
U:3 C:2 M:3 F:0 D:3 manual avg: utilization: 79.59 cpu_time: 2.6 memory: 26208938 fds: 4.0 duration: 3.2
Osquery profiler results.

Osquery osquery_schedule

SELECT * FROM osquery_schedule WHERE last_executed > 0;
Querying Osquery’s osquery_schedule table to fetch performance information related to our scheduled queries.
SELECT name, query, interval, executions, last_executed, denylisted, output_size,
IFNULL(system_time / executions, 0) AS average_system_time,
IFNULL(user_time / executions, 0) AS average_user_time,
IFNULL(wall_time / executions, 0) AS average_wall_time,
ROUND((average_memory * '10e-7'), 2) AS average_memory_mb
FROM osquery_schedule;

Optimizing queries

Table delay

File Hashing

Its all over!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store