SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Error in REP_SYS_AUDIT_TRAIL
Goto page Previous  1, 2, 3  Next
 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Error in REP_SYS_AUDIT_TRAIL
Author Message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I need to see column definitions before I can advise on the next step
Mon Jun 27, 2011 12:32 pm View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post column definitions in Central Repository Reply with quote
Columns in db_audit.STAGE$100001


Columns in db_audit.STAGE$100002


Rows in db_audit.REP_STAGE

Mon Jun 27, 2011 1:16 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
xactid column data type seems to be correct in the repository and it matches sysaudits_XX tables. Strangely, your SQL Server refuses to insert data into that column, complaining about some conversion. Please try changing the data -type in the repository to nvarchar(14)
Mon Jun 27, 2011 2:58 pm View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post You can only use SELECT or SELECT INTO with the special tabl Reply with quote
What about the error:
[DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]You can only use SELECT or SELECT INTO with the special table.
How can I fix this?
Mon Jun 27, 2011 3:18 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Please see my reply here http://www.softtreetech.com/support/phpBB2/viewtopic.php?p=26323#26323
Basically, let's fix the replication to repository first (this will also fix reporting and alerting) and when that is working we focus on the data deletion. Issue.
Mon Jun 27, 2011 4:02 pm View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Deleting audit data from sybsecurity.dbo.sysaudits_xx Reply with quote
I changed in Central Repository Deployement Tools in 'Update database server profile stored in repository' Keep Days and I chose 'All' as you suggested in post "http://www.softtreetech.com/support/phpBB2/viewtopic.php?p=26323#26323"

Then I dropped the tables STAGE$100001 and STAGE$100002, and I had permission problems, but then I realized that these tables must have the db_audit schema, I put the db_audit scheme with commands:
ALTER SCHEMA db_audit TRANSFER dbo.STAGE$100001;
ALTER SCHEMA db_audit TRANSFER dbo.STAGE$100002;

I ran the job "Audit Log Archiving Job for Sybase" of Alert Center Scheduler and it ran without error, it delayed 5 minutes.

Then, I ran the following query in SQL Server
"select * from db_audit.REP_SYS_AUDIT_TRAIL order by event_time desc"
and I got the 47212 records of tables: sybsecurity.sysaudits_01 and sybsecurity.sysaudits_02 with action_name: login, logout, select, table access, and so on. :-)

But records in sybsecurity.sysaudits_01 and sybsecurity.sysaudits_02 are still, they have not been cleared.

I returned at Central Repository Deployement Tools in 'Update database server profile stored in repository' Keep Days and I chose '30' but, the job "Audit Log Archiving Job for Sybase" failed:

ERROR: [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server] You can only use SELECT or SELECT INTO with the special table.

you can tell me, what I can do to fix this?







53.log:
Archiving method: DATA PUMP
Connecting to database...
Connected
Retrieving catalog data...
Catalog query execution time: 0.0 seconds.
Pre-processing: Truncating staging table db_audit.STAGE$100001...
Pre-processing: Truncate query execution time: 0.1 seconds.
Validating staging table db_audit.STAGE$100001...
Archiving audit data from sybsecurity.dbo.sysaudits_01...
Audit time range: 2011-06-28 11:34:14.06 to 2011-06-28 12:04:15.119 with timezone offset 0
Searching remote audit trail...
Copying records from remote audit trail into staging table...
Total audit data records copied: 0
process time: 0.02 seconds
performance: 0.0 records per second
Deleting audit data from sybsecurity.dbo.sysaudits_01...
Leaving 30 days of data in the audit trail, deleting everything before 2011-05-29 12:04:15.119
Audit data archiving failed for table sybsecurity.dbo.sysaudits_01. [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]You can only use SELECT or SELECT INTO with the special table.
ERROR: [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]You can only use SELECT or SELECT INTO with the special table.
Table skipped...

Pre-processing: Truncating staging table db_audit.STAGE$100002...
Pre-processing: Truncate query execution time: 0.13 seconds.
Validating staging table db_audit.STAGE$100002...
Archiving audit data from sybsecurity.dbo.sysaudits_02...
Audit time range: 2011-06-28 11:34:14.06 to 2011-06-28 12:04:15.119 with timezone offset 0
Searching remote audit trail...
Copying records from remote audit trail into staging table...
Total audit data records copied: 93
process time: 2.068 seconds
performance: 44.970985 records per second
Deleting audit data from sybsecurity.dbo.sysaudits_02...
Leaving 30 days of data in the audit trail, deleting everything before 2011-05-29 12:04:15.119
Audit data archiving failed for table sybsecurity.dbo.sysaudits_02. [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]You can only use SELECT or SELECT INTO with the special table.
ERROR: [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]You can only use SELECT or SELECT INTO with the special table.
Table skipped...
Tue Jun 28, 2011 1:11 pm View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post The reports continue with error Reply with quote
The reports continue with error although central repository was fixed.

70.log :
SQL: /R ObjectAccessAndStatementAudit /S @D7 crd_dbaudit
Getting properties of database profile desgyesen003...
JDBC interface: ./drivers/mss/sqljdbc.jar
JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
Database URL: jdbc:sqlserver://desgyesen003:2668;databaseName=crd_dbaudit;SelectMethod=cursor
User: db_audit
Connecting to database...
Connected
Running job #70...
Command: /R ObjectAccessAndStatementAudit /S @D7 crd_dbaudit
Executing report...
Reporting period: Tue Jun 21 12:12:57 COT 2011 - Wed Jun 29 12:12:57 COT 2011
Reporting period: Tue Jun 21 12:12:57 COT 2011 - Wed Jun 29 12:12:57 COT 2011
--- Start of Stack Trace ---
java.lang.IllegalStateException: System audit is not installed!
at com.softtreetech.dbaudit.reports.MssqlReports.checkSysAuditInstalled(MssqlReports.java:43)
at com.softtreetech.dbaudit.reports.MssqlReports.doObjectAccessAndStatementAuditReport(MssqlReports.java:959)
at com.softtreetech.dbaudit.commandhandler.AbstractCommandHandler.executeReport(AbstractCommandHandler.java:162)
at com.softtreetech.dbaudit.commandhandler.AbstractCommandHandler.execute(AbstractCommandHandler.java:88)
at com.softtreetech.dbaudit.command.LegacyCommandHandler.handleCommand(LegacyCommandHandler.java:33)
at AlertJob.main(Unknown Source)
--- End of Stack Trace ---
ERROR: Unable to run database query. System audit is not installed!


65.log :
SQL: /R LogonLogoffAndResourceUsageAudit /S @D7 sybsecurity
Getting properties of database profile Sybase6...
JDBC interface:
JDBC driver: sun.jdbc.odbc.JdbcOdbcDriver
Database URL: jdbc:odbc:sybase6_odbc
User: db_audit
Connecting to database...
Connected
Running job #65...
Command: /R LogonLogoffAndResourceUsageAudit /S @D7 sybsecurity
ERROR: Unable to run database query. Driver type cannot be determined






Tue Jun 28, 2011 1:28 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Okey. That sounds good, the replication seems to be working now, but the data deletion is not yet. We can now focus on that part.

Does the account you use with Alert Center to connect to Sybase database for the data replication have sa_role?
Tue Jun 28, 2011 5:27 pm View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
yes, the user db_audit has role: sa_role, sso_role in sybase
Tue Jun 28, 2011 5:35 pm View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Screen of role of db_audit in Sybase Reply with quote
Screen of role of db_audit in Sybase:

Tue Jun 28, 2011 5:37 pm View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
Screen of roles of db_audit in SQL Server:

Tue Jun 28, 2011 5:42 pm View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
If I select database server: Repository Server

I can generate reports of type:
Scheduled Reports (Consolidated Events for Previous Day)
:-)


And not,
Scheduled reports (System &User Activities)

ERROR: Unable to run database query. System audit is not installed!



If I select database server: Sybase

When I generate reports of type:
Scheduled Reports (Consolidated Events for Previous Day)

I get the following error:

ERROR: Unable to run database query. [DataDirect][ODBC Sybase Wire Protocol driver]Server]'d' is not a recognized dateadd option.

And the reports of type:
Scheduled reports (System &User Activities)

I get the following error:

ERROR: Unable to run database query. Driver type cannot be determined
Tue Jun 28, 2011 6:07 pm View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
74.log
Database profile: Sybase6
SQL: SELECT eventtime event_time, loginname login_name INTO #t FROM sybsecurity.dbo.sysaudits_01 WHERE event = 45 and eventmod = 1 and eventtime BETWEEN DateAdd(d, -1, convert(datetime, convert(varchar, GetDate(), 112), 112)) and convert(datetime, convert(varchar, GetDate(), 112), 112)
INSERT INTO #t SELECT eventtime event_time, loginname login_name FROM sybsecurity.dbo.sysaudits_02 WHERE event = 45 and eventmod = 1 and eventtime BETWEEN DateAdd(d, -1, convert(datetime, convert(varchar, GetDate(), 112), 112)) and convert(datetime, convert(varchar, GetDate(), 112), 112)
SELECT login_name, count(1) total_connects, min(event_time) first_event_time, max(event_time) last_event_time FROM #t GROUP BY login_name ORDER BY login_name ASC
DROP TABLE #t

Getting properties of database profile Sybase6...
JDBC interface:
JDBC driver: sun.jdbc.odbc.JdbcOdbcDriver
Database URL: jdbc:odbc:sybase6_odbc
User: db_audit
Connecting to database...
Connected
Running job #74...
ERROR: Unable to run database query. [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]'d' is not a recognized dateadd option.




65.log
SQL: /R LogonLogoffAndResourceUsageAudit /S @D7 sybsecurity
Getting properties of database profile Sybase6...
JDBC interface:
JDBC driver: sun.jdbc.odbc.JdbcOdbcDriver
Database URL: jdbc:odbc:sybase6_odbc
User: db_audit
Connecting to database...
Connected
Running job #65...
Command: /R LogonLogoffAndResourceUsageAudit /S @D7 sybsecurity
ERROR: Unable to run database query. Driver type cannot be determined
Tue Jun 28, 2011 6:07 pm View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
72.log

Database profile: Repository Server
SQL: /R LogonLogoffAndResourceUsageAudit /S @D7 crd_dbaudit
Getting properties of database profile Repository Server...
JDBC interface: ./drivers/mss/sqljdbc.jar
JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
Database URL: jdbc:sqlserver://desgyesen003:2668;databaseName=crd_dbaudit;SelectMethod=cursor
User: db_audit
Connecting to database...
Connected
Running job #72...
Command: /R LogonLogoffAndResourceUsageAudit /S @D7 crd_dbaudit
Executing report...
Reporting period: Tue Jun 21 15:01:10 COT 2011 - Wed Jun 29 15:01:10 COT 2011
Reporting period: Tue Jun 21 15:01:10 COT 2011 - Wed Jun 29 15:01:10 COT 2011
--- Start of Stack Trace ---
java.lang.IllegalStateException: System audit is not installed!
at com.softtreetech.dbaudit.reports.MssqlReports.checkSysAuditInstalled(MssqlReports.java:43)
at com.softtreetech.dbaudit.reports.MssqlReports.doLogonLogoffAndResourceUsageAuditReport(MssqlReports.java:855)
at com.softtreetech.dbaudit.commandhandler.AbstractCommandHandler.executeReport(AbstractCommandHandler.java:158)
at com.softtreetech.dbaudit.commandhandler.AbstractCommandHandler.execute(AbstractCommandHandler.java:88)
at com.softtreetech.dbaudit.command.LegacyCommandHandler.handleCommand(LegacyCommandHandler.java:33)
at AlertJob.main(Unknown Source)
--- End of Stack Trace ---
ERROR: Unable to run database query. System audit is not installed!
Tue Jun 28, 2011 6:09 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Please hold on, we are looking into "ERROR: [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server] You can only use SELECT or SELECT INTO with the special table." error

For all other issues, please make sure you use the Alert Center to run reports from the central repository. Don't try to simply connect DB Audit console to the repository and server and run graphical reports from there, they won't work, its a different type of system and setup. Also, when choosing reports to run using the alert Center, make sure you select them from "Alerts and Reports from the Central Repository" branch
Wed Jun 29, 2011 10:49 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
Goto page Previous  1, 2, 3  Next
Page 2 of 3

 
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.