Searching SQLite databases using GRR and osquery

By

 

Introduction

There are billions of billions of SQLite instances in the world (ref: https://www.sqlite.org/mostdeployed.html), and many examples can be found on virtually every active phone, laptop or workstation. In this blog post we will discuss a method by which investigators can perform a search of SQLite database files using GRR Rapid Response (GRR), osquery and dfTimewolf.  We assume the reader has an understanding of these tools.

What is GRR?

GRR is an incident response framework focused on remote live digital forensic analysis. GRR was built to run at scale so that analysts are capable of effectively collecting and processing data from large numbers of machines concurrently. It achieves this through flows (asynchronous calls to execute client code and retrieve results from a single machine) and hunts (flows scheduled to run on many machines). 

What is Osquery?

Osquery is an open source tool that exposes an operating system as a query-able interface like SQL for a relational database; allowing investigators to extract valuable system information about an endpoint, such as running processes, network connections, file metadata and user accounts. osquery comes with many predefined parsers and tables but depending on the investigation context and needs, we may need to define custom parsing logic without wanting to build and redeploy a custom binary each time.

What is Automatic Table Construction (ATC)?

Osquery has an extensible design, allowing users to add custom parsing logic and expose new tables through a number of ways:

Using ATC, we can expose a SQLite database as an osquery table by simply defining a JSON object in a configuration file that osquery loads from the filesystem during runtime, either from a default location or set via a control flag. 

For example, using the query for the Chrome downloads Plaso parser, we can build an ATC configuration to query this artifact as follows:

{

  "auto_table_construction": {

    "chrome_download": {

      "query": "SELECT downloads.id AS id, downloads.start_time, downloads.target_path, downloads_url_chains.url, downloads.received_bytes, downloads.total_bytes, downloads.end_time, downloads.state, downloads.danger_type, downloads.interrupt_reason, downloads.opened FROM downloads, downloads_url_chains WHERE downloads.id = downloads_url_chains.id", 

      "path": "/Users/%/Library/Application Support/Google/Chrome/%/History", 

      "columns": ["id", "start_time", "target_path", "url", "received_bytes", "total_bytes", "end_time", "state", "danger_type", "interrupt_reason", "opened"]

    }

  }

}


In the next sections, we will explore how to use osquery ATC through GRR or dfTimewolf to search SQLite databases on live systems.


GRR and Osquery

GRR and osquery have had seamless integration for some time now (see the GRR osquery page for steps on how to setup integration) and investigators are able to execute osquery queries on GRR clients via the GRR web console, API client or via dfTimewolf (for example the grr_osquery_flow recipe). If you have osquery set up with an appropriate configuration file, via configuration management for example, you can already instruct osquery to parse and query SQLite databases (provided that they have an ATC definition) remotely and at scale via flows and hunts.

However, let’s consider two other scenarios where we have osquery installed on a GRR client:

  1. existing osquery configuration file on the GRR client’s local filesystem but it is not set up to load when osquery runs.

  2. no osquery configuration file on the GRR client’s local filesystem.

GRR has had some updates to the osquery flow which address these two scenarios.

Scenario 1 - existing configuration file on target GRR host

Using the example on the osquery readthedocs page, we can configure the osquery flow to query the macOS TCC database as follows:

  1. To simulate an existing configuration file, create an osquery configuration file on the GRR client under /var/osquery/tcc_atc.json with the following content:


{

 "auto_table_construction": {

  "tcc_system_entries": {

   "query": "SELECT service, client, auth_value, last_modified FROM access;",

   "path": "/Library/Application Support/com.apple.TCC/TCC.db",

   "columns": [

    "service",

    "client",

    "auth_value",

    "last_modified"

   ],

   "platform": "darwin"

  }

 }

}



  1. Go to the GRR web console for the GRR client and select the “Osquery” flow.

  2. Enter the query you want to perform e.g. SELECT * FROM tcc_system_entries.

  3. Click “Show low-level settings” to expand and make the “Configuration Path” text box visible.

  4. In the “Configuration Path” text box , enter the value /var/osquery/tcc_atc.json 


  1. Hit Start and wait for the flow to complete and see the results in GRR. 


Scenario 2 - no configuration file

Recent updates to the osquery flow also add a “Configuration content” option which creates a temporary file on the GRR client with the content of this field. For example, we can simply copy and paste the ATC configuration used above into the “Configuration content” text box and run the same query:

Take into consideration that the temporary file is created only for the purposes of running osquery and is cleaned up after the flow completes and whether this is appropriate for your investigation. 

dfTimewolf

For those using dfTimewolf, we can also use these new changes as follows:

  • If there is an existing configuration file on the GRR client:

$ dftimewolf grr_osquery_flow examplereason test.macbook.home --osquery_query "SELECT * FROM tcc_system_entries;" --remote_configuration_path "/var/osquery/tcc_atc.conf"


  • If you want to create a temporary configuration:


$ dftimewolf grr_osquery_flow examplereason test.macbook.home --osquery_query "SELECT * FROM tcc_system_entries;" --configuration_content '{

 "auto_table_construction": { "tcc_system_entries": { "query": "SELECT service, client, auth_value, last_modified FROM access;", "path": "/Library/Application Support/com.apple.TCC/TCC.db", "columns": ["service", "client", "auth_value", "last_modified"], "platform": "darwin"}}}'


  • If you have an existing configuration file on your investigation machine and want to create a temporary configuration with the contents:


