I am using Crystal Reports to report activities from the Audit Trail version 9.1 Database. Is there a listing of the tables and what is contained in each table?
audit trail database tables
Out of curiosity, what type of report are you looking to create? The existing database views provide a lot of options.
I agree with your statement on using Crystal Reports versus Audit Trail when it comes to grouping or reporting on relationships. That's a great use case for Crystal Reports.
Changes to a field value are logged in the [propval] table and mapped with more detail in [entry_events] view.
SELECT * FROM [LFAudit].[dbo].[entry_events] WHERE [event_type] = 28
Would yield any time a field value was written AND the Laserfiche Administration Console has been setup to log such an event.
You could further break this down by the value written, host name written from, user entry ID, and the *specific field.
(*denoted by unique prop_id value which is further defined in the [propdef] table in the main repository database. )
I think you would be able to get all the information you could desire for Crystal Reports from a combination of mappings in the Audit Trail database, Repository database in question, and a proper configuration of which events to record (not all events are setup to record by default and as Chris mentioned, the date range has a factor). But it does take a combination of all three to get you there.
Another option is to create workflows to post desired reporting data into a separate custom SQL database.
Best of luck!
I do not believe there is a published database dictionary but there is a an event type schema documented.
The tables themselves contain events mapped by event_ID. Events are created by the auditing settings configured per user or repository in the Laserfiche administration console.
The tables also use the same description language as the repository database itself. Meaning, propval refers to field values being declared and propset refers to template values being set.
Thank you Carl and Chris for you quick responses.
I am trying to gather statistics on our current project to see activities, such as how many documents a specific user processed, how many times a specific Metadata field was changed by that user, etc.
I should probably mention for this project we are not using Laserfiche in a traditional way. We are using Laserfiche to read tax returns and enter the required data into our other software program. We are now at the point we are trying to make this as automated as possible and not have to review all of the data before it is passed to our other software system.
I tried using the Audit Trail interface and I found that I cannot group or perform more advanced tasks in the Audit Trail program. I have been using Crystal Reports for 2 years and find it easier to organize the data through Crystal Reports than the Audit Trail.
Thank you for the reminder I need to watch the date range of the Audit Trail database!
Interesting approach. Usually it's not recommended by Laserfiche to interact directly with the SQL tables but I'm curious. Is there a reason you are doing things this way rather than through the Audit Trail console?
Also consider that not everything might be loaded into the SQL tables. Some information might be in the Audit Logs.
As for a list of what's in the tables. I'm guessing that would have to come from LF as the table structure looks pretty meaningless.