Read Part 1 of the series here.
This blog is the 2nd of a 3-part series on why automation is necessary for cybersecurity compliance in modern cloud environments. This blog posts supplement the material presented in #2 of the free webcast series:
- Why You Need Automation to Achieve Compliance in the Cloud
- Leveraging OSQuery for Compliance - A Recovering Auditor’s Perspective
- An Easier Way to Multi-cloud, Multi-account Cloud Compliance
A fundamental tenet we discussed in part 1 of this series was that compliance professionals should live off the land and use ubiquitous tools to facilitate compliance checks. By using tools that your administrators are using, compliance checks will be completed by those same administrators. This makes compliance professionals more efficient - and probably more liked by their co-workers in the operations and engineering departments.
In part 2 of this series, we are diving into osQuery from a compliance perspective.
What is OSQuery?
According to the osquery docs page,
"osquery is an operating system instrumentation framework for Windows, OS X (macOS), Linux, and FreeBSD. The tools make low-level operating system analytics and monitoring both performant and intuitive.
osquery exposes an operating system 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."
There are some critical aspects of the definition above that are essential to understand and dive into. The first aspect of osquery that makes this a robust security and compliance tool is that it works on Windows, OS X, Linux, and FreeBSD. The cross-platform capabilities of osquery make the tool valuable in modern environments. In my career, I have performed hundreds (maybe thousands?) of cybersecurity audits and assessments. I can confidently say that I have never seen an organization that exclusively uses one type of operating system. Generally, you come across a mix of Linux, Windows, or macOS, making compliance activities difficult. osquery takes that complexity away by allowing you to write queries across different operating systems to obtain relevant security and compliance data. Modern organizations require solutions that are operating system agnostic, and osquery accomplishes this.
Secondly, osquery exposes the operating system as a high-performance relational database that allows administrators to write SQL queries to obtain valuable data. Even if you're not a SQL expert, it is easy to start and begin writing queries quickly. Everything you do with osquery is centered around SQL and its expressive nature to gather endpoint data rapidly and efficiently. In the webinar linked above, I review and demo some of the basics of osquery that can help you get started.
There are two ways to run osquery once you have it installed on your system. The first is using the interactive SQL console - osqueryi - to run ad hoc queries locally on a system. You run this at the command line on the system. For example, incident handlers use this frequently to conduct ad hoc forensics or incident response activities. The screenshots below are from the osqueryi interactive shell. The second way to run osquery is using the osquery daemon, which allows you to run queries against remote systems. This is useful if you have many systems and would like to run and schedule the same queries against multiple systems at once. In addition, this daemon (or service on Unix) accepts remote requests, which is very useful for enterprise compliance at scale.
So What Does This Mean for Compliance?
As of writing this blog post, there are over 275 tables available for security professionals to explore. That's a lot of tables! The cool thing about osquery is that it is open-source, and there is a massive community behind its development and growth. There are a few valuable tables that we have found to be useful for compliance activities and data gathering.
As we explore some of the tables and queries below, we will outline the schema of each table, an example query, and example results. During the webinar and in SEC557, we dive deeper into writing queries and utilizing osquery in your enterprise environment.
As mentioned above, one of the great benefits of osquery is running the same queries against different operating systems. As of writing this blog, there are 50 cross-platform tables available for security professionals. In addition, there are a few tables we found helpful for compliance activities:
OS Version Table
The OS version table allows you to obtain information about the installed operating system, including helpful information such as install date, build number, codename.
Users, Groups, and user_groups
The users and groups tables contain information about local users and groups. The users' table contains information and domain accounts that have logged on locally on Windows systems. The user_groups table map local users into local groups, outlining user group relationships and performing SQL JOIN operations on the table.
The listening ports table outlines processes with listening network sockets and ports. This table exposes open TCP and UDP ports as well as the process ID. Providing the process ID lets you check your processes table and gather additional information about the processes running.
The file table allows you to access files on the filesystem host. This table is great because it contains many metadata attributes that can be utilized when building queries. However, the file table does require a WHERE clause, so you need to know where the file is before you query it. It is not practical or efficient for osquery to search every directory and subdirectory on the system.
Currently, there are 102 Windows-specific tables available for security professionals. However, the below tables can be helpful to compliance professionals for audits and ongoing monitoring of their environments:
The programs table includes information on programs installed on the system, including helpful information such as the version and install_date. This data is valuable in performing a compliance check against the CIS Critical Security Control 2 Inventory and Control of Software Assets. In SEC557, we use Powershell as our language of choice for compliance automation. Powershell is great, but a few osquery tables provide similar or more robust data than the equivalent Powershell query. For example, the 'Get-CIMInstance win32_product' query does not provide as many programs as the programs table does on osquery. It's helpful to understand these limitations and offer multiple options to administrators performing these compliance checks. Some fields within this table may be blank as Windows installation details are left to the discretion of the author of the software product.
The patches table includes all the patches applied to the system. This table provides equivalent results of the 'Get-Hotfix' Powershell cmdlet that we covered in this series's part 1 blog post. For compliance, you may need to prove that you have a particular patch installed on systems. For example, you could conduct a query of all systems that are missing a specific patch installed. There is a limitation here as this table does not include patches applied via MSI or downloaded from Windows Update (e.g., Service Packs).
The services table provides details of all installed Windows services and their relevant data, including the process ID, path, start_type, and much more. This table offers roughly the same results as the 'Get-Service' cmdlet from Powershell.
Lastly, we have a whopping 157 Linux-specific tables available for security professionals. That's a lot of tables to explore. However, the below tables are a few that we find extremely useful for compliance data gathering activities:
The kernel_info table includes basic active kernel information, including the kernel version.
The kernel_modules table includes data about installed and running kernel modules that are both loaded and within the load search path.
The disk encryption table includes disk encryption status and information.
The suid_bin table contains information about executables with the SUID and SGID (privilege escalation) bit set. This is a beneficial compliance check when determining if an organization has implemented the principle of least privilege.
Compliance professionals should leverage osquery to help automate compliance in enterprise and cloud environments. Understanding the tools your administrators use to perform security tasks enables compliance professionals to speak the same language as the rest of the security organization. A core tenet of the automating compliance philosophy in SEC557 is living off the land and using ubiquitous tools to perform compliance checks. This mindset puts compliance professionals on the same playing field as their counterparts in the organization implementing agile and DevSecOps principles.
About The Author
AJ Yawn is Co-Founder and CEO at ByteChek and a Founding Board Member of the National Association of Black Compliance and Risk Management Professionals (NABCRMP). AJ has earned 6 AWS certifications including the AWS Solutions Architect-Professional and AWS Security-Specialty. Prior to ByteChek, AJ spent over a decade in the cybersecurity industry both in the US Army and as a consultant. He is a regular speaker at SANS Cloud Security curriculum events such as BIPOC in Cloud Forum and CloudSecNext Summit, and can be found teaching SEC557: Cloud Security and Continuous Compliance. Learn more about AJ here.