ApexSQL Log CLI support

Description
This article explains how to use ApexSQL Log command line interface (CLI) and create output files.

To start the ApexSQL application GUI via the command line interface enter the product name. For example apexsqllog.exe

The CLI must be started using apexsqllog.com

Switches

Format of switches

1. Switches are case insensitive, i.e. /S: = /s:
2. Switches are preceded with one forward slash (/), i.e. /db:pubs
3. Switches are followed with a colon (:), i.e. /db:pubs
4. There are two types of switches – abbreviated/alias (1-3 chars) and full – with full name of switch. i.e. /p and /project
5. Default values for omitted parameters are used in most cases. Default values are specified in the following format in /help switch (“<default>” if omitted), i.e. (“(local)” if omitted)
6. Switches can be placed in arbitrary order
7. Switches and parameters are delimited with a white space

The following ApexSQL Log switches are available:

Connection options
/s:arg [ /server ]: Server name ((local) if omitted)
/e [ /trusted ]: Trusted connection (implicit if omitted)
/d:arg [ /database ]: Database name
/u:arg [ /user ]: User name (trusted connection if omitted)
/p:arg [ /password ]: Password specified along with the /user (/u) switch

Input options
/b:arg [ /backup ]: Transaction log backup files to audit
/o:arg [ /online ]: Logical names of online transaction log files to audit
/dt:arg [ /detached ]: Detached transaction log files to audit
/dbb:arg [ /db_backup ]: Optional database backup files to analyze

Export options
/ifl:arg [ /intermediary_files_location ]: Intermediary files location. This location can be changed to fit currently available HDD resources. Default directory if omitted (e.g. X:\Users\<user>\AppData\Local\ApexSQL\ApexSQLLog\LogData\)
/blk:arg [ /bulk ]: Exports audited data as SQL bulk into <files>. Usage: /bulk:bulkfile lognav_recordfile lognav_recorddetailfile
/sql:arg [ /sql ]: Exports audited data as SQL script into a file
/xml:arg [ /xml ]: Exports audited data as XML into a file
/htm:arg [ /html ]: Exports audited data as HTML into a file
/csv:arg [ /csv ]: Exports audited data as CSV into a file
/un:arg [ /undo ]: Creates an UNDO script as .sql file
/re:arg [ /redo ]: Creates an REDO script as .sql file
/bahtm:arg [ /before_after_html ]: Exports Before-after report as HTML into a file
/baxml:arg [ /before_after_xml ]: Exports Before-after report as XML into a file
/basql:arg [ /before_after_sql ]: Exports Before-after report as SQL script into a file
/bablk:arg [ /before_after_bulk ]: Exports Before-after report as SQL bulk script into a file
/todb [ /to_database ]: Exports audited data to database
/badb [ /before_after_database ]: Exports Before-after report to database
/exs:arg [ /export_server ]: Export to database – server name ((local) if omitted)
/ext [ /export_trusted ]: Export to database – trusted connection (implicit if omitted)
/exd:arg [ /export_database ]: Export to database – database name
/exu:arg [ /export_user ]: Export to database – user name (trusted connection if omitted)
/exp:arg [ /export_password ]: Export to database – password specified along with
/ec:arg [ /export_columns ]: Specifies columns to output into CSV, SQL, BULK, XML, or HTML
/a [ /append ]: Appends output if the output file already exists
/nh [ /no_header ]: Suppresses output of a header for SQL, BULK, REDO and UNDO exports
/nhr [ /no_historic_reconstruction ]: Suppresses historic reconstruction of UPDATEs
/euf [ /export_unchanged_fields ]: Exports unchanged fields

