Introduction to Osquery Workshop 1 2019 Pass the S ALT Workshop - PowerPoint PPT Presentation

Introduction to Osquery Workshop 1 2019 Pass the S ALT Workshop

Overview 2 Introduction to Osquery Osquery Basics S QL Refresher Osquery Configuration and Extensions Fleet Management Osquery and Elastic S tack

Introductory Workshop! 3 • This is an introductory workshop • Y ou probably won’ t hear/ see a lot of new things if you have: • Already used osquery; • Followed S ANS S EC599, etc.; • If you are stuck, please do not suffer in silence!

Workshop VM 4 • ais_workshop_xubuntu-18.04.2-desktop-amd64 • VMware Workstation, Player, or Fusion sorry!  • Y ou can try VirtualBox too, but you are on your own with that… • 8 GB RAM • 30-50 GB disk space • Keyboard layout: EN-US !!! • Workshop VM (Ubuntu) user/ pass: user / Workshop1234% • Normally, it should not require password for login and sudo

About David 5 • Managing partner at Alzette Information S ecurity (@ AlzetteInfoS ec) • Network penetration testing, security architectures, security monitoring, incident response • Instructor at S ANS Institute: FOR572 • BS ides Luxembourg organizer https:/ / bsideslux.lu • Twitter: @ DavidS zili • E-mail: david.szili@ alzetteinfosec.com • Blog: http:/ / j umpespj ump.blogspot.com

Introduction to Osquery 6 2019 Pass the S ALT Workshop

About Osquery 7 What is osquery? Why osquery? • Build for: • (Free) Open S ource S oftware • S ecurity • Cross-platform • Compliance • One platform for monitoring • Operations (DevOps) • Native packages for supported • Everything in S operating systems QL! • Large-scale host monitoring or • Exposes the operating system as a relational database threat hunting • Developed by Facebook • Growing Community

Osquery History 8 2018 APR 25: v3.2.4 - First stable 2014 OCT 29: release in 3.0.0 Announcement series 2016 SEP 27 / 2019 JUN 28: 2016 Oct 4: osquery 4.0.0 Osquery for Windows released (Trail of Bits)

Osquery flavours 9 Carbon Black LiveOps™ osql • Osquery open source "soft -fork" from Trail of Bits • https:/ / blog.trailofbits.com/ 2019/ 04/ 18/ announcing-the-community- oriented-osquery-fork-osql/ • https:/ / osql.io

Osquery Basics 10 2019 Pass the S ALT Workshop

Installation 11 • Built and signed by the osquery team • Uses minimal number of run-time library dependencies • Binaries are a bit big (~20MB) • Packages for: • macOS • Linux (Tarball, RPM, DEB) • Windows (MS I) • https:/ / osquery.io/ downloads • Alternative downloads: darwin, apt, yum, freebsd, chocolatey repositories

Getting Help 12 • Osquery Documentation • https:/ / osquery.readthedocs.io/ en/ stable/ • Osquery S lack • https:/ / osquery-slack.herokuapp.com/ • Osquery E-mail (for long-form questions) • osquery@ fb.com • Osquery Github • https:/ / github.com/ facebook/ osquery/ issues

Main Components 13 osqueryi osqueryd • Host monitoring daemon • Interactive query console • Distributed, high-performance, • Provides an SQL interface low-footprint • Completely standalone, no • S chedules queries to be communication with a daemon executed across an entire • Does not require elevated infrastructure privileges (root/ Administrator), • Aggregates query results and but not every table can be generates logs queried in this case

Osquery S QL and schema 14 • S • Osquery schema documentation : uperset of S QLite’s S QL https:/ / osquery.io/ schema • SELECT only! (without using extensions) • More than 200 tables in total! • Y ou can still create run-time • All platforms: ~40 tables/ VIEWs • MacOS : ~160 • "S QL As Understood By S QLite“ : • FreeBS D: ~40 • https:/ / www.sqlite.org/ lang.html • Linux: ~130 os que r y> . he l p • Windows: ~73 os que r y> . t a bl e s os que r y> . s c he m a

Using osqueryi 15 • Used for: • Linux/ BS D/ MacOS : • $ {sudo} osqueryi 1. Developing queries 2. Exploring a single system • Windows: • S ide note: • Osqueryi is not in the path by default • There is no connection between • C:\ProgramData\osquery\osqueryi.exe interactive and daemon mode {in an Administrator console} • However, osqueryi and osqueryd are the same binary! • Y ou can run osqueryi in daemon mode and osqueryd interactively 

Osquery S hell and S chema Hands-On 16 2019 Pass the S ALT Workshop

S QL Refresher 17 2019 Pass the S ALT Workshop

