Version: Next

Audit Logs

Every change in a Visiopharm database is recorded in an audit log. This allows the review of changes made at a specific time, by a particular user, or based on the type of event.

The audit log is a chronological list of records that provides a trail of the activities in a database. This includes any additions, changes or removals a user have made within the database.

The audit log is accessed through the Microsoft SQL Server Management Studio distributed along with Visiopharm.

caution

The audit log is not protected against changes. Therefore the logs cannot be used as evidence against a certain user.

Accessing the Log

Open Microsoft SQL Server Management Studio.
Windows 7 users can find the program under the folder Microsoft SQL Server 2008 R2 or by searching for Management Studio. Windows 8 users can find for the program by clicking the Windows button start typing the name of the program to open up the search functionality.
In the login window (seen below), press Connect.

A window with two columns will appear. In the left column, Object Explorer, double-click Databases to view a list of available databases, matching those in Visiopharm.
Select a database by double-clicking it.
Double-click Tables(SQL terminology for a list of records).
Locate the dbo.AuditLog table. Right-click on the table and select Select Top 1000 Rows.

This will open three sub-windows: (1) A query editor, (2) a result window and (3) a properties window.

The query window is used to define search criteria for the records to be retrieved. The found records will be displayed in the results window and a summary of the given records can be found in the properties window.

Contents of the Audit Log

The results window for the audit log includes the following fields for each change in the database:

Field	Description
AuditLogID	A specific ID for each record.
TimeStamp	The date and time of the change.
UserName	The name of the user who made the change.
EventType	The type of change in the database. The EventType can be one of the following: Add - Used for additions, e.g. when an image is added or a new information field is created. Remove - Used for deletions, e.g. when an information field is deleted. Change - Used for changes, e.g. when ROIs, labels and measures are added. State - Connection state of the database, e.g. when logging is activated. APP Execution Used for APP executions. NB! APP executions are not logged by default, the variable APP Execution Log needs to be added.
Event	Details about which kind of change that is made to the database. Example: For databases created with Visiopharm 6.2.0 or later, the specific database object which the change applies to, is displayed as well. E.g. in the Result window above, the AuditLogID 10 shows that the LayerData.mld was added (EventType: Add) to the image under: Demo Images/Whole Sections/Ki-67.

Searching the Audit Log

Records in the audit log can not be found in the standard way from a search bar, but needs to be retrieved with an SQL query.

The SQL query is a line of text that defines search criteria, that the record needs to fulfill in order to become a result. Queries are written in the SQL syntax and some examples of queries are given below. If a record cannot be found, we advised to follow the tutorial on SQL from w3schools.com/sql.

A SQL query is written and the query editor and executed by clicking on the execute button.

note

A query is searching within a selected database. To search within another database, right click the database in the Object Explorer and click New Query.

List of common SQL queries:

Query	Description
*SELECT FROM dbo.AuditLog**	This query will return all records in the audit log.
*SELECT FROM dbo.AuditLog WHERE UserName = 'John'**	This query will return all records where the user is John.
*SELECT FROM dbo.AuditLog WHERE EventType = 'Add'**	This query will return all records where the EventType is Add.
*SELECT FROM dbo.AuditLog WHERE EventType = 'Add' OR EventType = 'Remove'**	This query will return all records where the EventType is Add or Remove.
*SELECT FROM dbo.AuditLog WHERE Event LIKE 'D%'**	This query will return all records where the Event starts with the letter D.
*SELECT FROM dbo.AuditLog WHERE Event LIKE '%Study Unit%'**	This query will return all records where the Event contains the text Study Unit.
*SELECT FROM dbo.AuditLog WHERE Event LIKE 'D%' AND USERNAME = 'John'**	This query will return all records where the Event starts with the letter D and the user is John.
*SELECT FROM dbo.AuditLog WHERE TimeStamp BETWEEN '2018-01-01 12:00:00' AND '2018-01-31 12:00:00'**	This query will return all records where the TimeStamp is between 2018-01-01 12:00:00 and 2018-01-31 12:00:00.
*SELECT FROM dbo.AuditLog WHERE TimeStamp BETWEEN '2018-01-01 12:00:00' AND '2018-01-31 12:00:00' ORDER BY TimeStamp DESC**	This query will return all records where the TimeStamp is between 2018-01-01 12:00:00 and 2018-01-31 12:00:00 and order the results by TimeStamp in descending order. Replace DESC with ASC to order the results in ascending order.

Export selection of audit by a query

The found results can be exported to a file. In the example below, the query is searching for records where something have been removed and is showing the latest records first (descending).

SELECT * FROM dbo.AuditLog WHERE EventType='Remove' ORDER BY TimeStamp DESC

To export the list of retrieved records, right click in the results window and click Save Results As...

Choose a destination and a suitable filename. The filetype can be chosen as either CSV, which can be opened directly in Excel or as a tab delimited text file.

Export the entire audit log

Open SQL Server Import and Export Wizard.

The SQL Server Import and Export Wizard is a convenient way to export the entire audit log for a database. To open the wizard, right-click on the database to export. Then go to Tasks and click the "Export Data..." option.

Choose Data source, and double-check that the correct database is selected.

Choose Destination. To export to Microsoft Excel, change the Data source from "SQL Server Native Client 10.0" to "Microsoft Excel". Then select an Excel file path. If a path for an existing Excel file is chosen, the exported audit log rows from SQL will be appended to content in the Excel file. If the Excel file does not exist, a new file will be created containing the exported rows.

Table Copy or Query. It is also possible to only export some rows using a query in the wizard. But, for exporting the entire audit log leave the option at Copy data from one or more tables or views.