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.
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.
First, create a new job group that will include all the custom jobs you create. This is important for two reasons:
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.
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.
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.
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.
Go to the Data Source tab. In the Data Collector drop-down, choose SQL.
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.
Click Finish to return to the Query Properties screen and then click OK to exit.
The new query will be shown on the Query Selection screen:
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.
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.
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.
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.
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.
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.
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.
The new query will be shown on the Query Selection screen:
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.
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.
To learn more about how Stealthbits, now part of Netwrix, can help you properly audit your IT infrastructure, visit the StealthAUDIT product page.
Sujith Kumar has over 25 years of professional experience in the IT industry. Sujith has been extensively involved in designing and delivering innovative solutions for the Fortune 500 companies in the United States and across the globe for disaster recovery and high availability preparedness initiatives. Recently after leaving Quest Software/Dell after 19 years of service he was working at Cirro, Inc. focusing on database management and security. His main focus and area of interest is anything data related.
Sujith has a Master of Science in engineering degree from Texas A&M University and a Bachelor of Science in engineering degree from Bangalore University and has published several articles in referred journals and delivered presentations at several events.
Proper data security begins with a strong foundation. Find out what you're standing on with a free deep-dive into the security of your Structured and Unstructured Data, Active Directory, and Windows infrastructure.Read more
Start a Free Stealthbits Trial!
No risk. No obligation.