Osquery — part II

Demonstration of Osquery

Practice makes a man perfect. Doing a lot of practice could help to sort out many things. In this blog i’m going to demonstrate a few example for osquery. As said earlier osquery is an opensource which aids to converts operating system into relational database. Using osquery we could explore information like process running, number of users, network connectivity & communication among anonymous link. If you’re newbie kindly gothrough the osquery blog which i shared earlier.

Let’s divein. Before starting demo, we should install osquery repo into our system.

Installation of Osquery

Here i’ve mentioned steps to install osquery for Debian or Ubuntu based systems.

sudo apt-get upgrade

Use the above command to upgrade the repos and followed by the below command to install osquery.

sudo apt-get install osquery

If everything goes well then we can start interacting with osquery.

use osqueryi command to load interactive shell.

osqueryi

In osquery meta commands are fixed with .(period).

Eg : .Table is a command used to view the table information.

Table Schema

Single information is not a perfect solution for a problem, To sort out that, we need as much of information. Without querying is there any other way to gather information what the table contains. Table schema is used to view the structure of the table, aids to view table column and data types.Table names are not sufficient to know what data it contains.

Use the following command to view table structure

.schema table name

The result provides column name such as pid, description and it’s data types like integer, bigint, text and so on.

List of information helps to find schema documentation

1. A dropdown lists shows versions of Osquery. We can Choose the version of Osquery as our wish.
2. The number of tables also displayed for the selected version in osquery.
3. The list of tables would listed in alphabetical order for the selected version of Osquery.
4. The name of the table and brief description.
5. A detailed chart showing each table’s column, type, and description.
6. Information to which Operating System the table applies. (In the above image, the account_policy_data table is available only for macOS)
7. A dropdown menu to select the Operating System of choice. We can choose multiple Operating Systems, which will display the tables available for those Operating systems.

Here i’ve enclosed a screenshot of osquery schema documentation. As shown on above picture you can choose the platform version, based on that number of tables will display on left pane.

Limit

Based on the query it fetch all the revelant data’s and display it as an output. Osquery is not an entire sql, but it’s an superset of sqlite. We can set a limit and fetch the data as we need.

for an example if you’re searching for any table, and queried , it displays all the tables stored on the system and it goes on which is difficult to find the process we need. To avoid this you can set a limit and search for it.

Use the below query and set the limit as your wish and the output displayed.

SELECT * FROM program LIMIT 1;

On the above i set the limit to 1 to display the programs, so it displays only one program.

Display Modes

It comes with numerous display modes to select. We can use .help option to view all the modes. As shown in the screenshot you can choose the mode as your wish.

Thanks for Reading…