How to get logs from a oracle database table other than the audit table
I am interested in integrating some application specific logs which are stored in an oracle database, but the logs are stored xyz table instead of audit table. Pls help me out with the machanism to fetch those via ODBC connector.
Thanks in advance.
Thanks for the response...I have not tried custom ESI using ODBC...I dont know how to extract logs from the table where the logs are stored in oracle database...If you can help me how to get logs from a different table other than audit table in oracle database.....
hi Sandeep, this is little complex (may be contact Pro srv). you need to define the GOTS file first.
Here is some information.
GOTS – Generic ODBC Type Specification
The GOTS file contains all the input needed by the ODBC service to fetch event data from the input database. To begin with, the file contains an SQL query which returns the event details. The service issues this query on the associated DSN at periodic intervals. To ensure than data fetched in a scheduled pull isn’t fetched again in the subsequent pulls – GOTS also specifies a tracking value. This value is usually a column in the table with a monotonically increasing value.
Along with the tracking value an SQL max-tracking query is required that returns the maximum value of the tracking column after a given scheduled pull. This maximum value is preserved by the service and is used in the next pull to filter out the old rows from the database table. The event data query can refer to this value using the %TRACKING% placeholder.
Consider the following GOTS file which can be used with the database defined in the previous section:
<?xml version="1.0" encoding="UTF-8"?>
SELECT * FROM orders.csv WHERE order_id > %TRACKING% ORDER BY order_id
<maxTrackingQuery>SELECT MAX(order_id) FROM orders.csv</maxTrackingQuery>
The file specifies the device tag and delimiters which will be used in the normalized event text. The database query for events is specified in the dataQuery tag and the trackingColumn is identified as order_id. The maxTrackingQuery specifies how the value of the %TRACKING% placeholder will be computed after every fetch. Lastly, GOTS also specifies the column which should be used as the message id in the normalized text.
Thanks Vikas for your response...
Is there any trining for writing the GOTS provided by RSA....or may be any advance training for writing UDS and how to integrate devices which are not supported by rsa envision...it would be of great help and if this kind of training can be there in india specifically in mumbai...I would surely like to attend the same...
Thanks again for your response....waiting for your reply...
You first need to know which table that the logs are located, also make sure you have an account your DB team can provide for you to connect to the DB. I use a SQL developer tool to connect to the DB and run test queries to see the data that is in the table. Once you have the columns you want from the table(s) then you need to create your connection. You need to decide if you will use date/time or something else for tracking your data.
This is a custom coding I have to connect to a Oracle DB to pull logs from multiple tables.
SELECT a.ACTION, a.serial, d.user_id, to_char(a.from_event_time, 'Mon-dd HH24:MI:ss yyyy') "from_event_time", a.rss_name, a.rss_type, a.ess_user, a.TABLE_NAME, a.entity_key, a.origin, a.process_id, b.field_label, b.field_type, b.old_value_str, b.new_value_str, c.status, a.message, c.pre_msg, c.act_msg, c.post_msg FROM servername.audit_trail a, servername.audit_info b, servername.trans c, servername.RSS_USER d WHERE c.rss_user_name = d.rss_user_name(+) and c.rss_name = d.rss_name(+) and c.rss_type = d.rss_type(+) and a.trans_serial = c.serial(+) AND a.serial = b.serial(+) AND a.serial > '%TRACKING%' ORDER BY a.serial ASC
Thanks bob for your reply,
I want to know if I am using ODBC connector, What would be the username and password for accessing the table. will it be the same as we use fo audit table(audit_reader)?