Introducing usql: an interactive Command Line tool for Osquery & Uptycs

If you like a command line and want a way to access osquery data stored in your various Uptycs databases (global | realtime | timemachine), you'll want to install and use usql. usql is written in python and uses the dbcli framework. It functions like osqueryi, giving you the ability to run a query or multiple queries against all enrolled assets in Uptycs.

Motivation

The main intention behind creating this tool was to enable system automation with command line scripting for Uptycs users. We’ve created access to Uptycs SQL interface through the command line and built functionality that is able to do the following:

Provide query output for day to day security/osquery analysis
Ability to run a query or multiple queries against all the enrolled assets in Uptycs
Give access to all the data stored in Uptycs global store
Leverage Uptycs Time Machine capability to explore specific time intervals
Use shell script or python script to define workflow and analyze data
Enable some system automation

Installing usql

Installing usql on your laptop is very easy. If you have python and pip tools installed on your machine, then use the following command:

pip install -U usql

“-U” option is for getting the latest version of usql from PyPi.

Using usql

usql uses Uptycs APIs for sending SQL queries and getting data from Uptycs. With usql, the user experience will be similar to an RDBMS command line interaction. There are some meta-commands which are similar to SQLite, and there are some commands which are identical to PostgreSQL’s meta-commands (like “\dt”, “\d”, “\?”)

In usql, accessing different data sets or tables simply requires picking from a drop-down menu. For example, if you want to access Uptycs’ raw data (global), you can connect to “-D global”. And, if you want to run real time queries against online enrolled machines in Uptycs you can use “-D realtime”. Similarly, you can use the time machine database for Uptycs’ investigate “timemachine.”

Auto-completion for table and column names

usql also has auto-completion capability for table names. As you type a SELECT statement in usql, usql prompts you with possible table names and SQL keywords. Below is a snapshot of the auto-complete feature.

Column names of a table

usql also gives you a way to describe your table (“\d”). “\d” meta-command displays the definition of a table based on the database you are connected to. For example, the processes table in global and timemachine will have extra columns (Uptycs specific columns upt_*). However, in the realtime database, process_events will have raw osquery tables. Below is an example based on process_events table definition:

In Global store:

In Realtime:

Never forget your query history

usql never forgets any SQL queries. Using up and down arrow keys, you can easily explore your session’s history. SQL history always gets carried forward to the database you are connecting to.

Save favorite/frequently used queries

Many of us have a handful of queries we run and re-run regularly for analysis. Re-typing or copy/pasting can be a pain. In usql, using the “\fs” command, allows you to easily save your favorite and frequently used queries. For example, I always check all the enrolled online assets in Uptycs, and I have saved the query as online_assets in usql. Below is a snapshot showing that:

You can also see a list of your saved queries using “\f”

To execute any of the saved SQL queries, you can use “\f < SQL name >” command.

If you want to delete your saved queries, just use command “\fd <saved SQL name>”

Here is a snapshot of other usql commands:

Why usql?

The many features available in usql; exploring tables, table definitions, connecting to a single enrolled machine, executing SQL queries across all enrolled online assets, etc, make it a helpful tool for day to day security analysis. Please explore usql further and leave your comments below.

For more information on the topic, please refer the following link: https://github.com/Uptycs/toolsreadme/blob/master/README.md

Check out more Uptycs tools and integrations here: https://www.uptycs.com/tools-and-integrations

Related osquery resources: