TryHackMe: Osquery: The Basics Complete Walkthrough (SOC Level 1)

Welcome to this walkthrough of the OSquery Room on TryHackMe. Let’s cover the basics of Osquery, a Osquery is an open-source agent created by Facebook in 2014. It converts the operating system into a relational database, which allows us to query data with SQL.

Room URL:
https://tryhackme.com/room/osqueryf8

I am making these walkthroughs to keep myself motivated to learn cyber security, and ensure that I remember the knowledge gained by these challenges on HTB and THM. Join me on learning cyber security. I will try and explain concepts as I go, to differentiate myself from other walkthroughs.

Task 1: Introduction

Osquery is an open-source tool developed by Facebook in 2014 that transforms an operating system into a relational database, allowing users to query system data using SQL. It is useful for security roles like analysts, incident responders, and threat hunters, and works across Windows, Linux, macOS, and FreeBSD.

Learning Objectives in this introduction:

• Understand what Osquery is and the problem it addresses
• Learn how to use Osquery in interactive mode
• Use SQL queries to explore system data
• Learn to join tables to extract combined information

Note: SQL knowledge is helpful for using Osquery effectively.

Questions

Move on to the next task

Answer: No answer needed

Task 2: Connect with the Lab

The virtual machine attached to this room already has Osquery installed and configured for you on Windows and Linux. Before proceeding, start the attached VM and use the following credentials to connect. The VM will be accessible in the split screen on the right side. In case the VM is not visible, use the blue Show Split View button at the top-right of the page.

Click on the powershell terminal pinned at the taskbar and enter osqueryi to enter the interactive mode of osquery.

Machine IP: MACHINE_IP

Username: James

Password: thm_4n6

Questions

Connect with the Lab.

Answer: No answer needed

Task 3: Osquery: Interactive Mode

Getting Started with Osquery Interactive Mode

• Launch Osquery’s interactive shell by running osqueryi in the terminal.
• Use .help to list available meta-commands for navigating and interacting with the tool.

Useful Meta-Commands

• .tables – Lists all available tables.
  • Example: .tables user lists all tables with “user” in their name.
• .schema table_name – Displays the schema (columns and data types) of a table.
  • Example: .schema users shows columns like uid, username, directory, etc.

Querying Data

• Use SQL syntax to retrieve data:
  SELECT column1, column2 FROM table;
  • Example:
    SELECT gid, uid, description, username, directory FROM users;

Display Modes

• Output formatting can be adjusted using .mode, such as:
  • csv, column, line, list, pretty (default)

Tip: For full reference, check Osquery’s online schema API for a complete list of tables, columns, and descriptions.

Questions

How many tables are returned when we query “table process” in the interactive mode of Osquery?

Open up a terminal and run osqueryi to start Osquery. Afterwards run:

.table process

There are three results.

Answer: 3

Looking at the schema of the processes table, which column displays the process id for the particular process?

To show the schema of the processes table, we run:

.schema processes

Here we can see a column called pid, which must mean process ID.

Answer: pid

Examine the .help command, how many output display modes are available for the .mode command?

Simply run the help command:

.help

There are 5 output display modes for the .mode command.

Answer: 5

Task 4: Schema Documentation

We can look up the schema documentation at https://osquery.io/schema/5.5.1. Here we can see the different tables we can query, and the columns each includes.

Here are the main points:

• Version Selector: A dropdown lets you choose different versions of Osquery to view their respective schema tables.
• Table Count: Displays the total number of tables for the selected version (e.g., 106 tables).
• Alphabetical Table List: All tables are listed in alphabetical order, similar to using the .table command in interactive mode.
• Table Info:
  • Each table includes a name and brief description.
  • A detailed chart shows the table’s columns, data types, and descriptions.
• OS Applicability:
  • Indicates which Operating Systems (OS) each table applies to (e.g., account_policy_data is macOS-only).
  • You can filter tables by OS using a multi-select dropdown.
• Supported OS List: Displays all OSes supported by Osquery.

With this layout, you can confidently explore Osquery schemas and find the information you need.

Questions

In Osquery version 5.5.1, how many common tables are returned, when we select both Linux and Window Operating system?

Simply open the documentation page:

https://osquery.io/schema/5.5.1

The version should be set to 5.5.1 so all we have to do is select both Linux and Windows on the table compatibility dropdown, as shown below.

There are 56 common tables.

Answer: 56

In Osquery version 5.5.1, how many tables for MAC OS are available?

Simply select MAC OS instead of Windows and Mac, and you should see 180 tables.

Answer: 180

In the Windows Operating system, which table is used to display the installed programs?

Select the Windows Operating system. Now have a scroll through the list of tables on the left side of the screen. You should see a table called programs.

Answer: programs

In Windows Operating system, which column contains the registry value within the registry table?

Once more, look at the table list. Luckily, the names are pretty descriptive, so the table is called registry.

The tables includes a data column, which contains the data contents of registry values.

Answer: data

Task 5: Creating SQL queries

Osquery SQL Overview:

