Last updated at Sun, 19 Nov 2023 00:51:29 GMT
What is osquery?
osquery is an open source tool created by Facebook for querying various information about the state of your machines. This includes information like:
- Running processes
- Kernel modules loaded
- Active user accounts
- Active network connections
And much more!
osquery allows you to craft your system queries using SQL statements, making it easy to use by security engineers that are already familiar with SQL.
osquery is a flexible tool and can be used for a variety of use cases to troubleshoot performance and operational issues. From a security perspective, it can be used to query your endpoints to detect, investigate, and proactively hunt for various types of threats. For example: if you suspect a malicious process is running on a system, you can query for the process by name or even a filename it has open. We’ll talk about some more of these below.
osquery at a glance
Query for top 10 largest processes by resident memory size
select pid, name, uid, resident_size from processes order by resident_size desc limit 10;
Return process count, name for the top 10 most active processes
select count(pid) as total, name from processes group by name order by total desc limit 10;
As you can see, you can use standard SQL including limits, aggregates, and joins, you can ask powerful questions about your infrastructure! And you’re not just limited to process information – you can view the full list of ‘tables’ you can query from in the documentation (plus we’ll explore more in the examples below).
Deploying osquery
osquery is agent software that must run directly on your endpoints (e.g, your OSX installation or Linux servers). osquery will require root or system privileges to get a lot of detailed system information, although it is possible to glean some information when not ran as 'root'. For more information, see the official deployment guide.
One important thing to note as you plan your deployments is that are two main ways to deploy osquery depending on your use case(s).
- For performing ad-hoc queries, you only need osqueryi (the command line tool).
This deployment is useful in DFIR use cases where you are only using osquery in response to detection events from other tools. OSQuery information can be used to perform or supplement other live forensics or incident response tasks, e.g. logging into and running osqueryi queries to pinpoint the endpoint process that has triggered a network indicator to fire. The pros to this type of deployment is that it’s extremely lightweight and unlikely to have any impact to the endpoint users or application. The downsides are that without other additional automation or tooling, it’s hard to use osquery to detect active threats.
- To run queries periodically in the background, you should install osqueryd (the osquery daemon).
This mode is extremely useful for detection use cases, when you are doing things like good state detection (e.g., did a new process start since the last check?) or regular behavioral checks for specific indicators of compromise (are any of these network connections suspicious?). The downsides is that you will need some other infrastructure and tooling to aggregate the output of these queries (e.g., take the osquery output in syslog and store in ElasticSearch for analysis).
To get started, you should try the osqueryi tool. Running the osqueryi command line tool will bring you to a “REPL”-like prompt where you can begin to run your queries.
Try typing osqueryi below, then running select * from processes;
You can also type your query right after the osqueryi command to run it immediately and exit:
osqueryi "select * from processes limit 3;"
Try it in the terminal above! You can also run the command by hitting the 
button at the top right hand corner! Even if you decide to use the osqueryd infrastructure, the osqueryi command line tool is a useful way to test and play around with queries.
Use Cases for Threat Detection & DFIR
Let’s get started running some queries that could be useful for a security team.
Remember: some of these queries are designed for Linux, and others for OSX. A query that runs on OSX will not necessarily work on Linux.
To run the queries, simply copy and paste the query into your osqueryi console at the osquery> prompt. Of note: all queries need to be terminated with a ‘semicolon’ in order to execute the query. Let’s get started!
Use case 1: Finding new processes listening on network ports
Frequently, malware will listen on port to provide command and control (C&C) or direct shell access for an attacker. Running this query periodically and diffing with the last ‘known good’ results will provide the security team with any new processes that are listening for network connections, and allow the team to investigate the nature of that process.
SELECT DISTINCT process.name, listening.port, listening.address, process.pid FROM processes AS process JOIN listening_ports AS listening ON process.pid = listening.pid;
To run the query, hit the button at the top right hand corner.
Use case 2: Finding suspicious outbound network activity
On endpoints with well-defined behavior, the security team can use osquery to find any processes that do not fit within whitelisted network behavior, e.g. a process scp’ing traffic externally when it should only perform HTTP(s) connections outbound.-- example: looks for processes with IP traffic to ports not in (80, 443) select s.pid, p.name, local_address, remote_address, family, protocol, local_port, remote_port from process_open_sockets s join processes p on s.pid = p.pid where remote_port not in (80, 443) and family = 2;
To run the query, hit the button at the top right hand corner.
Use case 3: Finding processes that are running whose binary has been deleted from the disk
Frequently, attackers will leave a malicious process running but delete the original binary on disk. This query returns any process whose original binary has been deleted or modified (which could be an indicator of a suspicious process).
SELECT name, path, pid FROM processes WHERE on_disk = 0;
Use case 4: Finding specific indicators of compromise (IOCs) in memory or on disk
osquery can aid your team in quickly querying for specific indicators of compromise. For example, Facebook has provided the queries below which detect Hacking Team’s OSX backdoor by querying for specific persistent mechanisms and file system activity on OSX:
select * from file where path = '/dev/ptmx0';
select * from apps where bundle_identifier = 'com.ht.RCSMac' or bundle_identifier like 'com.yourcompany.%' or bundle_package_type like 'OSAX';
select * from launchd where label = 'com.ht.RCSMac' or label like 'com.yourcompany.%' or name = 'com.apple.loginStoreagent.plist' or name = 'com.apple.mdworker.plist' or name = 'com.apple.UIServerLogin.plist';
Use case 5: Finding new kernel modules that have loaded
Running this query periodically and diffing against older results can yield whether or not a new kernel module has loaded: kernel modules can be checked against a whitelist/blacklist and any changes can be scrutinized for rootkits.
select name from kernel_modules;
Want more queries for security teams? Facebook has created a mechanism to distribute/share osquery ‘scripts’ related to various threat detect.
Limitations of osquery
Generally, much of osquery works in a ‘polling’ fashion – i.e., any queries will return results at its existing state. There are some exceptions to this. Using this framework, you can record events as they occur in real-time and query the history later, such as filesystem activity via the inotify faculty in Linux.
Similarly, for threats that get root access to the machine, it could be possible for that process to tamper with the osquery processes. osquery does not attempt to hide or obfuscate itself using rootkit techniques (unless you build this functionality yourself).
osquery does not come with centralized deployment or data collection mechanisms across a installation base. There are some fleet management tools (see “Complementary Tools” below) that have been provided by the open source community, but at its core osquery only ships with its single endpoint agent. To use osquery operationally for detection, some heavy infrastructure lift will be required by the security and IT/operations teams.
Complementary Tools/Resources
- https://github.com/mwielgoszewski/doorman – Doorman is an open source fleet management solution for osquery.
osquery Alternatives
osquery isn’t by any means the only way of gathering this information and performing these kinds of detailed endpoint checks. There are other techniques and tools in the open source and commercial world, a few of which are listed below (Google for ETDR tools to see more commercial ones). Some of these use a ‘continuous recording’ approach, while others are polling-oriented.
- Auditd (Open Source, Linux only, continuous only)
- OSSEC (Some continuous support (filesystem checks) and some polling only, e.g. will periodically query netstat (network state information) to check for new ports)
- Querying the /proc filesystem directly (Linux only)
- Sysdig (Linux only, commercial + open source, requires kernel module)
- Carbon Black (commercial, Windows + Linux)
- Threat Stack (commercial, continuous only, Linux only)
