Build custom osquery tables using ATC

ATC to search every file downloaded across your Mac Fleet

What is an ATC table?

ATC (automatic table construction) is a method which can expose the contents of local SQLite database file as an osquery virtual table.

ATC was added to osquery by Mitchell Grenier (obelisk) in response to a number of virtual table pull requests which all functioned by parsing SQLite databases. Rather than approving each table as a separate pull request, Mitchell took the opportunity to add a native SQLite parsing method to osquery, which would allow adding any number of new virtual tables on a customizable basis.

Why is parsing SQLite DB’s useful?

Many applications use SQLite databases as a storage method for application data, including things like:

Google Chrome Browser History

1Password Vault Sync Configuration

Skype Call History

iMessage Chat History (*finally changed in Mojave)

macOS Quarantine Events (System-wide Download History)

As these examples illustrate, while application databases can provide tremendous utility, they also represent a potential concern for user privacy (a core tenet of osquery’s security philosophy). There are times however, where the introspection of databases can be invaluable to an Incident Response team in their forensics gathering (eg. the aforementioned Quarantine Events database).

While you may be concerned by the privacy implications of reading databases containing PII, you can take some solace in the fact that ATC tables must be declared at a configuration level in osquery and are not as simple as:

select * from atc_table where path = /foo/bar.db

Let’s examine a real life scenario in which ATC tables could be utilized to expand the data collection capabilities of osquery.

Searching the macOS Download History using ATC:

“My computer was infected with malware, but don’t worry I cleaned it up.”

There are few things more frustrating to an incidence response team than the needless deletion of evidentiary findings. Discovering the active presence of malware on a device is of the highest concern. However, it is equally vital to know about the past-presence of malware and its respective source of origin (eg. an installer download link sent via email).

Yet, combing through various download history files is no one’s idea of fun, and not all applications keep a record.

You might be surprised to learn however, that if you are using an Apple computer, a record of every file you’ve ever downloaded exists on your device. No matter whether it was downloaded in Safari, Chrome, Mail.app, AirDrop, or any other 3rd party application, it’s right there all in one convenient location: