SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Unable to filter by user

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Unable to filter by user
Author Message
Vegeta Lee



Joined: 20 Jul 2011
Posts: 5
Country: Hong Kong

Post Unable to filter by user Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
In which format do you enter report dates?
Mon Aug 15, 2011 8:29 am View user's profile Send private message
Vegeta Lee



Joined: 20 Jul 2011
Posts: 5
Country: Hong Kong

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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 View user's profile Send private message
Vegeta Lee



Joined: 20 Jul 2011
Posts: 5
Country: Hong Kong

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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 View user's profile Send private message
Vegeta Lee



Joined: 20 Jul 2011
Posts: 5
Country: Hong Kong

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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 View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
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


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.