PROTIP – How to Purge Data in StealthAUDIT

If you have been using StealthAUDIT for your data access governance (DAG) and compliance needs, then you have likely come across situations where you would like to purge data pertaining to a specific host being monitored. In addition, when you upgrade to a newer release of StealthAUDIT, there might be a need to drop all the tables related to a specific job.

While the StealthAUDIT back-end database uses SQL Server with a published and open data model, it is not advisable to delete data or drop tables from the StealthAUDIT database directly unless directed by our support organization. Indeed, it is almost impossible to perform these tasks directly because the back-end database is typically quite large and utilizes primary/foreign key relationships extensively. Therefore, all the data collectors in StealthAUDIT include built-in utilities for purging data.

In this blog, I will walk you through the steps required to purge data related to specific host and to drop the tables related to a specific job using the StealthAUDIT console. Please not that this blog is intended to serve as a quick guide on how to perform these tasks; it does not replace the StealthAUDIT Installation Guide and supplemental documentation.

How to Drop Tables Related to a Specific Job

For this example, I’ll detail how to delete all the StealthAUDIT tables related to the Microsoft SQL Server job. The process is similar for all other jobs.

Step 1. Create a new job group for your custom jobs.

First, create a new job group that will include all the custom jobs you create. This is important for two reasons:

• It minimizes the issues that you might run into when upgrading StealthAUDIT.
• You won’t unintentionally run a custom job that was added to the stock job group.

Let’s call the new job group zCustom_Jobs. The prefix “z” will ensure that it is the last entry in the job group tree in the StealthAUDIT console.

Step 2. Create a new custom job.

Now let’s create the custom job we need. Either right-click the job group zCustom_Jobs and choose Create Job, or use the keyboard shortcut Ctrl-Alt-A.

Give the new job a name. Since we want to delete all the StealthAUDIT tables related to the Microsoft SQL Server job, let’s use the name Drop_SQL_Tables.

Step 3. Create a new query.

Expand the Configure node under the Drop_SQL_Tables job and select Queries. On the Query Selection screen that appears, click the Create Query link.

Step 4. Specify the general properties of the query.

On the Query Properties screen, specify a name and description for the query in the General tab. The Table field can be left set to DEFAULT since there is no reason to define a customer table for this job. Click OK.

Step 5. Choose the data collector.

Go to the Data Source tab. In the Data Collector drop-down, choose SQL.

Step 6. Configure the job to remove all tables related to a job group.

Click the Configure button to start the SQL Data Collector Wizard. Since we want to delete the tables related to a SQL Server job, in the Category section, scroll down to the Microsoft SQL Server section and choose Utilities -> Remove StealthAUDIT Tables. Then click Next.

Step 7. Exit the wizard.

Click Finish to return to the Query Properties screen and then click OK to exit.

Step 8. Check the query.

The new query will be shown on the Query Selection screen:

Step 9. Choose the target database.

Now that the job is created, we need to select the target database. In the Configure node, choose Hosts, which will bring up the Host Selection screen on the right. Ensure that only the Local host is being targeted.

Step 10. Execute the job.

Last, right-click the job name Drop_SQL_Tables and choose the Run Job option. The job should will the desired tables and display a status of Success.

How to Delete Data for a Specific Host

Under certain situations — for example, when a server is decommissioned — you might want to delete all the data related to a particular host in StealthAUDIT. Let’s step through how to delete data for a Microsoft SQL Server host.

Step 1. Perform steps 1 through 5 from the previous section.

First, perform steps 1 through 5 as detailed in the previous section. However, in Step 2, choose a different name for the job, such as Delete_Host_Data.

Step 2. Configure the new job.

Click the Configure button to start the SQL Data Collector Wizard. Scroll down to the end of the Category section and choose the Utilities -> Remove StealthAUDIT Data option. (You should use this option to remove tables for any database host.) Then click Next.

Step 3. Select the host where data should be purged.

In the Filters section of the wizard, choose Only select database objects in the Filter options section. Then click the Retrieve button. In the Available database objects section, expand the Microsoft SQL Server group, select the host where data needs to be purged and click Add. (Note that it is possible to add more than one host here; there is no need to run the same job multiple times to purge data on several hosts.) Then click Next.

Step 4. Choose the type of data to be deleted.

In the Data removal settings section of the wizard, choose the types of data you want to delete: permissions, user activity audits, sensitive data and/or orphaned rows. Then click Next.

Step 5. Check the query.

The new query will be shown on the Query Selection screen:

Step 6. Choose the target host.

Now that the job is created, we need to target the correct database. In the Configure node, choose Hosts, which will bring up the Host Selection screen on the right. Ensure that only the Local host is being targeted.

Step 7. Run the job.

Last, right-click the job name Delete_Host_Data and choose the Run Job option. The job should delete the desired data and show a status of Success.

For More Information

To learn more about how Stealthbits, now part of Netwrix, can help you properly audit your IT infrastructure, visit the StealthAUDIT product page.