Basic Queries

Basic Queries to run using OSQuery

There are many different queries that can be run using OSQuery which can give useful information about the system being queried. This information can help hunt down a possible backdoor or malware that may have run or still may be running on a system. First, let's start with a few basic queries.

Logged In Users

Let's take a look at all currently logged-in users on the system. The below query will print all current sessions on the system while giving valuable information such as the user type, user, host, time, and pid.

select * from logged_in_users;

All Users Listed are Currently Logged Into the System.

To expand on this, it can be checked to see if anyone has logged in as the root user for the target machine using the following query.

select * FROM last
WHERE username = "root"
AND time > (( SELECT unix_time FROM time ) - 3600 );

Processes, Their Paths, Their Network Address, and Their Ports

Something that can help with hunting down malware or other suspicious processes is this query. This query will list any process's path that is running on the system, their assigned port(s), and their assigned network address(es) and pid.

SELECT DISTINCT process.path, listening.port, listening.address, process.pid FROM processes AS process JOIN listening_ports AS listening ON process.pid = listening.pid;

Process Paths Listed with Their Associated Port(s) and Address(es)

In addition to this query, here is one that will list any and all processes that are currently running on the system without an associated binary on the disk. Malware likes to delete its binary after running in order to try and hide itself and its origin. This is why processes without associated binaries are typically seen as suspicious.

SELECT name, path, pid FROM processes WHERE on_disk = 0;

Current Most CPU Intensive Processes

Another way to hunt down malicious processes is to see which processes are taking up a high percentage of a system's CPU. The following query will pull the process id, user id of who started the process, process path, process name, and finally, the percentage of CPU Utilization the process is taking up.

SELECT pid, uid, name, path, ROUND((
  (user_time + system_time) / (cpu_time.tsb - cpu_time.itsb)
), 2) AS percentage
FROM processes, (
SELECT (
  SUM(user) + SUM(nice) + SUM(system) + SUM(idle) * 1.0) AS tsb,
  SUM(COALESCE(idle, 0)) + SUM(COALESCE(iowait, 0)) AS itsb
  FROM cpu_time
) AS cpu_time
ORDER BY user_time+system_time DESC
LIMIT 5;

PID, UID, Process Name, Process Path, and CPU Percentages Listed

Listing Installed Python Packages + Their Versions

Some Malware depends on certain Python packages to be installed in order for them to work. To narrow down what malware could be running on a system, check which Python packages are currently installed on a system and their versions by using the following query.

select name, version from python_packages;