We demonstrated incident response and investigation using osquery on Windows and Linux endpoints.

Blue Team Study Notes

HackTheBox Certified Defensive Security Analyst (CDSA) Study Notes

Overview

  • Tool: OSQuery, developed by Facebook, is a SQL-based framework used for querying system data to identify Indicators of Compromise (IOCs) and perform threat analysis.
  • Objective: Learn the basics of OSQuery, including querying system tables, analyzing events, and understanding its integration in cybersecurity workflows.

Key Concepts

1. What is OSQuery?

  • A platform for interacting with endpoints (Windows, Linux, macOS) using SQL queries.
  • Not a security service or anti-malware tool but a query framework to analyze system state.
  • Key Use Cases:
    • Threat hunting.
    • Incident response.
    • System and application monitoring.

2. OSQuery Setup

Centralized management can be achieved using a server for multiple endpoints.

Install OSQuery on endpoints (Windows/Linux/macOS).

Command-line interface allows interaction via SQL-like queries.

Basic Commands in OSQuery

  1. Starting OSQuery:
    • Launch interactive shell:
osqueryi

View available commands:

.help

Viewing Tables:

  • List all available tables:
.tables

Querying Tables:

  • Retrieve data from a table:
SELECT * FROM <table_name>;

Example: List installed programs:

SELECT * FROM programs;

Filtering Output:

  • Use SQL syntax to refine results:
SELECT * FROM services WHERE name LIKE 'windef%';

Tasks and Exercises

1. OSQuery Basics

  1. Check OSQuery Version:
    • Command:
.show
  • Output includes OSQuery version and default settings.

Set Output Mode:

  • Change display format:
.mode <format>
  • Example formats: line, pretty.

Exit OSQuery:

  • Use either:
.exit

or

.quit

2. Schema Documentation

  • OSQuery documentation provides details about each table and its fields.
  • Example:
    • Table: services
    • Description: Lists all Windows services.
    • Example Query:
SELECT * FROM services WHERE name LIKE 'windef%';

Practical Use Case

  1. Identify Running Services:
    • Query for specific services:
SELECT * FROM services WHERE name LIKE 'windef%';
  • Output includes service status, start mode, and description.

Extract OSQuery Version:

  • Use the osquery_info table:
SELECT version FROM osquery_info;

Filter Usernames:

  • Query usernames meeting specific criteria:
SELECT username FROM users WHERE LENGTH(username) >= 3 AND username LIKE '%en';

Documentation Insights

  • The official documentation (linked in the room) details table schemas and SQL examples.
  • Filter tables by OS type (Windows/Linux/macOS).

Key Outputs

  1. Installed OSQuery Version:
    • Found in .show output or by querying osquery_info.
  2. Running Services:
    • Extracted via the services table.
  3. User Accounts:
    • Extract usernames and filter using SQL conditions.

Conclusion

  • OSQuery is a powerful tool for querying endpoint data using familiar SQL syntax.
  • It complements security monitoring tools like Splunk or Sysmon for in-depth analysis.
  • The TryHackMe room teaches practical applications for real-world incident response.

TryHackMe Osquery Room Answers

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

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

 

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

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

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

 

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

 

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

Using Osquery, how many programs are installed on this host?
 
Using Osquery, what is the description for the user James?
 

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’;

 

When we run the following search query, what is the Internet Explorer browser extension installed on this machine?

Query: select * from ie_extensions;

 

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%’;

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

 

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

 

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

 

How many services are running on this host?

 

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)?

 

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

 

Video Walkthrough

, ,
Show Comments

MasterMind Study Notes

About the Author

Mastermind Study Notes is a group of talented authors and writers who are experienced and well-versed across different fields. The group is led by, Motasem Hamdan, who is a Cybersecurity content creator and YouTuber.

View Articles