{"id":164227,"date":"2021-01-27T17:00:51","date_gmt":"2021-01-27T14:00:51","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/"},"modified":"2021-01-27T17:00:51","modified_gmt":"2021-01-27T14:00:51","slug":"use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/","title":{"rendered":"#Use SQL and osquery to Interrogate Your Hardware on Linux \u2013 CloudSavvy IT"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a27249a32c31\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a27249a32c31\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_osquery_Application\" >The osquery Application<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#Installing_osquery\" >Installing osquery<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_osquery_Interactive_Shell\" >The osquery Interactive Shell<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#Using_SQL_Commands\" >Using SQL Commands<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#Selecting_Data_From_Tables\" >Selecting Data From Tables<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_users_Table\" >The users Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_processes_Table\" >The processes Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_logged_in_users_Table\" >The logged_in_users Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_os_version_Table\" >The os_version Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_interface_addresses_Table\" >The interface_addresses Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_memory_info_Table\" >The memory_info Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_groups_Table\" >The groups Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_usb_devices_Table\" >The usb_devices Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_deb_packages_Table\" >The deb_packages Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#The_system_info_Table\" >The system_info Table<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/buradabiliyorum.com\/en\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux-cloudsavvy-it\/#Tip_of_the_Iceberg\" >Tip of the Iceberg<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#Use SQL and osquery to Interrogate Your Hardware on Linux \u2013 CloudSavvy IT&#8221;<\/strong><\/p>\n<div id=\"article-content-area\">\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4038\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/f1fee0a0a83b16d260ba2e862cb46eec\/p\/uploads\/2017\/07\/add8ac45.png\" alt=\"Bash Shell\" width=\"1400\" height=\"600\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Do you keep forgetting the syntax for obscure hardware commands you hardly use? The osquery <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>lication lets you interrogate the hardware, users, and performance of your Linux computer with standard SQL commands.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"The_osquery_Application\"><\/span>The osquery Application<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/osquery.io\/\">osquery application<\/a>\u00a0is a free and open-source program from the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/github.com\/osquery\/foundation\">osquery Foundation<\/a>.\u00a0It gathers a tremendous amount of information about your Linux computer and makes it accessible as a pseudo-database. The database contains many tables holding different categories of information. The data in the tables can be retrieved using simple\u00a0<a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/en.wikipedia.org\/wiki\/SQL\">structured query language<\/a> (SQL) commands.<\/p>\n<p>osquery can be used interactively, or it can be controlled through a configuration file and run as a daemon. Running it as a daemon allows you to schedule queries. osquery is a very flexible, sophisticated application. It contains far more than can be covered in an introductory article. The <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/osquery.readthedocs.io\/en\/stable\/\">excellent documentation<\/a> is the first place to turn if you want to investigate its many other options.<\/p>\n<p>We\u2019re going to look at using osquery interactively.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Installing_osquery\"><\/span>Installing osquery<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Most Linux distributions do not include osquery in their official repositories. That doesn\u2019t present a problem, we can download installation packages from the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/osquery.io\/downloads\/official\/4.6.0\">osquery downloads page<\/a>.<\/p>\n<p>For Ubuntu, download the \u201c.deb\u201d package. This will most likely be downloaded to your \u201cDownloads\u201d directory. Change directory to the \u201cDownloads\u201d directory and then use <code>dpkg<\/code> command <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/manpages.ubuntu.com\/manpages\/groovy\/man1\/dpkg.1.html\">to install the \u201c.deb\u201d file<\/a>. Substitute the name of the file in our example with the name of the one you download.<\/p>\n<pre>cd Downloads<\/pre>\n<pre>sudo dpkg -i osquery_4.6.0-1.linux_amd64.deb<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9185\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/bcdd3fef1e69a76064cd2e8b00b824c4\/p\/uploads\/2021\/01\/4a47a0db.png\" alt=\"sudo dpkg -i osquery_4.6.0-1.linux_amd64.deb in a terminal window\" width=\"646\" height=\"97\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For Fedora, <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/osquery.io\/downloads\/official\/4.6.0\">download the \u201c.rpm\u201d file<\/a>. Locate the file on your hard drive and use the <code>rpm<\/code> <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/docs.fedoraproject.org\/en-US\/fedora\/rawhide\/system-administrators-guide\/RPM\/\">package to install it<\/a>. We\u2019re using the <code>-U<\/code> (update option. This installs an application if it is not already installed on your system, and upgrades it if it is.<\/p>\n<pre>sudo rpm -U osquery-4.6.0-1.linux.x86_64.rpm<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9188\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/c2a9c38a24e1ef3f341450a2016ba9bf\/p\/uploads\/2021\/01\/cd34f872.png\" alt=\"sudo rpm -U osquery-4.6.0-1.linux.x86_64.rpm in a terminal window\" width=\"646\" height=\"57\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>On Manjaro, osquery is in the default repositories. we can <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/wiki.manjaro.org\/index.php\/Pacman_Overview\">install it using<\/a>\u00a0<code>pacman<\/code>.<\/p>\n<pre>sudo pacman -Sy osquery<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9190\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/9ba03542ae0c89e9ec48d562da13074f\/p\/uploads\/2021\/01\/a8911d2f.png\" alt=\"sudo pacman -Sy osquery in a terminal window\" width=\"646\" height=\"57\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can test that osquery has installed correctly\u2014and find out the version you\u2019re using\u2014by starting osquery with the <code>--version<\/code> option.\u00a0Note there is an\u00a0 \u201ci\u201d at the end of the command. It\u2019s \u201cosqueryi\u201d not \u201cosquery.\u201d The \u201ci\u201d stands for interactive.<\/p>\n<pre>osqueryi --version<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9191\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/7f3cea3ed31efb6a10cd3a17bc0bbc69\/p\/uploads\/2021\/01\/fb5c81ed.png\" alt=\"osqueryi --version in a terminal window\" width=\"646\" height=\"97\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"The_osquery_Interactive_Shell\"><\/span>The osquery Interactive Shell<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To start osquery in interactive mode, use the <code>osqueryi<\/code> command.<\/p>\n<pre>osqueryi<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9196\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/b4054340b7f286b676623a324bcc816a\/p\/uploads\/2021\/01\/10fb15c7.png\" alt=\"osqueryi in a terminal window\" width=\"646\" height=\"97\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>An osquery interactive shell starts and an \u201cosquery&gt;\u201d prompt appears.<\/p>\n<p>This is where you\u2019ll type the SQL and <em>dot commands<\/em>. Dot commands are commands you issue to interact with the osquery shell. With one or two exceptions dot commands don\u2019t return information about your computer. They get their name from the dot or period \u201c.\u201d that appears at the start of all dot commands.<\/p>\n<p>You can use the \u201cUp Arrow\u201d key to scroll through previously used commands, and tab completion is available for table names.<\/p>\n<p>To get a list of the dot commands and a quick description of each one, use the <code>.help<\/code> command at the \u201cosquery&gt;\u201d prompt.<\/p>\n<pre>.help<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9197\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/361cf1dcbb38c88d55af7406f33b0136\/p\/uploads\/2021\/01\/09dd8c26.png\" alt=\".help in an osquery interactive session\" width=\"646\" height=\"382\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To close the osquery session you can use either <code>.exit<\/code> or <code>.quit<\/code>.<\/p>\n<pre>.quit<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9198\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/7fd8174d504b7199e764f3f39d5c9fc2\/p\/uploads\/2021\/01\/8266e4bf.png\" alt=\"v in an osquery interactive session\" width=\"646\" height=\"77\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Using_SQL_Commands\"><\/span>Using SQL Commands<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>osquery presents the system information it can report on as tables in a database. To be able to interrogate that database you need to know the names of the tables and the fields they each contain. The <code>.tables<\/code> command will list the tables for you.<\/p>\n<pre>.tables<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9210\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/af201f97e766189810dbd33a511348a7\/p\/uploads\/2021\/01\/f19c9085.png\" alt=\".tables in an osquery interactive session\" width=\"646\" height=\"382\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>There\u2019s a lot of tables. You can scroll forward and backward through the list and look for ones that interest you. Let\u2019s say we want to know more about the <code>uptime<\/code> table. The <code>.schema<\/code> command will list a table\u2019s field names and the type of data each field holds.<\/p>\n<pre>.schema uptime<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9211\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/f16e2f8ae64635dfbede40874c1455a9\/p\/uploads\/2021\/01\/9eb9cd58.png\" alt=\".schema uptime in an osquery interactive session\" width=\"646\" height=\"122\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>We know enough now to be able to issue our first SQL statement. We\u2019re going to select the data from the <code>days<\/code>, <code>hours<\/code>, and <code>minutes<\/code> columns from the <code>uptime<\/code> table. Remember this is SQL, so use a semicolon \u201c<code>;<\/code>\u201d to terminate your command.<\/p>\n<pre>select days, hours, minutes from uptime;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9213\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/1ea6ce4f34ed817ac19e097798e7307a\/p\/uploads\/2021\/01\/602e8f04.png\" alt=\"select days, hours, minutes from uptime; in an osquery interactive session\" width=\"646\" height=\"167\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If we want to see all of the columns we can use an asterisk \u201c<code>*<\/code>\u201d as a shortcut representing all the column names. But there\u2019s a dot command that is even quicker. Type <code>.all<\/code>\u00a0followed by the table name to get the same result. (Remember, there\u2019s no semicolon \u201c<code>;<\/code>\u201d at the end of <em>dot<\/em> <em>commands<\/em>.)<\/p>\n<pre>select * from uptime;<\/pre>\n<pre>.all uptime<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9215\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/02e0b3efafba129068e10a7c31ed8047\/p\/uploads\/2021\/01\/7afbb160.png\" alt=\"select * from uptime; in an osquery interactive session\" width=\"646\" height=\"262\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Selecting_Data_From_Tables\"><\/span>Selecting Data From Tables<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"The_users_Table\"><\/span>The users Table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To see a list of all usernames we can query the <code>users<\/code> table.<\/p>\n<pre>select username from users;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9217\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/dc11af29a9190b861a0bdfb5a62fe8d0\/p\/uploads\/2021\/01\/586e508f.png\" alt=\"select username from users; in an osquery interactive session\" width=\"646\" height=\"382\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can sort the data by any column in the results. In this example, we have only a single column but we can still sort the data.<\/p>\n<pre>select username from users order by username;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9218\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/c026642860b8630c515c637d329f06fb\/p\/uploads\/2021\/01\/59b2900a.png\" alt=\"select username from users order by username; in an osquery interactive session\" width=\"646\" height=\"382\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If we want to see a subset of the usernames\u2014those of a particular length, perhaps\u2014we can add more clauses to our SQL statement.<\/p>\n<pre>select username, directory from users where length(username)=4 order by username;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9220\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/99eeac14ef78c8f6d523cc6d0e3ae057\/p\/uploads\/2021\/01\/9eb60bc8.png\" alt=\"select username, directory from users where length(username)=4 order by username; in an osquery interactive session\" width=\"646\" height=\"352\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Perhaps we are only interested in a single username.<\/p>\n<pre>select username, directory, shell from users where username=\"mary\";<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9221\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/a5112b845e7e3522de403c932a4bbac9\/p\/uploads\/2021\/01\/c00b5755.png\" alt=\"select username, directory, shell from users where username=&quot;mary&quot;; in an osquery interactive session\" width=\"646\" height=\"197\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"The_processes_Table\"><\/span>The processes Table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>We can apply the same principles to the data in any table. If we want to find out the details of a <code>gedit<\/code> process, we can search for it like this:<\/p>\n<pre>select pid, name, state, start_time from processes where name=\"gedit\";<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9222\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/63d7c753feeb160d8eb626256c95407f\/p\/uploads\/2021\/01\/7b6fbd4c.png\" alt=\"select pid, name, state, start_time from processes where name=&quot;gedit&quot;; in an osquery interactive session\" width=\"646\" height=\"167\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The \u201cS\u201d in the <code>state<\/code> column means <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/440848\/how-to-run-and-control-background-processes-on-linux\/\">interruptible sleep<\/a>.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"The_logged_in_users_Table\"><\/span>The logged_in_users Table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Instead of looking at the list of configured users like we did before, perhaps we\u2019re more interested in the users who are logged in right now.<\/p>\n<pre>select user, host, time from logged_in_users;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9223\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/36aa5d17bc53bfa1b802bdc2d49c3b7c\/p\/uploads\/2021\/01\/c9baca3c.png\" alt=\"select user, host, time, from logged_in_users; in an osquery interactive session\" width=\"646\" height=\"212\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To exclude non-human users, use the \u201c<code>not like<\/code>\u201d SQL clause. The <code>tty<\/code> column holds the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/428174\/what-is-a-tty-on-linux-and-how-to-use-the-tty-command\/\">details of the user\u2019s tty<\/a> or a tilde \u201c<code>~<\/code>\u201d if no tty is in use.<\/p>\n<pre>select user, host, time from logged_in_users where tty not like \"~\";<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9228\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/fa030bc96e02744f511c72403c842058\/p\/uploads\/2021\/01\/75c168b6.png\" alt=\"select user, host, time from logged_in_users where tty not like &quot;~&quot;; in an osquery interactive session\" width=\"646\" height=\"212\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>We can see that the user with username \u201cdave\u201d is logged in twice.<\/p>\n<p>The \u201c:0\u201d in the <code>host<\/code> column indicates the screen and keyboard connected to the computer. So that user ought to be physically on the premises. The other login session using that username is from another IP address.<\/p>\n<p>That probably needs investigating. How can they be remotely accessing the computer if they\u2019re here in the building? One of these sessions might be fraudulent.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"The_os_version_Table\"><\/span>The os_version Table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To find out details about the Linux version, query the <code>os_version<\/code> table.<\/p>\n<pre>select name, major, minor, codename from os_version;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9225\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/9e86758f45b8fd02865897da5e1a6e1d\/p\/uploads\/2021\/01\/d642f8c3.png\" alt=\"select name, major, minor, codename from os_version; in an osquery interactive session\" width=\"646\" height=\"167\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"The_interface_addresses_Table\"><\/span>The interface_addresses Table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>The <code>interface_addresses<\/code> table holds information about your network interfaces.<\/p>\n<pre>select interface, address from interface_addresses order by interface;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9226\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/5881e392a92a975f32987f21e7d87a15\/p\/uploads\/2021\/01\/1e412544.png\" alt=\"select interface, address from interface_addresses order by interface; in an osquery interactive session\" width=\"646\" height=\"227\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"The_memory_info_Table\"><\/span>The memory_info Table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>The <code>memory_info<\/code> table holds information regarding the amount of memory in your computer and how it is being used.<\/p>\n<pre>select memory_total, memory_free, cached from memory_info;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9227\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/aa41442e02b5c792af6c97d2a02fd832\/p\/uploads\/2021\/01\/ba6beb7a.png\" alt=\"select memory_total, memory_free, cached from memory_info; in an osquery interactive session\" width=\"646\" height=\"167\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"The_groups_Table\"><\/span>The groups Table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To quickly see the configured groups on your system, use the <code>.all<\/code> dot command on the <code>groups<\/code> table.<\/p>\n<p>.all groups<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9232\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/c8c59ad6a74cf1c7bc8113f40f31288a\/p\/uploads\/2021\/01\/079f4fb5.png\" alt=\".all groups in an osquery interactive session\" width=\"646\" height=\"382\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To see the details of the groups associated with users, filter out the groups with a group ID greater or equal to 1000.<\/p>\n<pre>select * from groups where gid&gt;=1000;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9233\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/b77a57d1eeb640bab4552575976984d3\/p\/uploads\/2021\/01\/7134f8f5.png\" alt=\"select * from groups where gid&gt;=1000; in an osquery interactive session\" width=\"646\" height=\"197\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"The_usb_devices_Table\"><\/span>The usb_devices Table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>If a universal serial bus device is not being recognized, you can check this table to see if the operating system is even detecting it.<\/p>\n<pre>select usb_address, usb_port, version, model from usb_devices;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9230\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/76ab146737dddd311aebe06ecc01a209\/p\/uploads\/2021\/01\/7ae5e99a.png\" alt=\"select usb_address, usb_port, version, model from usb_devices; in an osquery interactive session\" width=\"646\" height=\"212\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"The_deb_packages_Table\"><\/span>The deb_packages Table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>The <code>deb_packages<\/code> table holds details of all of the \u201c.deb\u201d software packages that have been installed on your computer. There\u2019s also an <code>rpm_packages<\/code> table for Fedora and other Red Hat derivatives. Other package management systems are not reported on.<\/p>\n<p>There are a lot of packages on this test machine. The results have been limited to 10 records.<\/p>\n<pre>select name, version, source from deb_packages limit 10;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9235\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/ee16f59928d2a911bb7edebc8ecf1a8d\/p\/uploads\/2021\/01\/2484a7df.png\" alt=\"select name, version, source from deb_packages limit 10; in an osquery interactive session\" width=\"646\" height=\"322\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"The_system_info_Table\"><\/span>The system_info Table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>There\u2019s a lot of information packed into the system_info table. We\u2019re just going to verify the brand of <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/en.wikipedia.org\/wiki\/Central_processing_unit\">central processing unit<\/a> (CPU) that is installed in this computer.<\/p>\n<pre>select cpu_brand from system_info;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9236\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/3181ead2bb109d2b49bedc88dc0fe398\/p\/uploads\/2021\/01\/a969aaab.png\" alt=\"select cpu_brand from system_info; in an osquery interactive session\" width=\"646\" height=\"167\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Tip_of_the_Iceberg\"><\/span>Tip of the Iceberg<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>osquery places the information from over 150 tables at your fingertips. Even if you don\u2019t know much SQL, you\u2019ll find it easy to master the little that you need to make productive use of this excellent application.\n<\/p><\/div>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.cloudsavvyit.com\/9184\/use-sql-and-osquery-to-interrogate-your-hardware-on-linux\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#Use SQL and osquery to Interrogate Your Hardware on Linux \u2013 CloudSavvy IT&#8221; Do you keep forgetting the syntax for obscure hardware commands you hardly use? The osquery application lets you interrogate the hardware, users, and performance of your Linux computer with standard SQL commands. The osquery Application The osquery application\u00a0is a free and open-source&#8230;<\/p>\n","protected":false},"author":1,"featured_media":164228,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.cloudsavvyit.com\/p\/uploads\/2017\/07\/add8ac45.png","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-164227","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/164227","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=164227"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/164227\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/164228"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=164227"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=164227"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=164227"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}