October 20, 2017 at 1:38 am
Hi all,
Am trying to produce a audit trail report for customer however after trying using SSMS Audit-->Database Audit Specification-->Database_Object_Access_Group and after viewing the logs, there are 1000 of entries captured and most of them belongs Database Principal. Is there any way show lesser entries?
I just want to captured action taken by user and not system.
What other methods your organization is using to generate Audit trail report? Thanks!
October 20, 2017 at 2:02 am
Hi,
You can use some of the available 3rd party tools.
In my office, we use ApexSQL Log - you can export and maintain audit trails as described here https://knowledgebase.apexsql.com/sql-script-bulk-insert-and-database-audit-trail-view-transaction-log-and-export-results/
Best regards
October 20, 2017 at 3:25 pm
Riic - Friday, October 20, 2017 2:29 AMThanks kosta83ni! My boss want to fully utilize SQL Server hence have to work within it.
You can create filters on the server audit you are referencing. There is a really small example in the documents - just says:
On the Filter page, enter a predicate, or WHERE clause,
to the server audit to specify additional options not available from the General page.
Enclose the predicate in parentheses; for example: (object_name = 'EmployeesTable')
You can find better examples if you search for them. They will generally be scripts. To get you started, take a look at this example:
Multiple Filters for Databases on SQL Server Server Level Audit
And this example shows the script as well as the GUI:
SQL Server Auditing: Looking Back
Another option would be to use extended events sessions to capture the information you need.
Sue
October 20, 2017 at 9:29 pm
Thank you Sue! Will try your suggestions else I will go for SQL profiler to do the tracking.
October 21, 2017 at 8:40 am
Riic - Friday, October 20, 2017 9:29 PMThank you Sue! Will try your suggestions else I will go for SQL profiler to do the tracking.
If you do try your own tracing, using Extended Events would be significantly better than using Profiler on SQL Server 2016.
There is a big difference in terms of overhead and then tie that in with this being an event that can generate a lot of data.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply