Applies to
ApexSQL Log
Summary
How to use advanced filtering options in ApexSQL Log in order to get the details faster.
Description
ApexSQL Log has the following filter options:
- Time range
- Operations
- Tables
- Transactions
- Users
- Field Values
- SPIDs
- Descriptions
Depending on your needs, filters can reduce the time needed for reading the data sources – attached transaction logs, detached transaction logs, and transaction log backups.
The default filter settings in ApexSQL Log provide a reading of the INSERT, UPDATE, and DELETE operations that occurred in the last 24 hours in all tables available in the database.
Narrowing down the search process by providing exact information will speed up the process. Recommended settings depend on a scenario, here are the general guidelines:
- Narrow down the time frame if possible
- Include only the operations that are necessary
- Filter out any tables that are not important in the scenario
- Determinate which transaction state to include
- If you know which user made the changes, filter out the rest
Settings and filters can be saved as a session .axlp file. Saved sessions can be re-used at any time.
The Time range filter
The Time range filter defines the time frame of transactions read in the data sources that will be displayed in the main grid or exported into a file. You can search for a specific number of hours of the oldest or latest transactions, the whole transaction log, or set a custom time range. Additionally, continuous auditing feature allows auditing to continue from the exact moment where the previous auditing job has finished, by reading the information on the last audited LSN from the specified tracking file.
The default setting is the last 24 hours.
The Operations filter
The Operations filter is used to narrow down the transactions found in the data sources to a specific operation or a set of operations. The Operations filter offers 3 data operations (Insert, Update, and Delete) and more than 40 schema (Data Definition Language DDL) operations. The latter can be grouped by the operation type into ALTER, CREATE, DROP, and OTHER. Due to system limitations, including any of the DDL operations will result in ignoring DML operations on a system tables.
By default, ApexSQL Log reads only the data (DML) operations.
The Tables filter
The table filter offers a possibility to choose specific tables to filter transactions. Tables are divided into two groups – user tables and system tables. Excluding the tables that are not necessary in your scenario will result in faster analyzing of a data sources.
By default, ApexSQL Log reads the transactions on all user tables, including the dropped ones. The system tables are excluded.
The next set of the filters will be shown after expanding Advanced Options section.
The Transactions filter
The Transactions filter is used to filter the transactions by their state. The database transactions can have the following states: committed, aborted, running, and unknown. Besides filtering by transaction state, the transactions can be filtered by the duration time. If the Minimum option is used, all transactions that lasted shorter than a specified time will be ignored.
By default, ApexSQL Log shows all transactions except aborted, regardless the duration time.
The Users filter
The Users filter enables filtering the transactions by the specific user(s) who made them. The filter lists all users found in the data sources, giving the option to select only one or multiple users.
By default all users are included.
The Field values filter
The Field values filter enables to filter the transactions by a specific table column value. Only the records that meet the specified criteria will be shown. The filter lists all available tables and their fields. You can choose the column to be equal, not equal, greater than, less than, greater than or equal to, less than or equal to, contains, and is null to the value specified in the filter.
By default, this filter is not used. Please note that filtering by values can be time consuming and performance intensive.
The SPIDs filter
The SPIDs filter can search for a specific Server Process ID or a set of them. Server Process IDs designate sessions in SQL Server, every time an application connects to SQL Server, a new connection (SPID) is created. Filtering this information in advance can distinguish the specific connection made to a SQL Server instance.
By default, this filter is not used.
The Descriptions filter
The Descriptions filter enables filtering the transactions by a transaction description. If the executed transaction has a description in their details, this filter can be applied. By using the Description filter, the data source will be filtered out to show only transactions that have met the condition.
By default, this filter is not used.
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 has occurred. Read the transaction log to find out who created, changed, or dropped a database object.
