Skip to content

Instantly share code, notes, and snippets.

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
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
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
Copy link

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