15 Replies Latest reply on Jan 9, 2019 9:30 AM by Roger Palmen

    AF Audit Trail cleanup

    Roger Palmen

      Hi All,

       

      As i did not find anything in the documentation, let's pick our community brain for ideas.

      We use the AF Audit Trail feature to capture changes on the AF database and that of course consumes more space over time. Is there any way to prune this data after a specific period of time? E.g. all records > 1 year?

       

      I'm not really knowledgeable on the CDC functionality used in SQLserver to make this work, so also don't really understand the SQLserver side of things to work on that side. The documentation on SQLserver hints on supporting this pruning, so curious if that can be used in some way.

        • Re: AF Audit Trail cleanup
          Roger Palmen

          One of the other questions i have is the huge size of two CDC tables:

          • cdc.dbo_AFElementAttributeValue27_CT has 5M6 records consuming almost 22Gb. What are the possible causes of that? We cannot have had that many changes on the values/ We don't (or should not) have Analysis writing back to Attibutes. We do have a lot of Ad-Hoc Analysis (Analysis DataReference), but my assumption is that those should not cause this # of records. The dbo.AFElementAttributeValue table is just 2765 records summing to 2.7Mb, which is a ratio (on records) of 2000:1. Does that mean i have 2000 changes captured for every attribute???
          • cdc.dbo_AFEventFrameAttributeValue27-CT has 31M records consuming almost 19Gb. The underlyhing table dbo_AFEventFrameAttributeValue has 7M5 records for 2Gb. That is a ratio of 3:1 for the changes, meaning data base been recaptured at least 3 times on average?
            • Re: AF Audit Trail cleanup
              scheung

              You might want to take stock of the change records broken down by attributes like __$operation or rid.

               

              The __$operation would give you insight on what types of changes are being made. For example, a lot of updates would mean a lot of records with __$operation 3 (old value) and 4 (new value).

               

              The rid would give you insight on if changes are being made to the same attribute values over and over.

               

              Given the size of the tables, I suspect there is one workflow that regularly updates element attribute values and another workflow which occasionally updates event frame attribute values.

               

              If you need additional assistance, I suggest reaching out to our Technical Support team.

                • Re: AF Audit Trail cleanup
                  Roger Palmen

                  Hi Sam,

                  Thanks for the excellent info! Really helpful, you showed me the smoking gun. We indeed have an application that updates EventFrame attributes regularly, and that causes the bulk of the Audit Trail records. I will look into pruning specific sets of data for this AF database that we don't need to Audit in the first place.

                  • Re: AF Audit Trail cleanup
                    Roger Palmen

                    I noticed another application that causes a lot of records, and that is the Integrator for Business Analytics, which keeps a DataCache in AF. Just a quick analysis shows about 50% to 75% of the Audit Trail records are caused by entries for the Integrator in the configuration database.

                     

                    As that is a real issue, i will create a techsupport ticket for that...

                    1 of 1 people found this helpful
                • Re: AF Audit Trail cleanup
                  scheung

                  There are 2 SQL Agent jobs to service the audit trail feature, a:

                   

                  1. Capture job (e.g. cdc.PIFD_capture)
                  2. Cleanup job (e.g. cdc.PIFD_cleanup)

                   

                  The cleanup job can help you prune your change tables. You'll want to:

                   

                  1. Configure the cleanup job (e.g. set the desired retention period)
                  2. Enable the cleanup job

                   

                  The cleanup job can be configured by executing sys.sp_cdc_change_job. The following example changes PIFD's CDC cleanup job to have a 1 year retention period:

                   

                  --configure cleanup job retention for 1 year (525600 minutes)
                  USE PIFD;
                  EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention=525600;
                  

                   

                  To confirm the settings for the job, we can use sys.sp_cdc_help_jobs:

                   

                  --confirm settings
                  USE PIFD;
                  EXEC sys.sp_cdc_help_jobs;
                  

                   

                  Enabling the cleanup job can be done with SSMS or with TSQL (dbo.sp_update_job). In SSMS:

                   

                  1. Expand SQL Server Agent
                  2. Expand Jobs
                  3. Right-click on the cleanup job and click Enable

                   

                  Further customizations of the cleanup job might be editing its threshold value (by default it deletes in chunks of 5000) or changing its schedule (by default it runs nightly at 2am).

                  1 of 1 people found this helpful
                  • Re: AF Audit Trail cleanup
                    Roger Palmen

                    For reference, i got this information from Techsupport, so eagerly awaiting that release:

                     

                    In the next version of AF which is about to be released (June) we have enhanced AF audit trail and AFdiag, and we have options to include a clean-up job and include users to that clean up job. So if the PI Integrator for BA service is running under a specific user you will be able to filter by that user.

                     

                     

                      • Re: AF Audit Trail cleanup
                        Roger Palmen

                        Just following up on this. I was not able to find much more details than the list of AFDIAG parameters in the documentation: https://livelibrary.osisoft.com/LiveLibrary/content/en/server-v11/GUID-7092DD14-7901-4D63-8B9D-4414C569EA5F

                        So if i'm correct, i need to enable audit trail cleanup /ATC , and add the service accounts /ATCA:<<serviceaccount>> that i need to have removed from the audit trail?

                          • Re: AF Audit Trail cleanup
                            David Hearn

                            You are correct, those are the AFDiag options you need to use for the Audit Trail Cleanup. The /ATCR option is used to remove a user that was previously added using the /ATCA option from the table of users being cleaned up from the Audit Trail table.

                            2 of 2 people found this helpful
                              • Re: AF Audit Trail cleanup
                                Roger Palmen

                                Any more details how this cleanup works? How fast or often are records cleared?

                                 

                                In my case i've struggled with a transaction log consuming lots of space due to the pruning, but as i don't know exactly how this works it's difficult to know how long to keep temporary measures up to deal with the higher load on SQL.

                                  • Re: AF Audit Trail cleanup
                                    scheung

                                    Enabling audit trail cleanup (afdiag /ATC) creates a new SQL Agent job (OSIsoft Cleanup CDC). The job calls a cleanup sproc to delete change table data associated with users on the audit trail cleanup list (defined with afdiag /ATCA). The job is scheduled to run every 10 minutes by default.

                                     

                                    Note: Audit trail record retrieval should ignore any records which are waiting to be cleaned up.

                                    1 of 1 people found this helpful
                                      • Re: AF Audit Trail cleanup
                                        Roger Palmen

                                        Hi,

                                        I also have a techsupport call running on this topic, and i'll relay any useful generic information from that.

                                         

                                        The key problems with the audit trail cleanup:

                                        • You can't disable it, only enable. You can disable the entire audit trail, but that also deletes all data, so once you start collecting audit trail, and clearing audit trail, there is no going back, unless you want to delete all
                                        • A SQLserver Agent job is created for the Audit Trail cleanup, but something (i assume AFserver service) actually executes the job. So just disabling the job in SQLserver won't keep the job from running
                                        • If you have a large backlog of records for cleanup, there is no way to throttle the cleanup. This can cause a large strain on the database, with little ways to control that.
                                          • Re: AF Audit Trail cleanup
                                            scheung

                                            1. You can delete or disable the audit trail cleanup without disabling the audit trail feature. The /ATC switch is a Boolean argument and works similar to the other Boolean switches in afdiag (e.g. /Silent[-]). This means you can use the following syntax to turn on/off the cleanup feature:

                                             

                                            //To enable
                                            afdiag /ATC
                                            
                                            //To disable
                                            afdiag /ATC-
                                            
                                            

                                             

                                            Note: Turning off the audit trail cleanup feature will also delete the cleanup list entries.

                                             

                                            2. The SQL Server Agent is the one responsible for running the job, not the AF Application Service. Disabling the job will prevent the cleanup sproc from being called.

                                             

                                            3. Audit trail records are removed change table by change table for the users in the cleanup list. Beyond trimming the number of users in the cleanup list, we don't currently have any other mechanism to reduce the number of records removed during each pass. We're open to enhancing/changing this; please add any suggestions to User Feedback for OSIsoft Products and Services.

                                            1 of 1 people found this helpful