Auditing DBA Activity

DBA Activity Audit

In the DBA Activity tab of the ITM On-Prem Web Console, you can monitor all SQL queries that were executed by DBAs against production databases.

DBA Activity Auditing provides monitoring of SQL queries executed by DBAs against production databases. SQL query activity is captured by ObserveIT when the DBA is using a DB management tool on an ObserveIT-monitored computer.

A recommended configuration is to ensure that all DBAs for whom recording is required must connect through a Windows gateway, on which the ObserveIT Agent and the DB management tool application are installed.

Using ObserveIT, administrators and auditors can review all SQL queries performed on a given date or filter results by database, DB User, endpoint, login ID, or any text contained within the queries. SQL queries are also included in the session activity details displayed in the Endpoint Diary and User Diary pages. When using the Search page in Metadata (user activity log) mode, text matches within SQL queries will also return the relevant sessions in the search results.

SQL query activity is captured by ObserveIT when the DBA is using a DB management tool on an ObserveIT-monitored computer (Endpoint installed).

ObserveIT supports the following database management tool applications:

• Microsoft SQL Server Management Studio (SSMS) 2005, 2008, 2012, 2014, 2016, 18

  All versions of Microsoft SQL Server Management Studio Express (SSMSE) are currently not supported.

• Toad for Oracle 11.6, 12.10

• SQL*Plus 11.2.0.1.0

For information about configuring the SQL server, see Configuring the SQL Server.

The following example illustrates how SQL queries are captured by ObserveIT:

1. A user opens a remote RDP connection to the gateway in order to perform an SQL query.

2. The ObserveIT Agent captures the SQL query using the database management tool application on the gateway.

Querying SQL Server Sessions

SQL queries are included in the session activity details displayed in the Endpoint Diary and User Diary pages. When using the Search page in Metadata mode, text matches within SQL queries will also return the relevant sessions in the search results.

You can query the database for sessions according to any of the following criteria:

• Database name

• The server on which the user logged in

• Database user

• Login ID of the user

• Free text: Specific information that you are looking for (for example, specific user, alert, name of table, and so on)

• Specific time period, or start and end dates.

To view and search for SQL Server sessions

1. Click the DBA Activity tab.

   The DBA Activity page displays the results of SQL server queries on the currently selected default ITM On-Prem (ObserveIT) Database, in reverse chronological order, and according to the default date filter.

   The number of queries on the page out of the total number of queries is displayed. By default, the page shows up to 20 queries; you can change the default by selecting 50 or 100 from the Items per page drop-down list above the table.

   

   For each item in the table, the following information is displayed:

   • Time: The time that the SQL query occurred.

   • SQL Query: The content of the SQL query.

   • Database: The database on which the SQL query was performed.

   • DB User: The name of the database user.

   • Details icon: Enables you to view details about the SQL query session.

   • Video icon: Enables you to replay a video of the SQL query session.

2. To change the criteria for the SQL queries display, click the icon next to Filters to expand the search fields.

   

   Specify the search criteria according to which you want to perform an SQL Server query, as follows:

   1. In the Database field, specify the required database (or click the button to select it from a list of databases).

   2. In the Endpoint field, specify the endpoint to which the user is logged in (or click the button to select it from a list of endpoints).

   3. In the DB User field, specify the name of the database user (or click the button to select it from a list of database users).

   4. In the Login field, specify the login name of the user (or click the button to select it from a list of Login names).

   5. In the Query Text field, you can enter any specific text for your search.

   6. Under Period, you can filter your search criteria further by specifying a time period, or start and end dates.

3. When you have finished defining the criteria for the SQL Server session queries, click the Search button.

   The page refreshes to display a list of sessions according to the specified criteria.

   SQL Server queries that were performed on a session, will also be displayed at the end of the session in the Search tab, Endpoint Diary, User Diary, or Archive Search.

 

To view details of an SQL query session

• In the DBA Activity table, click the icon next to the SQL query whose details you want to view.

  A window opens displaying the details of the selected SQL query.

  Use the Up/Down arrows to browse between all the SQL query activities in the recorded session.

  

  From this window, you can also view a video of the selected SQL query session by clicking the Session Video icon.

To view a video of an SQL Query session

• In the DBA Activity table, click the Video icon to the right of the SQL query in which you are interested.

  The ObserveIT Session Player opens, enabling you to replay the entire recorded session. For details, see Replaying User Sessions.

See Also

• DBA Activity Report Configuration