Live Query

How to run your own Osquery SQL queries on the Kolide Agent

Antigoni Sinanis avatar
Written by Antigoni Sinanis
Updated over a week ago

Kolide's Live Query allows you to run your own ad-hoc SQL queries and get results from online devices in real time. 

In this article, we will discuss the following:

  • How to get started

  • Setting up Continuous Live Query

  • Limitations and visibility

  • How to disable this feature 

Please note: This is an advanced feature and care should be taken when querying any device in your fleet. While most SQL queries only have a trivial performance impact on a device, it is possible to write queries that negatively impact a device or even return sensitive data. Always test a query on a small set of devices before querying your entire fleet.

Getting Started

To get started, head to your Kolide Dashboard, where you will see the Live Query tab:

Once here, click on "New Query".

You can select which devices you want to run queries on by selecting them in the dropdown menu (Seen here, where it says "5 Devices Targeted"). 

You can search this collection, or select from the list and click "Add to Targets".

To query an entire list of devices, first go to Inventory, and select "Devices" in the left-hand menu. Here, you can select by OS. In this example, we will Live Query "macOS". Click on this, and then click "Live Query", which can be found along the top right of the device list. 

Once you click "Live Query", you will see this window. From here, you can run a pre-selected query. In this example, we will run the default query, "system_info".

Once you are ready, you can click "Save & Run", and watch as the results roll in.
There are four statuses that you may see in this process. 

  • Waiting to send: Waiting for device to check in (e.g. the device is not online)

  • Waiting for results: Device has the query running, and Kolide is just waiting for the results to come back.

  • View Results: Results have successfully been received.

  • Error'd: We got an error message back from the agent when we tried to run the query (the error message will appear a few moments in the same line). 

Continuous Live Query

The Continuous Live Query option allows you to run your queries on a scheduled interval so you can always have the latest data, making Live Query a much more powerful tool for regularly collecting device data your organization cares about.

New Target Selector

When you run queries continuously, it is very important to be able to select devices by their platform so that newly enrolled devices will be targeted by the query in future runs.

In anticipation of Continuous Live Query, we have rebuilt the target selector to now make this group selection possible.

Running Queries Continuously

To run write a query that will run continuously, simply write a new query and press Save/Run. Once you are happy the query returns the data you are looking for, you can click the "Draft" button, and in the modal that appears, select Published under visibility options. This should reveal an option where you can choose the desired continuous interval you would like to run the query.

Additional Published Query Protection

When you publish a Live Query, you allow others on your team to see it. Unfortunately, even well-meaning team members may not realize that when they modify that query, they may be erasing/modifying important information that others rely on.

To help mitigate this, users will now notice the Save & Run button turns orange when either the SQL or targets are modified.

If they click the orange Save & Run button, they will now be presented with a helpful dialog that gives them a number of options that clarifies their intent.

Limitations and Visibility 

Limitations

Please also note that you cannot query Private Devices. They will not show up on any of your targeted devices. 

Visibility 

You can control whether people can see your queries by changing the query visibility from "Draft" to "Published".  Published Queries must have a name. 

All queries will appear in the Audit Log, regardless of whether they are a draft or published. Downloading a CSV will also be recorded in the Audit Log. 

Blocking Osquery Tables

You may not want your teammates to query certain tables because they may contain sensitive information (ex: shell_history) or cause performance issues. 

To control which tables are blocklisted, go to Settings, Device Privacy. There, you can now see a new section called "Blocklisted Osquery Tables".

Once you hit "Save", this item will have a lock next to it in the Live Query documentation sidebar.

How to Disable Live Query

Don't want this feature in your environment, or don't want certain people to access this feature? You can easily disable Live Query for your entire org, or for a select number of teammates. 

To disable the feature completely, start by going to "Settings", and "Device Privacy". 

From here, you will be able to see a new item to check off, which will disable this feature for ALL USERS. Save changes, and Live Query will no longer be present in your web interface. 

Another way to disable this is by user. Again, go to "Settings", and "Teams & Access" to review the list of your teammates.

Select which member(s) you want to restrict by click on the far right menu bubble, and selecting "Edit".

Here, you will want to select "Restricted Access" and specify which items you would like to restrict. In this case, you will check the "Prevent User from using Live Query" and "Update User" to complete this step. 

Questions? Please feel free to contact us by hitting us up on Intercom, or emailing support@kolide.co.

Did this answer your question?