 |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
Vegeta Lee
Joined: 20 Jul 2011 Posts: 5 Country: Hong Kong |
|
Unable to filter by user |
|
Hi,
I am going to retrieve data change log using the built in reports and try to filter by user name and date. However, when I input any selection criteria, the report shows "No record found" (Something with the same meaning to that). If I leave the criteria blank, data shown on the report but the report is > 14000 pages.
I have checked that the user name I specified in the selection criteria is the same to the value of column "audit_login" in the data audit trail table. Same problem occured when using date as selection criteria. (I've tried all 3 date formats as specified in the selection criteria page but all resulted in "No record found") What's wrong with my input?
I have to compile the report within this week before auditor's visit. I will be appericated if I can receive assistance ASAP.
Thanks.
|
|
Mon Aug 15, 2011 7:09 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
In which format do you enter report dates?
|
|
Mon Aug 15, 2011 8:29 am |
|
 |
Vegeta Lee
Joined: 20 Jul 2011 Posts: 5 Country: Hong Kong |
|
|
|
I've tried yyyy-mm-dd, dd/mm/yyyy and also d/m/yyyy. I am using SQL Server 2008.
I have tried not to tick the "Filter by date" checkbox , leaving the date textboxes blank and just filter by user name also but still no result.
|
|
Mon Aug 15, 2011 9:13 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
Do you enter user name exactly as it appears on the report? Can you copy and paste user name value from report? Does that work for you?
|
|
Mon Aug 15, 2011 10:14 am |
|
 |
Vegeta Lee
Joined: 20 Jul 2011 Posts: 5 Country: Hong Kong |
|
|
|
I've figured out what's wrong with the user name.
In my server, user logins are mapped to database with default schema 'dbo'. When audit log is captured, it save the user name used to login database (e.g. 'abc') into column 'audit_login' and 'dbo' into the 'audit_user' column of the audit trail table.
When I run the report "Audit Trail by Schema", I entered 'abc' as user name filter and probably DB_AUDIT map the criteria to the 'audit_user' instead of 'audit_login' therefore I did not get the result.
I would like to check data changes made by 'abc' instead of 'dbo'. Do you have any suggestion?
For the date criteria, I have tried DD/MM/YYYY, MM/DD/YYYY, YYYY-MM-DD and also D/M/YYYY and M/D/YYYY and all return "No record found".
|
|
Tue Aug 16, 2011 7:53 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
Which DB Audit version are you running?
Do date filters in YYYY-MM-DD format work for other reports?
For your information, behind the scenes DB Audit constructs SQL query for the report appending selected filters to the end. So that in case of a date filter, it adds something like ... AND audit_timestamp BETWEEN date 1 here' AND 'date 2 here'
That's why it is important to enter dates ina the format that can be understood by your database.
|
|
Tue Aug 16, 2011 8:34 am |
|
 |
Vegeta Lee
Joined: 20 Jul 2011 Posts: 5 Country: Hong Kong |
|
|
|
My DB_AUDIT is version 4.2.26.1
I've tried YYYY-MM-DD format again for the "Audit Trail by Schema" report in my development environment and I got result when I input 2011-08-16 and 2011-08-17 as from and to date but no result when I input 2011-08-16 for both from and to date. All data are changed in 2011-08-16.
I have tried the Login / Logout Activity Report and the result is the same.
Regarding the user name criteria problem, I found that the 'Audit Trail by Table' report use report column "Login Name" as filter whereas 'Audit Trail by Schema' report use report column "Database User" as filter. Do you have any suggestion so that I can obtain the 'Trail by Schema' report using "Network User" instead of "Database User"? It's the key report I have to present to the auditor.
|
|
Tue Aug 16, 2011 9:07 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
That makes sense. You would need to enter dates that are at least 1 day apart. Please keep in mind that audit record timestamp values contain time portion and the server-side filter is applied as BETWEEN 2011-08-16 AND 2011-08-17 which means any date time between these 2 dates
As for the user filter, you can run report for specific date range and after that use client-side filters (in the report righ-click and choose Filter or click View/Filter menu) to filter by login name or any other available column or expression.
|
|
Tue Aug 16, 2011 9:29 am |
|
 |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|