Threat Hunting

Threat hunting with osquery

July 31, 2019 by Lester Obbayi


In this article, we take a look at osquery and how it can be used to query the security, reliability and compliance information of systems within your network environment. This is not a complete beginner’s tutorial to cover the basic installation process; rather, it serves as an overview of the capabilities of the tool for security professionals.


For us to bring to perspective the power of osquery, we will need to analyze the activities of a malware sample and look at how various malicious activities such as persistence and the installation of root certificates are achieved. We will also, where necessary, leverage on other tools to support osquery.

Obtaining the malware sample

We will need to obtain a malware sample to work with. In this case, we will be working with the famous Emotet banking Trojan. The way Emotet spreads is by email, where the malicious dropper runs and downloads the virus through a malicious Word macro. The sandbox report detailing the activities of Emotet can be found here. You can also find the VirusTotal malware summary here.

We will create a Windows 7 environment on VirtualBox and intentionally infect it with Emotet. We will then make osquery queries to retrieve the events generated by PowerShell from the powershell_events table. We will also need to enable script block logging in order to read the PowerShell event log channel. You can see how to enable that here.

Understanding the malware activities

Once the malware is run in our sandbox environment, we can view the PowerShell events using the following osquery command: [CLICK IMAGES TO ENLARGE]

select time, script_text from powershell_events;

Figure 1. Exposing PowerShell scripts used during malware execution

The two lines below the PowerShell command above are the script texts that we get once the PowerShell command above gets decoded.

We can also use osquery to log socket connections for each process, performing network communications as shown below:

select, process_open_sockets.remote_address, process_open_sockets.remote_port from process_open_sockets LEFT JOIN processes ON = WHERE process_open_sockets.remote_port != 0 AND != ”;

Figure 2. Exposing processes performing network communication

The query above shows the JOIN statement we used between the process_open_socket and processes tables. As can be seen, PowerShell connects to two remote IPs at port 80.

We can also use a query that identifies anything within the Users directory that was written to disk within the last 100 seconds and after the payload’s download. The following command was used:

select path, size, from file where path like ‘C:Users%%’ and mtime > (select local_time from time) – 100 and filename != ‘.’;

Figure 3. Exposing files written to disk within the last 100 seconds

As you can see, the file squarectx.exe is extracted and executed. We can do a query to identify the running processes, in which we identify the running malicious process. This can be seen below:

select, users.username, processes.path from processes LEFT JOIN users ON processes.uid = users.uid WHERE processes.path != ”;

Figure 4. Identifying the malicious running process

We can also use the same command we used above to query the network connections and see which Command and Control server the malicious process is communicating with. This command and its output are shown below:

Figure 5. Determining the Command and Control in communication

Some malware will attempt to make changes to the registry. In the next section, we will see how to detect these malicious registry changes using osquery.

Detecting persistence activities with osquery

Sometimes malware performs illegal registry operations to achieve persistence. Malware such as the Shrug ransomware create a file within the user’s “temp” directory then adds a new value within the “CurrentVersionRun” registry key. This allows the malware to be executed each time the machine boots up and the user logs in.

We can use osquery to identify the malware executable dropped within the “Users” directory (in the last 100 seconds), using a similar command to the one we used above. We did not infect our system, but the command which can be used for this is shown below:

select path, size from file where path like ‘C:Users%%’ and mtime > (select local_time from time) – 100 and filename != ‘.’;

Figure 6. Identifying any files dropped within the Users directory

We can also use osquery to detect the registry change by querying the “registry” osquery table. We can also query the “startup_items”, which shows all the start-up paths to executables that are executed at system start-up. The command used is shown below:

select source, name, path from startup_items;

Figure 7. Querying start-up items and registry changes

We can also determine the installation properties of malware. If the system had not been infected before, then the malware creates a new key entry within the registry (and with the same name) that will be used to write the necessary instructions for installation. Results here can be compared with “Known Good” to determine the state of the system. We used the following osquery command:

select path, name, type, data from registry where path like ‘HKEY_USERS%%%’ and mtime > (select local_time from time) – 100;

Figure 8. Determining new registry keys and entries within the last 100 seconds

The command above checks to see whether there are any new keys created within the HKEY_USERS hive within the last 100 seconds. Our system is not infected; however, with the Shrug malware, you should be able to see a new “Shrug” key present.

We can also search through the registry table to identify any values that have been added to the registry key. The command below can be used to identify the installation date, victim identifier, key and Initialization Vector (IV).

select path, name, type, data from registry where key like ‘HKEY_USERS%Shrug’;

Before the Shrug ransomware can begin encrypting the user’s files, it communicates with a Command and Control server. As we saw before, it is possible to check the processes communicating on the network to discover this behavior. We can also determine the timestamps of files that have been recently modified within the infected system. The command is shown below:

select path, size from file where path like ‘C:Users%%’ and mtime > (select local_time from time) – 100 and filename != ‘.’;

Figure 9. Discovering file changes through analysing timestamps

Some malware variants are also able to achieve persistence by either creating scheduled tasks or installing new services. Osquery allows us to query the “scheduled_tasks” table. The command to do this is given below:

select name, action, path, enabled, next_run_time from scheduled_tasks;

We can query the installed services using the command below:

select name, display_name, start_type, path, user_account from services;

Figure 10. Querying installed services

Detecting the installation of malicious root certificates with osquery

Osquery can be used to detect maliciously installed root certificates. Attackers can leverage the ability to install root certificates in order to install their own malicious ones, which they can then use to intercept communication or bypass code-signing among various other malicious actions.

The following osquery command can be used to list new certificates within the system:

select common_name, issuer, strftime(‘%d/%m/%y’,datetime(not_valid_after,’unixepoch’)) as expiration_date from certificates where path = ‘CurrentUserTrusted Root Certification Authorities’ ORDER BY common_name;

Figure 11. Determining installed root certificates

There are multiple other queries that you can be able to perform with osquery. However, this section has covered the most basic and common ones.


In this article, we have discussed the various ways in which we can use osquery to analyze malware behavior for threat-hunting purposes. There are numerous other queries that can be utilized in order to determine the state of various operating systems. In case you are interested in these, you can check out the osquery documentation here.



  1. osquery For Security, Medium
  3. New EMOTET Hijacks a Windows API, Evades Sandbox and Analysis, Trend Micro
Posted: July 31, 2019
Articles Author
Lester Obbayi
View Profile

Lester Obbayi is a Cyber Security Consultant with one of the largest Cyber Security Companies in East and Central Africa. He has a deep interest in Cyber Security and spends most of his free time doing freelance Penetration Tests and Vulnerability Assessments for numerous organizations.