Mon, Oct 4, 2021

How to Use KAPE and SQLECmd with EventTranscript.db

EventTranscript.db, a recently discovered Windows 10+ artifact, can track and maintain a wealth of artifacts and data elements. Kroll’s Andrew Rathbun and Josh Mitchell found that Kroll Artifact Parser and Extractor (KAPE), leveraging the EventTranscriptDB Target and SQLECmd Module, will collect the database from any Windows 10+ system if the database is enabled and parse it accordingly.

In this webcast replay, Andrew and Josh walk through the benefits of using the EventTranscriptDB Target using KAPE, parsing EventTranscript.db using !EZParser or SQLECmd Modules and how to set up EventTranscriptDB SQLECmd Map within your local instance of KAPE.

Watch the Webcast Replay

How to Use KAPE and SQLECmd with EventTranscript.db

This Webcast Covers

  • Overview of the DiagTrack service
  • Ways control mechanisms can impact logging
  • Data sampling and how to identify its presence
  • EventTranscript.db in everyday analysis
  • How EventTranscript.db can be the only location of certain information
  • Investigative workflow using the applicable KAPE Target and Module
 

Tools Used in This Session

 

Speakers

 

Download Webcast Slides

Notable Passages From the Presentation

On EventTranscript.db

EventTranscript.db relates to telemetry and diagnostic tracking. It's basically the back end for the diagnostic data viewer, which is an application that you can download from the Microsoft store. It has multiple levels of diagnostic tracking, which we'll briefly touch upon. And there's a lot of documentation on Microsoft's website in regards to diagnostic data and telemetry, but nothing really talks about EventTranscript.db as the artifact itself. – Andrew Rathbun

On DiagTrack Service

We really needed to understand how the data was being put into that database. What kind of controls happened? What really made this thing work? So, to that end, I've started pulling apart the kind of massive projects, that is windows telemetry subsystem. – Josh Mitchell

So, you have the DiagTrack service, now that connects to Microsoft through various rest API endpoints and pulls down data collection packages. Those data collection packages are used to dictate the behavior of process collection nodes, which are the user land-processes that you and I use, as well as dictate the behavior of the compatibility layer. Now, these things are stored in the registry and there's lots of communication between these various components and the registry at the varying levels where this stuff happens. – Josh Mitchell

The DiagTrack service maintains most of its registry settings under the software, Microsoft windows current version diagnostics key. – Josh Mitchell

On DiagTrack Service Registry

Pulling apart some of these JSON files, we can see the collection names. Those are the different event names and their collection rates and how much data to get. And the interesting thing is that whether or not the packages are enabled, this seems to indicate that the data is always being given, it's just whether or not the JSON package is there to tell the system to record it. – Josh Mitchell

On Data Collection Control Mechanisms

How do we figure out if the data gets sent to the database, and then as a result sent back to Microsoft? Now inside of the DiagTrack service, there is a DLL that is used and called flightsettings.dll. And that DLL calls into policymanager.dll. And we can see on the right-hand side, some of the different registry values that are checked by policymanager.dll and then back into flightsettings.dll. So, these types of registry keys, we can see here definitely seem to indicate that this is how we decide if we should enable or collect the telemetry packages. – Josh Mitchell

That DLL seems to provide the same type of functionality as flight settings, but in a user-controlled process in edge. And one of the big values that we've come across is that this allows telemetry value, which seems to show how data is collected, whether it's for both or it's just minimal collection, but this key seems to control how verbose it is. – Josh Mitchell

Within flight settings DLL, is a pretty neat thing that we came across. You see, you have like FX:1190030E, these seem to be related to different flights is the terminology that's used. When we look into the JSON and we look into the diagnostic data viewer, we can see a direct correlation between these flight ID numbers and the various collection packages that are being run on this system. Obviously going by the name, it seems like it's related to experimentation, we don't know yet, but it is just another example of the different kinds of things that we have left to figure out, because there's quite a lot. – Josh Mitchell

On SQLECmd

We can parse what we've seen using SQLECmd, which is a tool by Eric Zimmerman to deconstruct SQLite databases, really regardless of where they came from Obviously KAPE is used on windows systems, but you can use it to technically point at a folder of SQLite DBs that you extract from a phone, for instance, or from Linux. Anything that has a SQLite database, you can parse it, so long as there's a map for it. – Andrew Rathbun

Some tips and tricks when using SQLECmd: As always, (I always harp on this), make sure you're always running a sync and keeping your maps up-to-date. If you're ever having any parsing issues; you're not getting output, the answer is always going to be to delete your maps folder, and run a sync again, because you're likely having some conflicts. – Andrew Rathbun

On KAPE

We're acquiring files, artifacts with the KAPE triage target, we're parsing it with the EZ parser module, so we're leveraging almost all of Eric's command line tools. We're sending it to CSV for the format and debug messages help us with troubleshooting. Any of this output can be put into Timeline Explorer, modern CSV, Excel, depending on the size of the CSV, or really any other CSV viewer tool for analysis. – Andrew Rathbun

On Final Thoughts

We speculate that diagnostic data and telemetry in windows is here to stay. It's reasonable to speculate that the level of logging should only increase over time, given the value of the data. A big thing with software development nowadays is, they want to know what users are clicking on which buttons, so they know how to best improve the user interface. – Andrew Rathbun



Cyber Risk

Incident response, digital forensics, breach notification, managed detection services, penetration testing, cyber assessments and advisory.

Kroll Artifact Parser And Extractor (KAPE)

Find, collect and process forensically useful artifacts in minutes.

24x7 Incident Response

Kroll is the largest global IR provider with experienced responders who can handle the entire security incident lifecycle.


Data Recovery and Forensic Analysis

Kroll's expertise establishes whether data was compromised and to what extent. We uncover actionable information, leaving you better prepared to manage a future incident.

Cyber Risk Retainer

Kroll delivers more than a typical incident response retainer—secure a true cyber risk retainer with elite digital forensics and incident response capabilities and maximum flexibility for proactive and notification services.

Computer Forensics

Kroll's computer forensics experts ensure that no digital evidence is overlooked and assist at any stage of an investigation or litigation, regardless of the number or location of data sources.


Data Collection and Preservation

Improve investigations and reduce your potential for litigation and fines with the strict chain-of-custody protocol our experts follow at every stage of the data collection process.