Filter options
/tr:arg [ /transactions ]: Transaction states to audit [BEGIN|COMMIT|ROLLBACK|UNKNOWN] (all transactions if omitted)
/from:arg [ /from ]: The beginning of a time range as ‘YYYY-MM-DD [HH:MM:SS[.mmm]]'(no time filtering if omitted)
/to:arg [ /to ]: The beginning of a time range as ‘YYYY-MM-DD [HH:MM:SS[.mmm]]'(no time filtering if omitted)
/last:arg [ /last ]: Time filter – last n hours from transaction log. Not compatible with /continuous, /first, /from and /to
/first:arg [ /first ] : Time filter – first n hours from transaction log. Not compatible with /continuous, /last, /from and /to
/cont:arg [ /continuous ]: Continuous auditing tracking file. Not compatible with /first, /last, /fromand /to
/md:arg [ /min_duration ]: The minimum transaction duration in seconds (not filtered by minimum transaction duration if omitted)
/tbls:arg [ /tables ]: Tables to audit (all tables if omitted)
/usrs:arg [ /users ]: Users to audit (all users if omitted)
/eusr:arg [ /exclude_users ]: Users to exclude when auditing
/ops:arg [ /operations ]: Operations to audit

(all operations (DMLALL|DDLALL) if omitted)
/fs:arg [ /fields ]: Fields to search for as ([table].[column] operation ‘value’) (ignored if omitted)
/ss:arg [ /spids ]: SPIDs to search for (all SPIDs if omitted)
/ds:arg [ /descriptions ]: Transaction descriptions to search for (all descriptions if omitted)
/eut [ /exclude_user_tables ]: Excludes all user tables from audit
/ist [ /include_system_tables ]: Include system tables in auditing process
/edt [ /exclude_dropped_tables ]: Excludes operations made on dropped tables
/edu [ /exclude_dropped_users ]: Excludes operations made by dropped users
/i [ /ignore_case ]: Ignores the letter case of names and values to filter

Additional options
/pr:arg [ /project ]: Project file path
/ie [ /ignore_errors ]: Ignores errors occurred during the analysis (e.g. unknown user). If set, no exceptions will be thrown by the application, they will be logged instead
/tm [ /timing ]: Outputs the begin and end time of a process

Additional options for output
/? [ /help ]: prints help
/af:arg [ /argfile ]: an XML file with options
/cw:arg [ /console_width ]: specifies the width of console output
/f [ /force ]: overrides existing file of same name
/out:arg [ /out_to ]: redirects console output to specified file
/t [ /test ]: outputs parsed command line switches without taking any actions
/v [ /verbose ]: prints messages that are suppressed by default

Special keys
To abort program execution, use Ctrl+C or Ctrl+Break

Examples

The following CLI example creates a rollback (UNDO) script (D:\undo.sql) output with transactions and operations, using the following data sources and filters:

• The ACMEDB database hosted on the SQL Server instance named ACME_SQLSERVER
• The online transaction log as the data source (ACMEDB_log), the database backup (C:\BackupSQLServer\ACMEDB_Backup.bak) file, and detached transaction log (C:\BackupSQLServer\ACMEDB_log.ldf)
• The Date/Time range filter: from 2/1/2014 to 3/14/2014
• The Operations filter: INSERT (DML) and DROPTABLE (DDL) operations
• The Tables filter: without operations on dropped tables
• The Transactions filter: only the Committed transactions

1. ApexSQLLog.exe /verbose /server:ACME_SQLSERVER /database:ACMEDB /trusted /undo:D:\undo.sql /online:ACMEDB_log /backup:C:\ BackupSQLServer\ACMEDB_Backup.bak /detached:C:\BackupSQLServer\ACMEDB_log.ldf/operations:INSERT DROPTABLE /transactions:COMMIT /from:”2014-02-01 00:00:00.000” /to:”2014-03-14 00.00.00.000” /exclude_dropped_tables /force

The following CLI example creates an XML file (D:\export.xml) output with transactions and operations, using the following data sources and filters:

• The ACMEDB database hosted on the SQL Server instance named ACME_SQLSERVER
• Only the online transaction log as the data source
• The Date/Time range filter: the last 24 hours in data sources
• The Operations filter: all DML operations, without DDL operations
• The Tables filter: only the Customers and Invoices tables
• The Transactions filter: the Committed, Aborted, and Running transactions
• The Users filter: only the Jack login

1. ApexSQLLog.exe /verbose /server:ACME_SQLSERVER /database:ACMEDB /trusted /ec:begintime endtime lsn operation schema object transactionid user /xml:D:\export.xml/operations:DMLALL /transactions:ROLLBACK COMMIT BEGIN /tables:Customers Invoices /users:Jack /force