Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
END,
REPLACE(ure_fullname, '\', '/') -- swap the backslashes for forward slashes
FROM usn_journal_report -- this is a view in the Triforce ANJP DB
ORDER BY ur_datetime ASC; -- order by timestamp
@NomisRenarc

This comment has been minimized.

Copy link

@NomisRenarc NomisRenarc commented Apr 20, 2021

Hi there, I am looking for something similar to be able to convert a sqllite db from 'NTFS Log Tracker v1.6'.
It seems that Triforce website cert has expired and cannot get access to the Free Version to try this out.
I am looking at extending my arsenal of tools, any assistance will be great appreciated.

@obsidianforensics

This comment has been minimized.

Copy link
Owner Author

@obsidianforensics obsidianforensics commented Apr 22, 2021

I haven't used that particular tool before. If you can give me an example output SQLite database, I can see if I can modify the SQL to work with it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment