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.
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 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.
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:
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:
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:
Vibhor is the Director of Professional Services at Uptycs, responsible for ensuring the ongoing success of our customers. He speaks and dreams in SQL. Vibhor holds a Bachelor's Degree in Computer Science and Master's Degree in Computer Application.
Other posts you might be interested in
4 min read | March 22, 2018
How to unistall osquery from macOS in 4 steps [Video]Read More
11 min read | June 14, 2018
Osquery in action: Where and when to apply "threat intel"Read More
4 min read | August 15, 2019