SQL Query to Convert Triforce USN DB to Gource Custom Log
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
|/* 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|
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
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.