 |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
I need to see column definitions before I can advise on the next step
|
|
Mon Jun 27, 2011 12:32 pm |
|
 |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
column definitions in Central Repository |
|
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 |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
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 |
|
 |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
You can only use SELECT or SELECT INTO with the special tabl |
|
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 |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
Mon Jun 27, 2011 4:02 pm |
|
 |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
Deleting audit data from sybsecurity.dbo.sysaudits_xx |
|
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 |
|
 |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
The reports continue with error |
|
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 |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
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 |
|
 |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
|
yes, the user db_audit has role: sa_role, sso_role in sybase
|
|
Tue Jun 28, 2011 5:35 pm |
|
 |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
Screen of role of db_audit in Sybase |
|
Screen of role of db_audit in Sybase:

|
|
Tue Jun 28, 2011 5:37 pm |
|
 |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
|
Screen of roles of db_audit in SQL Server:

|
|
Tue Jun 28, 2011 5:42 pm |
|
 |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
|
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 |
|
 |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
|
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 |
|
 |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
|
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 |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
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 |
|
 |
|
|
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
|
|
|