OSINT: Query Your Target System Like A Database

You've been running queries on your data collection for a long time, but what if I told you that your system can also be queried in this manner?

Let's not rush through the dull part: depending on your operating system and distribution, you can only perform select statements on particular tables. Osquery is a cross-platform agent developed by Facebook, that "kind of" convert your system into a relational database where you can ask questions in the syntax of SQL queries. For example, here is a simple query to get a list of username and home directory all the users.

SELECT username, directory
FROM
	users;

Now about installation part, I am leaving that on you. It is pretty well documented on the downloads page. https://osquery.io/downloads/

Interactive vs Daemon Mode

There are two modes to interact with OSQuery: Interactive mode and Daemon mode.

Interactive ( osqueryi ) will spawn an interactive standalone shell which does not communicate with a daemon, and does not need to run as an administrator. It can be used to prototype questions and investigate the present condition of your operating system.

osqueryi (shell) - osquery

osquery

Daemon ( osqueryd ) is the host monitoring daemon that allows you to schedule queries and record OS state changes. It is useful if you are developing an EDR solution to get more out of it like parsed results of OS eventing APIs to record monitored file and directory changes, hardware events, network events, and more.

osqueryd (daemon) - osquery

osquery

In this tutorial, I will go easy and use the interactive shell osqueryi.

Using Interactive Mode

The interactive mode extends SQLite shell, which means it will support some of the commands like .help, .tables and .mode. Let's get started with listing the help text.

Although I can get a list of tables and schema from the osquery website, but why switch to browser again and again when I can do this in the shell itself.

Use the .schema chrome_extensions meta command to list the schema (table description) of the chrome_extensions table.

Now I have all the required information to query name and identifier of the chrome browser extensions.

SELECT identifier as id, name
FROM
	chrome_extensions;

I had trust issues with this tool at first, but once I confirmed the information from osquery with the information from the browser, it was absolutely evident that this thing is a true beast.

Output Modes

If you're wondering how to read this information in the EDR programmatically, the answer is the .help command output I mentioned before. Didn't you notice? Search for CSV within a string.

Yes! You can alter this behaviour to a different format by using .mode FORMAT. Change to csv format to acquire the same information about chrome extensions.

Even if you don't have a TTY shell or interactive shell, you can still get a JSON result for SQL queries by using the --json_pretty parameter followed by the SQL query to execute.

Complex SQL Queries

Now its getting boring to execute simple queries. Allow me to spice it up aggregation and joins 😋. I will be using more tables like programs, users and processes.

Get list of username and description from the users table for all users containing james in the username file

select username, description
from 
	users
where
	username like "%james%";

Get list of process id, name and username the processes running by logged in users. Sort the result by process id in ascending order.

From the logged_in_users table, I got only one user result i.e James

select user 
from 
	logged_in_users 
where 
	type = "active";

Now I must perform a JOIN of processes with users based on the uid field from both databases, use the where clause to select just James username records, and finally order by the pid column.

select pid, name
from 
	processes
join 
	users on users.uid = processes.uid
where 
	users.username = "James"
order by pid asc;

That's all for now folks, happy hunting!