• Osquery uses a SQL-like language based on a superset of SQLite, primarily for querying endpoint data.
• Most queries use the SELECT statement since Osquery is read-only by default.
• Modifying commands (UPDATE, DELETE) are only usable with run-time tables or supported extensions.

Basic SQL Structure in Osquery:

• Queries generally include:
  • A SELECT statement
  • A FROM clause
  • A semicolon (;) at the end
• Example to get all data from the programs table:
  SELECT * FROM programs LIMIT 1;

Selecting Specific Data:

• You can limit output to relevant columns:
  SELECT name, version, install_location, install_date FROM programs LIMIT 1;

Counting Entries:

• Use count(*) to get total entries:
  SELECT count(*) FROM programs;

Filtering with WHERE:

• Narrow results using WHERE:
  SELECT * FROM users WHERE username='James';
• Supports various operators like =, <>, >, BETWEEN, LIKE, %, and _.

Wildcards in Paths:

• %: one level match
• %%: recursive match
• Example:
  /Users/%/Library/%% → recursively monitors all user Library folders

Required WHERE Clause:

• Some tables (like file) require a WHERE clause. Otherwise, an error is returned.

Joining Tables:

• You can join tables on common fields. Example:

SELECT p.pid, p.name, p.path, u.username FROM processes p JOIN users u ON u.uid = p.uid LIMIT 10;

Questions

Using Osquery, how many programs are installed on this host?

Easy one if you know SQL. It is actually one of the examples from the theory. Simply use the following command:

SELECT count(*) from programs;

Answer: 19

Using Osquery, what is the description for the user James?

There are quite some ways to do this, but I simply selected the username & description columns from the users table:

SELECT username, description FROM users;

--Alternatively filter on name
SELECT username, description FROM users WHERE username = 'James';

Answer: Creative Artist

When we run the following search query, what is the full SID of the user with RID ‘1009’? Query: select path, key, name from registry where key = ‘HKEY_USERS’;

Simply run the query as mentioned in the question.

Look for the user for which the path ends with 1009, and find the corresponding name.

Answer: S-1-5-21-1966530601-3185510712-10604624-1009

When we run the following search query, what is the Internet Explorer browser extension installed on this machine Query: select * from ie_extensions;

Simply run the query again.

The answer expects the path of this Url Search Hook extension.

Answer: C:\Windows\System32\ieframe.dll |

After running the following query, what is the full name of the program returned? Query: select name,install_location from programs where name LIKE ‘%wireshark%’;

Once more, run the query. The answer is the name of the program returned: Wireshark 3.6.8 64-bit.

Answer: Wireshark 3.6.8 64-bit

Task 6: Challenge and Conclusion

Now that we have explored various tables, learned how to create search queries, and ask questions from the operating system, it’s time for a challenge. Use OSquery to examine the host and answer the following questions.

Questions

Which table stores the evidence of process execution in Windows OS?

You can use the .tables command for this, but I recommend look at the documentation again for Windows:

https://osquery.io/schema/5.5.1

I will be honest, this took me a long time to find. I first thought it was processes, but nope. After some searching and guessing I found out it was userassist.

userassist

UserAssist Registry Key tracks when a user executes an application from Windows Explorer.Improve this Description on Github

Answer: userassist

One of the users seems to have executed a program to remove traces from the disk; what is the name of that program?

For this we can simply select all rows for the earlier found tables.

SELECT * FROM userassist;

It took me some patience, but in the above screenshot, on the second to last line you will see the DiskWipe.exe process.

Answer: DiskWipe.exe

Create a search query to identify the VPN installed on this host. What is name of the software?

Take a look at the documentation again, and you should find the table called programs.

https://osquery.io/schema/5.5.1/#programs

Time for some SQL ninja skills using the LIKE operator to find all rows which include VPN in its name.

SELECT * FROM programs WHERE name LIKE '%VPN%';

Here we find ProtonVPN.

Answer: ProtonVPN

How many services are running on this host?

For this we can use the COUNT(*) operator to count all rows in the services table.

SELECT COUNT(*) FROM services;

Answer: 214

A table autoexec contains the list of executables that are automatically executed on the target machine. There seems to be a batch file that runs automatically. What is the name of that batch file (with the extension .bat)?

Time for another WHERE clause:

SELECT * FROM autoexec WHERE name LIKE '%.bat';

Answer: batstartup.bat

What is the full path of the batch file found in the above question? (Last in the List)

See above.

Answer: C:\Users\James\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup\batstartup.bat

Congratulations on completing Osquery!!!

Congratulations on completing Osquery: The Basics. I really enjoyed this quick room, as I have a background as data engineer. Therefore, writing these SQL queries felt very natural to me. Quite a cool technology this Osquery!

Come back soon for more walkthroughs of rooms on TryHackMe and HackTheBox, and other Cybersecurity discussions.

Find more of my walkthroughs here.

Like my articles?

You are welcome to comment on this post, or share my post with friends. I would be even more grateful if you support me by buying me a cup of coffee:

I learned a lot through HackTheBox’s Academy. If you want to sign up, you can get extra cubes, and support me in the process, if you use the following link:

https://referral.hackthebox.com/mzwwXlg