$ dftimewolf grr_osquery_flow examplereason test.macbook.home --osquery_query "SELECT * FROM tcc_system_entries;" --local_configuration_path /store/osquery/configurations/tcc_atc.conf


Conclusion

Investigators can utilize the ATC functionality in osquery through GRR, enabling the ability to query and search SQLite databases on GRR clients efficiently and at scale. This opens a wide range of possibilities for threat hunting, incident response and forensic analysis. For example, in the context of users visiting phishing pages or downloading malicious files, this capability can be used to efficiently identify potentially impacted hosts, allowing for prompt containment and remediation measures.

Comments

Post a Comment

Popular posts from this blog

Parsing the $MFT NTFS metadata file

By
Background Numerous articles have been written about parsing the $MFT NTFS metadata file before. Some of these explain the technique itself [ 1 ] and others mostly focus on how to use tools. Rarely do articles mention the pros and cons of the technique from a digital forensics perspective [ 2 ].  What is $MFT parsing?  For readers not familiar with the NTFS file system, MFT stands for Master File Table. This table is stored in a file system metadata file, meaning that as far as the file system is concerned it is a file, however that the file contains file system metadata not “regular” content. The name of this file is “$MFT”.  The MFT consists of a sequence of (predetermined) fixed-size entries (or MFT entries). These entries are typically 1024 bytes in size, however the size is defined in the NTFS volume header and in a “regular” MFT entry itself.  The first 4-bytes of a “regular” MFT-entry (or record) starts with the signature “FILE”. The MFT entry can also be filled wit
Read more

Incident Response in the Cloud

By
Many organizations have begun moving a majority of their services towards the cloud in recent years. As a result, attackers have shifted their focus towards the cloud. This has resulted in new techniques and methods specifically designed to compromise cloud infrastructure, like the recent SALTSTACK vulnerability that was widely exploited [ 1 ]. Therefore it is critical for these organizations to have an incident response team that understands the new risks attached to cloud and how cloud can make incident response easier or harder.  In this blog post we will walk you through each phase you may encounter in traditional incident response and highlight the differences when adopting cloud computing. It is aimed towards both those who are new to incident response and cloud computing. We’ve included insights that will benefit organizations taking that step towards cloud who want to ensure they are prepared to respond efficiently to cloud incidents.  Traditional Incident Response Before jump
Read more

Container Forensics with Docker Explorer

By Anonymous
  Introduction As previous blog posts on cloud forensics have noted, applications are increasingly being deployed using container orchestration frameworks such as Kubernetes, especially in cloud environments.  Similar to traditional deployments on physical servers or virtual machines (VMs), when a containerised application has a security issue it can lead to a compromise of the underlying compute architecture. In the case of container deployments this means a compromise of the container itself, the container host, or even a wider cluster compromise via abuse of orchestration tools. Often digital forensics is required to establish what went wrong and remediate any issues. This article will provide an introduction to container forensics with Docker Explorer by working through a scenario involving a compromised container running within a Kubernetes cluster. Although Kubernetes is briefly mentioned, this article will focus on analysis of an individual container rather than the wider clust
Read more