Slide 1

Slide 1 text

10 Pitfalls on The Path to Osquery Bliss Zach Wasserman — Osquery/Fleet Consultant, Dactiv LLC QueryCon 2019

Slide 2

Slide 2 text

Pitfall #1 User context is important when executing queries

Slide 3

Slide 3 text

User context is important when executing queries • As user:
 SELECT * FROM firefox_addons; • As root:
 SELECT * FROM firefox_addons;

Slide 4

Slide 4 text

User context is important when executing queries • Osquery sometimes uses the user context in which it is running to retrieve results. • Solution: JOIN with the users table.
 SELECT * FROM users
 JOIN firefox_addons USING (uid);

Slide 5

Slide 5 text

No content

Slide 6

Slide 6 text

Pitfall #2 Order of JOINed tables can be significant

Slide 7

Slide 7 text

Order of JOINed tables can be significant • As root:
 SELECT * FROM firefox_addons
 JOIN users USING (uid);

Slide 8

Slide 8 text

Order of JOINed tables can be significant • The order in which the tables are generated can effect the constraints the generation function receives. • Solution: Order the JOINs so that tables that require constraints are generated after.
 SELECT * FROM users
 JOIN firefox_addons USING (uid); • Note: Sometimes the SQLite optimizer will reorder the tables anyway. To be sure the tables are JOINed in the order provided, use CROSS JOIN.
 SELECT * FROM users
 CROSS JOIN firefox_addons USING (uid)
 WHERE identifier LIKE '%mozilla%';

Slide 9

Slide 9 text

No content

Slide 10

Slide 10 text

Pitfall #3 Dude, where’s my SHA1? Reading large files and the --read_max flag

Slide 11

Slide 11 text

Reading large files and the --read_max flag • SELECT * FROM hash
 WHERE path = '/Users/zwass/suspicious';

Slide 12

Slide 12 text

Reading large files and the --read_max flag • Tables that try to read files over the --read_max size (default 50MB) can return empty results. • This can effect most tables and osquery functions that involve reading files, not just the hash table! • Solution: Tune the --read_max flag if you need results from large files.

Slide 13

Slide 13 text

No content

Slide 14

Slide 14 text

Pitfall #4 JSON Escaping and Query Packs

Slide 15

Slide 15 text

JSON Escaping and Query Packs • Let's copy a query from the windows-attacks query pack: ... "CCleaner_Trojan.Floxif": { "query" : "select * from registry where path like 'HKEY_LOCAL_MACHINE\\SOFTWARE\\Piriform\ \Agomo%';", ...

Slide 16

Slide 16 text

JSON Escaping and Query Packs • JSON backslashes are escaped as '\\', while osqueryi expects backslashes to use the literal '\'. • Solution: Be careful to use the appropriate escaping and modify for the format when translating between osqueryi and JSON query packs. • Note: The fleetctl format uses yaml and therefore does not require any escaping in backslashes. This means that queries can be directly copy/pasted to osqueryi.

Slide 17

Slide 17 text

No content

Slide 18

Slide 18 text

Pitfall #5 CLI Flags vs. Configuration Options

Slide 19

Slide 19 text

CLI Flags vs. Configuration Options • Let's try setting the extensions_socket configuration in our config file: {
 "options": {
 "extensions_socket": "/tmp/osquery_ext.sock"
 }
 }

Slide 20

Slide 20 text

CLI Flags vs. Configuration Options • Some options must be specified as CLI flags (and can't be modified after osquery startup), while others are configurable in a loaded configuration. • osqueryd --help will tell us which flags are CLI-only • Solution: Identify flags that are CLI-only and specify those in explicit flags or a flagfile.

Slide 21

Slide 21 text

No content

Slide 22

Slide 22 text

Pitfall #6 Understanding schedule intervals

Slide 23

Slide 23 text

Understanding schedule intervals • Schedule a query. • Put the computer to sleep. • When does the query run?

Slide 24

Slide 24 text

Understanding schedule intervals • The osquery scheduler runs on ticks (while the process is active), not wall time. • Solution: Account for time the machine is off or suspended when creating query intervals.

Slide 25

Slide 25 text

No content

Slide 26

Slide 26 text

Pitfall #7 Events in osqueryd and osqueryi

Slide 27

Slide 27 text

Events in osqueryd and osqueryi • Run osqueryd and see that events are collected. • Run osqueryi and query for the events.
 Where are they?

Slide 28

Slide 28 text

Events in osqueryd and osqueryi • An ephemeral database is used with osqueryi by default. • Solution: Provide the --database_path flag to osqueryi to open the RocksDB database used by osqueryd. • Note: Only one osquery process can open a database at a time. Terminate osqueryd before connecting osqueryi to the database.

Slide 29

Slide 29 text

No content

Slide 30

Slide 30 text

Pitfall #8 Tuning event expiration flags

Slide 31

Slide 31 text

Tuning event expiration flags • Run osquery with a low events_max: {
 "options": {
 "disable_events": false,
 "events_max": 4
 }
 }

Slide 32

Slide 32 text

Tuning event expiration flags • The flags --events_max and --events_expiration prevent the events buffers from growing indefinitely. • Solution: Ensure that the flags are tuned appropriately for the query intervals and volumes of data being generated by event publishers.

Slide 33

Slide 33 text

No content

Slide 34

Slide 34 text

Pitfall #9 Event publisher status

Slide 35

Slide 35 text

• osqueryd is running with events enabled • How can we understand why events are not coming through publishers? Event publisher status

Slide 36

Slide 36 text

Event publisher status • The osquery_events tables provides status information about event publishers and subscribers • Solution: Look at the active, events, and subscriptions columns of the osquery_events table for the relevant publishers.
 SELECT * FROM osquery_events;

Slide 37

Slide 37 text

No content

Slide 38

Slide 38 text

Pitfall #10 Identifying expensive queries

Slide 39

Slide 39 text

Identifying expensive queries • With osqueryd running a schedule • How can we identify which queries are utilizing the most resources?

Slide 40

Slide 40 text

Identifying expensive queries • The osquery_schedule table exposes metadata about the scheduled queries and their resource consumption. • Solution: Look for outliers in the osquery_schedule table
 SELECT * FROM osquery_schedule
 ORDER BY user_time + system_time DESC • Note: The osquery repository also has performance tooling at /tools/analysis/profile.py.

Slide 41

Slide 41 text

No content

Slide 42

Slide 42 text

Zach Wasserman github.com/zwass Osquery Slack: @zwass Twitter: @thezachw [email protected]