EV audit Query

[boontatt1982]

Hi All, I having issue on doing query on the non-indexed Audit table as the number of records growth. The growht cause my SQL server run out of space and hang. Is there any chance i have alternative to query the audit? Regards BT

[Andy_Joyce_VERI]

Could you post the query you are using?

A

[boontatt1982]

Hi Andy, Here is the SQL query. select at.AuditID,at.AuditDate,at.info,u.UserName,c.CategoryName,sc.SubCategoryName,m.MachineName,s.Status,v.Vault From dbo.AuditTrail at Inner join dbo.Users u on at.UserID = u.UserID Inner join dbo.Categories c on at.CategoryID = c.CategoryID Inner join dbo.SubCategories sc on at.ubCategoryID = sc.SubCategoryID Inner join dbo.MachineNames m on at.MachineNameID = m.MachineNameID Inner join dbo.Status s on at.StatusID = s.StatusID Inner join dbo.Status v on at.VaultsID = v.VaultsID Where at.AuditID > ?

[Simon_B_]

The Audit DB can grow very large in a short amount of time on a busy system. So it might be necessary to truncate data from it or create a new DB.

Have a look at this technote: http://www.symantec.com/business/support/index?page=content&id=TECH35746

[boontatt1982]

Hi Simon, I do have SOP to rollover the audit database but my IT security need 6 mths data in the audit table, 7 years records in offsite and he will query the audit table on and off. I really hope Symantec can help on this. As i understand from other technote, Symantec will not support if i make any changes on the database schema which include create index or view table (virtual table with index). Regards BT

[Simon_B_]

You could put the Audit DB on another server so that at least your productive EV Databases are not affected by poor performance or low disk space.

Regarding your support: This is correct, if you make any database changes that are not described by symantec it might be that a technician refers to this modification as possible reason and you will get no support. However: The Audit DB is simply a log DB and EV will run correctly without one.

This means: Should adding indices or creating views really lead to any problems with the auditing itself it should not affect ev production at all (besides that audit entries might be lost). You should always be able to simply delete it or to recover a backup of this particular database to revert changes.

[boontatt1982]

Yes i also think about putting the Audit DB to others server but the same problem will happen to the non-production server. As when my IT security run the query i will need a tempDB size > than AuditDB as the SQL server will load all the audit data and run a full scan. Imagine i need to set a side one 2TB space servers just for the my IT security query the log which I have difficulty for approval. Beside that the Audit DB recover mode are configured to be "full" recover do it requeried? What are the RPO from Symantec suggest?

[Simon_B_]

Thats correct, putting the DB on another server does not solve the problem, it would only be a mitigation of the side effects.

You do not need full recovery mode for the audit DB as you would have no real advantage of being able to perform a point in time recovery. In fact almost all of our customers have simple recovery mode configured as a point in time recovery using logs will most certainly inconsistant (because the backup mode is not enabled).

[boontatt1982]

Hi Simon, Will there be any patch/version include index in future (Currently run on EV 9.0)? Is there any technote on the recovery mode? Regards BT

[boontatt1982]

Hi Anyone Please help !!!

[Simon_B_]

I am not aware of any planned changes on this behaviour. EV 10 is out in beta but idk if something was changed regarding auditing there. I might be able to have a look at a beta test system later that day.

You could however post an idea to add indices here: https://www-secure.symantec.com/connect/backup-and-archiving/ideas

[Simon_B_ · Accepted solution]

Just checked in my ev10 test environment: The AuditTrail table also does not have any indices. I'd suggest that you create an idea via the link posted earlier.