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?
Question
Question
audit trail database tables
Replies
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.
Cheers,
Carl
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!
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!
Hi Miranda,
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.
Actually, for the Audit Trail tables we (Laserfiche) do encourage users to feel free to write reports which query the database directly. What we do not encourage is to modify the Workflow or Laserfiche repository databases directly, and we caution that querying those databases directly leads to code which is not portable across Laserfiche releases and bypasses security.
We endeavor to maintain a degree of stability for the Audit Trail database across releases and there's usually not a security concern for having your reporting tool query the database directly. You just need to ensure only authorized personnel are allowed to run the reports.
Interesting. Thanks Michael! Good to know.
Re: Chris Douglas
Personally, I've avoided using Audit Trail as I have found the tool to be very frustrating to use. A query that takes me at least 15 - 20 minutes with Audit Trail (assuming it doesn't fail completely), I can do in 2 -3 minutes through a direct query to the database.