How to automate daily reports with ApexSQL Log

Applies to
ApexSQL Log

Summary
This article explains how to create and maintain a daily audit trail for your databases using ApexSQL Log

Description
We will address two scenarios in this article:

• Everyday documenting of all transactions that occurred (incremental approach)
• Everyday documenting only transactions that occurred on the given day (differential approach)

Incremental approach

To employ the incremental approach for a single database please:

1. Copy and paste following lines into a text editor of your choice:

   @ECHO OFF
   CD <full_path_to_apexsql_log_installation_folder>
   ECHO apexsqllog.com
   /S:<sql_server_name>
   /D:<database_name> /HTML:<full_path_to_report_folder><database_name>_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport.bat
   START runreport.bat

2. Replace <full_path_to_apexsql_log_installation_folder> with the full path to the ApexSQL Log installation folder
3. Replace <sql_server_name> with the name of the SQL Server and SQL instance where the database you want to audit resides ( (local) if no named SQL Server instance exists)
4. Replace <database_name> with the name of the database you want to audit

5. Replace <full_path_to_report_folder> with the full path to the folder of your choice that will be used to store the reports

   Please note:
   In this example the report will be created in HTML format. To change the format of the report to CSV or XML use the /CSV: or /XML: switches respectively.

6. Save the file with the *.bat extension
7. Schedule it to run daily via Windows Task Scheduler or Windows Scheduler
   This way, each day at the time scheduled to run the task, an HTML report will be created containing detailed information on all of the transactions that have occurred on the database specified. The file name will be in a <Database_name>_<YY-MM-DD>

Incremental approach can be employed for multiple databases even on different SQL Servers or instances. To do so, add the following block of code as many as times as there are different databases you want the report to be generated for.

ECHO apexsqllog.com
/S:<sql_server_name_1>
/D:<database_name_1> /HTML:<full_path_to_report_folder><database_name_1>_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport1.bat
START runreport1.bat

Let’s say we want to audit three databases named A, B, and C. A is located on a local SQL Server which has no named instances installed, and B is located on the instance INST1 and C is located on the instance named INST2 of a remote SQL Server called SRVR. Furthermore, let’s say that ApexSQL Log is installed in its default location and that reports should be stored in D:\DatabaseAudit folder.
The batch file should look like this:

@ECHO OFF
CD C:Program FilesApexSQLApexSQLLog2010
ECHO apexsqllog.com
/S:(local) /D:A /HTML:D:DatabaseAuditA_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport1.bat
START runreport1.bat
ECHO apexsqllog.com
/S:SRVRINST1 /D:B /HTML:D:DatabaseAuditB_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport2.bat
START runreport2.bat
ECHO apexsqllog.com
/S:SRVRINST2 /D:C /HTML:D:DatabaseAuditC_%date:~10,4%-%date:~4,2%-%date:~7,2%.html
> runreport3.bat
START runreport3.bat

Please note:
To exit all the command prompt windows that’ll spawn as a result of this process add

TASKILL /IM cmd.exe /F

command at the end of the batch file. Please note, that adding this command will kill all instances of the cmd.exe process, even those unrelated to this particular batch file and therefore caution is advised if you decide to use this command.

Differential approach

To employ the differential approach for a single database you only need to add the /FROM: switch to the apexsqllog.com command discussed earlier. This means that instead of

<ECHO apexsqllog.com
/S:<sql_server_name>
/D:<database_name> /HTML:<full_path_to_report_folder><database_name>_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport.bat

the batch files should contain the following line

ECHO apexsqllog.com
/S:<sql_server_name>
/D:<database_name> /FROM:%date:~10,4%-%date:~4,2%-%date:~7,2% /HTML:<full_path_to_report_folder><database_name>_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport.bat

while the rest of the batch file content should remain unaffected.
This way, each day at the time scheduled to run the task, an HTML report will be created containing detailed information on all of the transactions that have occurred on the database on that day only.
For differential reports to be generated for multiple databases just add

/FROM:%date:~10,4%-%date:~4,2%-%date:~7,2%

to each instance of the ECHO apexsqllog.com… line in the batch file.

About ApexSQL Log
ApexSQL Log is a SQL Server Transaction Log reader that allows viewing transaction log data in read-friendly format. Audit and undo SQL database changes of your choosing. Determine who changed the data and when the change occurred. Read the transaction log to find out who created, changed or dropped a database object

Last updated
July 23, 2012