Skip to content

Instantly share code, notes, and snippets.

View obsidianforensics's full-sized avatar

Ryan Benson obsidianforensics

View GitHub Profile
@obsidianforensics
obsidianforensics / USN_to_Gource.sql
Last active April 22, 2021 02:32
SQL Query to Convert Triforce USN DB to Gource Custom Log
/* SQL to convert a Triforce ANJP USN Journal database to a Gource custom log
by ryan@obsidianfornesics.com
Convert the human-friendly timestamp to epoch seconds: */
SELECT CAST(round((JULIANDAY(ur_datetime)-2440587.5)*86400,0) as integer),
'USN', -- gource needs a 'User', so I set it statically to 'USN'
CASE ur_reason_s -- gource supports three file 'update types':
WHEN 'File_Create' THEN 'A' -- 'A' for adding a file
WHEN 'File_Delete,Close' THEN 'D' -- 'D' for deleting
ELSE 'M' -- and 'M' for modifying
{
"name": "parsers_counter",
"children":[
{"name":"chrome_preferences","size":26},
{"name":"chrome_27_history","size":1694},
{"name":"chrome_autofill","size":60},
{"name":"chrome_cache","size":140}
]
}
@obsidianforensics
obsidianforensics / gource_mbdbls_output
Created June 28, 2017 05:03
mbdbls.py -g output snippet
1417729597|User|M|/Media/PhotoData/Thumbnails/V2/DCIM/100APPLE/IMG_0008.JPG/5003.JPG
1417732840|User|M|/Media/DCIM/100APPLE/IMG_0009.JPG
1417732841|User|M|/Media/DCIM/100APPLE/IMG_0009.JPG
1417732841|User|M|/Media/PhotoData/Thumbnails/V2/DCIM/100APPLE/IMG_0009.JPG
1417732841|User|M|/Media/PhotoData/Thumbnails/V2/DCIM/100APPLE/IMG_0009.JPG/5003.JPG
1417743015|User|M|/Media/DCIM/100APPLE/IMG_0010.JPG
1417743015|User|M|/Media/PhotoData/Thumbnails/V2/DCIM/100APPLE/IMG_0010.JPG
1417743015|User|M|/Media/PhotoData/Thumbnails/V2/DCIM/100APPLE/IMG_0010.JPG/5003.JPG
1417747298|User|M|/Library/Preferences/com.apple.mediaartworkd.plist
@obsidianforensics
obsidianforensics / mbdbls_output.txt
Last active June 28, 2017 04:29
mbdbls.py Output Snippet
drwxr-xr-x 501 501 0 2016-07-12 18:08:11 2016-07-12 18:08:11 2014-12-03 19:20:45 cd9065c1e4b785cee8a0d9e6c90275f5e837c4d7 AppDomain-com.apple.iBooks::Library
drwxr-xr-x 501 501 0 2016-07-12 18:08:15 2016-07-12 18:08:15 2014-12-03 19:20:45 83eeb46d85472b89b8390d341bb0c896e53502b6 AppDomain-com.apple.iBooks::Library/Preferences
-rw------- 501 501 809 2016-07-12 18:08:15 2016-07-12 18:08:15 2016-07-12 18:08:13 51fca3a3004e8f8e08f37a0a5ac3d7512274ee24 AppDomain-com.apple.iBooks::Library/Preferences/com.apple.iBooks.plist
drwxr-xr-x 501 501 0 2016-07-12 18:08:11 2016-07-12 18:08:11 2016-07-12 18:08:11 d95fdd7d874991aec0b9260223f60d6c008474a6 AppDomain-com.apple.iBooks::Library/com.apple.iTunesStore
drwxr-xr-x 501 501 0 2016-07-12 18:08:11 2016-07-12 18:08:11 2016-07-12 18:08:11 25ca8b1106dd22d83351aef67278200618f087e4 AppDomain-com.apple.iBooks::Library/com.apple.iTunesStore/LocalStorage
# Open the 'LocalData.sqlite file
with local_data_db:
c = local_data_db.cursor()
# Select the rows where ZKEY starts with 'ToDoCollections' - there should only be two, ToDoCollection.TASK and
# ToDoCollection.SHOPPING_ITEM
c.execute("SELECT ZVALUE FROM ZDATAITEM WHERE ZKEY LIKE 'ToDoCollection%'")
# For both the rows we selected with the above query, we want to:
for row in c.fetchall():
@obsidianforensics
obsidianforensics / usn.log_Snippet
Created May 24, 2015 18:10
Sample from Gource-formatted usn.log
1424658814|USN|M|/Users/user1/AppData/Local/Temp/logEF94.txt
1424658814|USN|A|/Users/user1/AppData/Local/Temp/testmem.exe
1424658814|USN|M|/Users/user1/AppData/Local/Temp/testmem.exe
1424658814|USN|M|/Users/user1/AppData/Local/Temp/testmem.exe
1424658814|USN|D|/Users/user1/Downloads/voice#5734223/voice.exe
1424658814|USN|A|/Windows/Prefetch/VOICE.EXE-78467D55.pf

Keybase proof

I hereby claim:

  • I am obsidianforensics on github.
  • I am ryanbenson (https://keybase.io/ryanbenson) on keybase.
  • I have a public key whose fingerprint is 4AB5 DCB0 8EC1 8099 3601 797C 991F 9F58 90E9 7202

To claim this, I am signing this object: