Picture this – you are coming from a database background and getting into the world of IT monitoring or administration. While you are newly warming up to the Linux command line, you have to deal with Windows and Mac machines in your network. Add to that a bunch of Linux servers in your company’s data center.
As a systems administrator, how can you monitor each system’s health, disk space, and metrics? Unfortunately, learning the tools for each OS can be a drag. Many cloud companies offer their dashboard, and those can be helpful, but what about the physical machines in your network? How do you monitor them?
You will need some kind of instrumentation to monitor and take action based on the situation. With big data and high-speed networks and plenty of video-rich accesses, even the terabyte disks can fill up quickly, and you need to take stock of disk overruns, memory, CPU, and network usage. Or in the cybersecurity world, you need to monitor any suspicious activity on your company’s systems.
The Solution – Osquery
For the analysts coming from a database background, why not have a solution that lets you leverage that expertise?
SQL has been the de facto database access method for several decades now. SQL users are proficient with mathematical functions like AVG, COUNT, MAX, MIN and would prefer to get most OS metrics that way to sort and ferret out information. Even the LIMIT and ORDER BY are useful. Of course, SQL JOINs help collate two unrelated tables in which only a few columns overlap.
Osquery is your solution. It started as a tool developed at Facebook and sits in an OS as a high-performance relational database. This allows you to write SQL queries to explore operating system data. With osquery, SQL tables represent abstract concepts such as running processes, loaded kernel modules, open network connections, browser plugins, hardware events, or file hashes.
Structured query language, aka SQL, has been around for ages. The idea that you can represent OS assets and continuously changing events, like user logins and processes as SQL tables, is a novel approach in the security landscape.
But osquery does not apply just to security folks; it is a drop-in replacement for several Linux shell commands like <em>top</em>, which shows memory usage with a sorted list of processes, and <em>sockstat</em>, which shows open sockets and so on.
In this introductory article, we are exploring just the top-level benefits of using and installing osquery in a manner that opens your mind to new possibilities of OS monitoring, management, and security assessment.
Osquery cannot suffice for threat management. It is a looking glass to inspect your system’s health, monitor processes, memory and disk usage, and several other things. Osquery data can be added to your elasticsearch, Splunk, or additional log monitoring solution, or simply into some cloud-based storage like S3 such that it analyzes for threats by other tools. While that centralized storage can provide historical lookback, there is value in live-querying to get the system’s current state. That’s what you would need when there is a live-attack in progress.
Osquery – how to get your feet wet
Let us try out some commands.
All of us in the IT industry know a thing or two about SQL queries. If you know SQL and worked on a simple database like SQLite, then these examples would look even more familiar. SQL supports several refinements to common queries like select, which, when combined with OS-level data like browser plugins or loaded kernel modules, gives a lot of critical insights that are otherwise difficult to obtain even with the rich command-line Linux.
osquery> select * from users;
osquery> select pid, name, uid, resident_size from processes order by resident_size desc limit 10;
+-------+-----------------+------+---------------+
| pid | name | uid | resident_size |
+-------+-----------------+------+---------------+
| 3122 | chromium-browse | 1000 | 649736000 |
| 3462 | chromium-browse | 1000 | 319948000 |
| 2685 | skypeforlinux | 1000 | 238428000 |
| 10401 | chromium-browse | 1000 | 231392000 |
| 10368 | chromium-browse | 1000 | 226796000 |
| 1803 | gnome-shell | 1000 | 204544000 |
| 3576 | chromium-browse | 1000 | 189828000 |
| 3339 | chromium-browse | 1000 | 187936000 |
| 10468 | chromium-browse | 1000 | 146396000 |
| 2326 | skypeforlinux | 1000 | 145492000 |
+-------+-----------------+------+---------------+
osquery> select * from listening_ports where port != 0;
+------+-------+----------+--------+---------------+-----+--------+------+---------------+
| pid | port | protocol | family | address | fd | socket | path | net_namespace |
+------+-------+----------+--------+---------------+-----+--------+------+---------------+
| -1 | 53 | 6 | 2 | 192.168.122.1 | -1 | 41060 | | 0 |
| -1 | 53 | 6 | 2 | 127.0.0.53 | -1 | 28923 | | 0 |
| -1 | 21 | 6 | 2 | 0.0.0.0 | -1 | 26917 | | 0 |
| -1 | 22 | 6 | 2 | 0.0.0.0 | -1 | 41784 | | 0 |
| -1 | 631 | 6 | 2 | 127.0.0.1 | -1 | 78255 | | 0 |
| -1 | 5432 | 6 | 2 | 127.0.0.1 | -1 | 26236 | | 0 |
| -1 | 9050 | 6 | 2 | 127.0.0.1 | -1 | 31703 | | 0 |
| -1 | 7070 | 6 | 2 | 0.0.0.0 | -1 | 32698 | | 0 |
| -1 | 993 | 6 | 2 | 0.0.0.0 | -1 | 32184 | | 0 |
1992 |
| -1 | 5432 | 6 | 2 | 127.0.0.1 | -1 | 26236 | | 4026531992 |
| -1 | 9050 | 6 | 2 | 127.0.0.1 | -1 | 31703 | | 4026531992 |
| -1 | 7070 | 6 | 2 | 0.0.0.0 | -1 | 32698 | | 4026531992 |The above are basic but handy examples. You can certainly combine the SQL count(*) command or the order by or limit clauses to refine further queries and get some compelling outcomes.
And you can use the <em>.schema</em> and .tables inside the osquery> shell interface to play around with the full possibilities of osquery and what it can do for you.
There are several open-source projects on Github and an active community around osquery. You can configure Packs – a collection of predefined queries for a purpose like threat monitoring – and a config file to run those packs at intervals. You can inspect syslogs, loaded kernel modules, even look at email logs, and so on.
The possibility of using osquery for obtaining insight into your server health and characteristics is just mind-boggling, and this article merely scratches the surface.
In my system, the <strong>.tables</strong> command returns this:
=> acpi_tables
=> apparmor_profiles
=> apt_sources
=> arp_cache
=> atom_packages
=> augeas
=> authorized_keys
=> azure_instance_metadata
=> azure_instance_tags
=> block_devices
=> carbon_black_info
=> carves
=> chrome_extension_content_scripts
=> chrome_extensions
=> cpu_time
=> cpuid
=> crontab
=> curl
=> curl_certificate
=> deb_packages
=> device_file
=> device_hash
=> device_partitions
=> disk_encryption
=> dns_resolvers
=> docker_container_fs_changes
=> docker_container_labels
=> docker_container_mounts
=> docker_container_networks
=> docker_container_ports
=> docker_container_processes
=> docker_container_stats
=> docker_containers
=> docker_image_labels
=> docker_image_layers
=> docker_images
=> docker_info
=> docker_network_labels
=> docker_networks
=> docker_version
=> docker_volume_labels
=> docker_volumes
=> ec2_instance_metadata
=> ec2_instance_tags
=> elf_dynamic
=> elf_info
=> elf_sections
=> elf_segments
=> elf_symbols
=> etc_hosts
=> etc_protocols
=> etc_services
=> file
=> file_events
=> firefox_addons
=> groups
=> hardware_events
=> hash
=> intel_me_info
=> interface_addresses
=> interface_details
=> interface_ipv6
=> iptables
=> kernel_info
=> kernel_modules
=> known_hosts
=> last
=> listening_ports
=> lldp_neighbors
=> load_average
=> logged_in_users
=> lxd_certificates
=> lxd_cluster
=> lxd_cluster_members
=> lxd_images
=> lxd_instance_config
=> lxd_instance_devices
=> lxd_instances
=> lxd_networks
=> lxd_storage_pools
=> magic
=> md_devices
=> md_drives
=> md_personalities
=> memory_array_mapped_addresses
=> memory_arrays
=> memory_device_mapped_addresses
=> memory_devices
=> memory_error_info
=> memory_info
=> memory_map
=> mounts
=> msr
=> npm_packages
=> oem_strings
=> opera_extensions
=> os_version
=> osquery_events
=> osquery_extensions
=> osquery_flags
=> osquery_info
=> osquery_packs
=> osquery_registry
=> osquery_schedule
=> pci_devices
=> platform_info
=> portage_keywords
=> portage_packages
=> portage_use
=> process_envs
=> process_events
=> process_file_events
=> process_memory_map
=> process_namespaces
=> process_open_files
=> process_open_pipes
=> process_open_sockets
=> processes
=> prometheus_metrics
=> python_packages
=> routes
=> rpm_package_files
=> rpm_packages
=> selinux_events
=> selinux_settings
=> shadow
=> shared_memory
=> shell_history
=> smart_drive_info
=> smbios_tables
=> socket_events
=> ssh_configs
=> startup_items
=> sudoers
=> suid_bin
=> syslog_events
=> system_controls
=> system_info
=> time
=> ulimit_info
=> uptime
=> usb_devices
=> user_events
=> user_groups
=> user_ssh_keys
=> users
=> yara
=> yara_events
=> yum_sources
Conclusion
In terms of being able to find out exactly what we want from a network or node, and to act quickly based on unique situations that can arise from time to time, a sys admin‘s best help can be osquery. But you cannot count on this tool alone since this is only a snapshot system and not a live program that runs. Alongside other security tools you can add this to the mix to ease your life. In terms of learning-curve this can be significantly short since most sys admins are very good at SQL anyway.
Prefer English vs. SQL?
While SQL is excellent, wouldn’t it be helpful to use English vs. SQL for many of the common questions you would want answered from your osquery infrastructure? Bit of a shameless plug, but that’s what Query.AI can provide on top of osquery and other tools in your infrastructure. Also, you get to query your devices and tools centrally from your browser.
Did you enjoy this content? Follow our linkedin page!