S ELECT (1) 18 • SELECT statement Operator Description • FROM : defines input data = Equal • WHERE : boolean expression evaluated for <> Not equal each row > Greater than • GROUP BY : Groups the result-set by one or more columns < Less than • HAVING : boolean expression evaluated once >= Greater than or equal for each group (can use aggregate functions) <= Less than or equal • DISTINCT/ALL : no duplicate rows/ all rows displayed BETWEEN Between a certain range • https:/ / www.sqlite.org/ lang_select.html LIKE S earch for a pattern IN S pecify multiple values

S ELECT (2) 19 • More on SELECT statement: • Compound S ELECT S tatements • ORDER BY : the list of expressions • UNION ALL : returns all the rows in the ORDER BY determine the from two S ELECTs order in which rows are returned • UNION : like UNION ALL, but • ASC : smaller values returned first duplicate rows are removed • DESC : larger values returned first • INTERSECT : returns the intersection • LIMIT : upper bound on the number of the results of two S ELECTs of rows returned • EXCEPT : Returns the subset of rows • OFFSET : the first X number of returned by the left S ELECT that are rows are omitted from the results not returned by the right-hand S ELECT

Aggregate Functions 20 Function Description avg(X) Returns the average value of all non-NULL X within a group count(X) Returns a count of the number of times that X is not NULL in a group count(*) Returns the total number of rows in the group group_concat(X) Returns a string which is the concatenation of all non-NULL values of X group_concat(X,Y) group_concat(X) and Y is used as the separator between instances of X max(X) Returns the maximum value of all values in the group min(X) Returns the minimum non-NULL value of all values in the group sum(X) Returns the (integer) sum of all non-NULL values in the group total(X) Returns the (float) sum of all non-NULL values in the group

JOIN 21 • INNER JOIN (or j ust JOIN ): • LEFT OUTER JOIN (or j ust combines column values of two LEFT JOIN ): returns all values tables based upon the j oin from the left table, even if predicate ( ON keyword) there is no match with the right table • USING : specifies a list of one or more columns as a condition • ON , USING , NATURAL : works the • NATURAL INNER JOIN : same way as in INNER JOINs automatically tests for equality • CROSS JOIN : matches every between the values of every row of the first table with column that exists in both tables every row of the second table

Osquery Complex Query Example 22 os que r y> SELECT da t e t i m e ( l ogge d_i n_us e r s . t i m e , ' uni xe poc h' ) AS da t e t i m e , l ogge d_i n_us e r s . t ype , l ogge d_i n_us e r s . us e r , us e r s . ui d, l ogge d_i n_us e r s . t t y, l ogge d_i n_us e r s . pi d, pr oc e s s e s . na m e AS pr oc e s s _na m e , pr oc e s s e s . pa t h . . . > FROM l ogge d_i n_us e r s . . . > LEFT J OI N pr oc e s s e s USI NG( pi d) . . . > LEFT J OI N us e r s ON us e r s . us e r na m e = l ogge d_i n_us e r s . us e r ; +- - - - - - - - - - - - - - - - - - - - - +- - - - - - - - - - - +- - - - - - - - - - +- - - - - - +- - - - - - +- - - - - - +- - - - - - - - - - - - - - +- - - - - - - - - - - - - - + | da t e t i m e | t ype | us e r | ui d | t t y | pi d | pr oc e s s _na m e | pa t h | +- - - - - - - - - - - - - - - - - - - - - +- - - - - - - - - - - +- - - - - - - - - - +- - - - - - +- - - - - - +- - - - - - +- - - - - - - - - - - - - - +- - - - - - - - - - - - - - + | 2019- 03- 26 21: 35: 00 | boot _t i m e | r e boot | | ~ | 0 | | | | 2019- 03- 26 21: 35: 13 | l ogi n | LOGI N | | t t y1 | 834 | a ge t t y | / s bi n/ a ge t t y | | 2019- 03- 26 21: 35: 14 | us e r | us e r | 1000 | t t y7 | 1248 | s h | / bi n/ da s h | | 2019- 03- 26 21: 35: 39 | r unl e ve l | r unl e ve l | | ~ | 53 | | | +- - - - - - - - - - - - - - - - - - - - - +- - - - - - - - - - - +- - - - - - - - - - +- - - - - - +- - - - - - +- - - - - - +- - - - - - - - - - - - - - +- - - - - - - - - - - - - - +

Osqueryi Hands-On 23 2019 Pass the S ALT Workshop

Osquery Configuration and Extensions 24 2019 Pass the S ALT Workshop

Using osqueryd 25 • Osqueryd is the host monitoring • Configuration and query schedule daemon { " os que r y_i nf o" : { • It aggregates query results over " que r y" : " SELECT * FROM os que r y_i nf o; " , time and generates logs " i nt e r va l " : 300, " s na ps hot " : t r ue • Allows to: } } 1. S chedule queries • Logging and reporting 2. Record OS state changes, including file and directory • Query Packs changes, hardware events, network events, etc.