Windows Activity Cache in JSONL

As you know, I love putting my DFIR data into arangodb.

If you missed my talk on correlating forensic artifacts using ArangoDB at the SANS DFIR Summit, they have you covered with a video: https://youtu.be/-r9AjoZa4lA. You can also find the slides at: https://www.sans.org/summit-archives/file/summit-archive-1528492495.pdf.

Its no secret that I am a fan of having my artifacts in a nested format. When trying to display forensic artifacts to a user, often times, a tool can leave out data in trying to flatten the data structures into a format such as CSV. Because of this, it can sometimes be more difficult to correlate artifacts to help determine a given activity due to fields in the structures being left out. It is understandable why this occurs, a flat text type report allows for human readability. But, when it comes to the automation of looking for things (known or unknown), it can make it difficult.

This post will start a series I have been wanting begin for a while that will attempt to fill in gaps on my previous talks about forensic artifact correlation and its automation.

The first step in this process is the tool we use to process an artifact.

Because the Windows 10 ActivitiesCache.db is a flashy new artifact recently introduced and it provides valuable information, I wanted to make a tool that could format its records into JSONL output for use in my correlation and automation system. There are already some tools available that also help process this artifact. No surprise that Eric Zimmerman has made one! You can find his WxTCmd tool ( Windows 10 Timeline database parser) at his tool page here: https://ericzimmerman.github.io/#!index.md. Really, any SQLite viewer will also work as the artifact is just a SQLite database.

Introducing my ActivitiesCacheParser. Sorry, no fancy name. It is up on my github repo https://github.com/forensicmatt; as are a lot of the other tools I used in my SANS talk. Sorry, the ActivitiesCacheParser is currently only Python 3, though I aim to change this shortly.

Also, for a quick install in a Python 3 environment. Try:

pip install git+https://github.com/forensicmatt/ActivitiesCacheParser

I have added some documentation to the README file, have a look. I don’t feel like duplicating it here. Again, the main purpose of this tool is to output records as JSONL so that I can ingest in to solutions like AragnoDB for automating purposes. However, the winactivities2json.py script also allows you to do some custom output formatting (Example 1 in the README) to make it easier on the human eyes. One of the things that I needed my tool to do was to further expand JSON fields within the SQLite records.

This is from the Activity table:

As you can see in a SQLite viewer, the Activity records (among other tables) contain additional JSON documents. If I wanted to query these fields or collate against them in ArangoDB I will need to insure they are parsed out objects.

The winactivities2json.py tool can be ran on either a logical volume or an individual file.

Running it on an extracted file:

winactivities2json.py -t D:\Testing\activities --debug INFO -s D:\Testing\activities\mpowers-L.mpowers\ActivitiesCache.db

We get records look like this:

{“_rowid”:3,”Id”:”8e6f7dbc6dec80f277a5e77075eae2fb”,”AppId”:[{“application”:”C:\\Users\\mpowers\\Downloads\\vs_buildtools__1518588333.1531427400.exe”,”platform”:”x_exe_path”},{“application”:”C:\\Users\\mpowers\\Downloads\\vs_buildtools__1518588333.1531427400.exe”,”platform”:”packageId”},{“application”:””,”platform”:”alternateId”},{“application”:””,”platform”:”windows_universal”}],”PackageIdHash”:”YswsjmJjlyDHWvzdTOidplNztpGNSGyM37fh8h0z5BA=”,”AppActivityId”:”ECB32AF3–1440–4086–94E3–5311F97F89C4",”ActivityType”:5,”ActivityStatus”:1,”ParentActivityId”:”00000000000000000000000000000000",”Tag”:null,”Group”:null,”MatchId”:null,”LastModifiedTime”:”2018–07–12 20:30:21",”ExpirationTime”:”2018–08–11 20:30:21",”Payload”:{“displayText”:”vs_buildtools__1518588333.1531427400.exe”,”activationUri”:”ms-shellactivity:”,”appDisplayName”:”vs_buildtools__1518588333.1531427400.exe”,”backgroundColor”:”black”},”Priority”:3,”IsLocalOnly”:0,”PlatformDeviceId”:”zdb2vOpgPSkxd2PLwsayEmxe1DNFt6GOtaz+2ENpgLU=”,”CreatedInCloud”:0,”StartTime”:”2018–07–12 20:30:21",”EndTime”:0,”LastModifiedOnClient”:”2018–07–12 20:30:21",”GroupAppActivityId”:””,”ClipboardPayload”:null,”EnterpriseId”:””,”OriginalPayload”:null,”OriginalLastModifiedOnClient”:null,”ETag”:5}

Nothing fun about going through thousands of lines of that. But, this is output that is meant for something other than our human eyes. The next post I will walk though some other things we can do with this tool along with ingesting this data into ArangoDB and using ArangoDB to format and filter the records.