How to Install and Use Osquery in Ubuntu

Osquery is an open source and cross platform software utility which can be used to expose an operating system as a relational database. We can get data from the operating system by running SQL based queries. In this blog we will see how to install Osquery in Ubuntu and how to use it to get data from the operating system.

Installing Osquery in Ubuntu

Osquery packages are not available in the default Ubuntu repository so before installing it we have to add the Osquery apt repository by running the following command in the terminal.

ubuntu@ubuntu:~$ echo "deb [arch=amd64] https://pkg.osquery.io/deb deb main" |
sudo tee /etc/apt/sources.list.d/osquery.list

Now we will import the signing key by running the following command in the terminal.

ubuntu@ubuntu:~$ sudo apt-key adv --keyserver keyserver.ubuntu.com
--recv-keys 1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B

After importing the signing key, now update your system by running the following command in the terminal.

ubuntu@ubuntu:~$ sudo apt-get update

Now install Osquery by running the following command

ubuntu@ubuntu:~$ sudo apt-get install osquery

After installing Osquery, now we have to check whether it has been installed correctly by running the following command

ubuntu@ubuntu:~$ osqueryi --version

If it gives the following output then it is installed correctly

Using Osquery

Now after installing, we are set ready to use Osquery. Run the following command to go to interactive shell prompt

ubuntu@ubuntu:~$ osqueryi

Getting Help

Now we can run SQL based queries to get data from the operating system. We can get help about Osquery by running the following command in the interactive shell.

osquery> .help

Getting All the Tables

As mentioned earlier, Osquery exposes data from the operating system as a relational database so it has all the data in the form of tables. We can get all the tables by running the following command in the interactive shell

osquery> .tables

As we can see that by running the above command we can get a bunch of tables. Now we can get data from these tables by running SQL based queries.

Listing Information About all the Users

We can see all the information about users by running the following command in the interactive shell

osquery> SELECT * FROM users;

The above command will display gid, uid, description etc. of all the users

We can also extract only the relevant data about users for example we want to see only the users and not other information about users. Run the following command in the interactive shell to get the user names

osquery> SELECT username FROM users;

The above command will show all the users in your system

Similarly we can get usernames along with the directory in which the user exists by running the following command.

osquery> SELECT username, directory FROM users;

Similarly we can query as many fields as we want by running the similar commands.

We can also get all the data of specific users. For example we want to get all the information about the root user. We can get all the information about the root user by running the following command.

osquery> SELECT * FROM users WHERE username="root";

We can also get specific data from specific fields (columns). For example we want to get the group id and username of the root user. Run the following command to get this data.

osquery> SELECT username, gid FROM users WHERE username=”root”

In this way we can query anything we want from a table.

Listing all The Processes

We can list first five processes running in ubuntu by running the following command in the interactive shell

osquery> SELECT * FROM processes LIMIT 5;

As there are many processes running in the system so we have displayed only five processes by using LIMIT keyword.

We can find the process id of a specific process for example we want to find the process id of mongodb so we will run the following command in the interactive shell

osquery> SELECT pid FROM processes WHERE name="mongod";

Finding Version of Ubuntu

We can find the version of our Ubuntu System by running the following command in the interactive shell

osquery> SELECT * FROM os_version;

It will show us the version of our operating system

Checking Network Interfaces and IP Addresses

We can check the IP address, Subnet Mask of Network Interfaces by running the following query in the interactive shell.

osquery> SELECT interface,address,mask FROM interface_addresses
WHERE interface NOT LIKE '%lo%';

Checking Logged in Users

We can also check logged in users on your system by querying data from the ‘logged_in_users’ table. Run the following command to find logged in users.

osquery> SELECT user,host,time FROM logged_in_users WHERE tty NOT LIKE '-';

Checking System Memory

We can also check Total memory, free memory cached memory etc. by running some SQL based command in the interactive shell. To check total memory run the following command. This will give us total memory of the system in bytes.

osquery> SELECT memory_total FROM memory_info;

To check free memory of your system run the following query in the interactive shell

osquery> SELECT memory_free FROM memory_info;

When we run the above command, it will give us free memory available in our system

We can also check the cached memory of the system using memory_info table by running the following query.

osquery> select cached from memory_info;

Listing the Groups

We can find all the groups in your system by running the following query in the interactive shell

osquery> SELECT * FROM groups;

Displaying Listening Ports

We can display all the listening ports of our system by running the following command in the interactive shell

osquery> SELECT * FROM listening_ports;

We can also check whether a port is listening or not by running the following command in the interactive shell

osquery> SELECT port, address FROM listening_ports WHERE port=27017;

This will give us output as shown in the following figure

Conclusion

Osquery is a very useful software utility to find any kind of information about your system. If you are already aware of SQL based queries then it is very easy to use for you or if you are not aware of SQL based queries then I have tried my best to show you some major queries which are useful to find data. You can find any kind of data from any table by running similar queries.