Detailed Audit
I have an application which I would like to audit at a detailed level. I have Informix 11.5 FC5 auditing turned on. The application can make more than 1000 events in a 60 second period. I can sequentially see all the audit events that happened on different tables. I was wondering if I can capture the actual SQL that was issued by the application user corresponding to the audit event... like for ACTB. For now all I can see for such an event is 0:ACTB:csp_db:dmuser:env_code:160 Thanks Chandan
CHANDAN PAI wrote: > happened on different tables. I was wondering if I can capture the actual SQL > that was issued by the application user corresponding to the audit event... > like for ACTB. SQLTRACE? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Hi, You can capture detailed (in one of three configurable levels of detail) data on SQL by using the SQL tracing facility of IDS 11.50. Look up the SQLTRACE configuration parameter and associated commands within the task and admin functions. You can choose when and how much to capture, and the results are available in tables in the sysmaster database. Do note, however, that the trace data is only stored in memory. You will have to take it from there and store it if you want to persist the collected traces. You can trace based on the database, userid, session or combinations thereof. Cheers, Dick Snoke Executive IT Specialist IBM Software Group - ChannelWorks Tel: (404) 487-1595 Email: dsnoke@us.ibm.com From: "CHANDAN PAI" <chandanpai@yahoo.com> To: ids@iiug.org Date: 03/04/10 05:15 PM Subject: Detailed Audit [19230] Sent by: ids-bounces@iiug.org I have an application which I would like to audit at a detailed level. I have Informix 11.5 FC5 auditing turned on. The application can make more than 1000 events in a 60 second period. I can sequentially see all the audit events that happened on different tables. I was wondering if I can capture the actual SQL that was issued by the application user corresponding to the audit event... like for ACTB. For now all I can see for such an event is 0:ACTB:csp_db:dmuser:env_code:160 Thanks Chandan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
IDS SQL capture can capture most of your SQL for you for a given period if you give it a big enough memory cache to work with and enough disk to save the SQL into a table. However, at that kind of transaction rate, especially along with IDS Auditing enabled, this will seriously impact on performance. You should consider an external product to capture the SQLs. One such is iWatch from ExactSolutions, Inc. (http://www.exact-solutions.com/) Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 4, 2010 at 5:14 PM, CHANDAN PAI <chandanpai@yahoo.com> wrote: > I have an application which I would like to audit at a detailed level. I > have > Informix 11.5 FC5 auditing turned on. The application can make more than > 1000 > events in a 60 second period. I can sequentially see all the audit events > that > happened on different tables. I was wondering if I can capture the actual > SQL > that was issued by the application user corresponding to the audit event... > like for ACTB. > > For now all I can see for such an event is > 0:ACTB:csp_db:dmuser:env_code:160 > > Thanks > Chandan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747857cb8b7ec0481018157
IDS does not provide any feature specifically designed for capturing SQL statements. You may find other tools useful, but most have limitations: - SQLTRACE (captures to memory, and you'd have to make sure you put it file before the capture buffer wraps around) - SQLIDEBUG (a feature directed to support needs) - External tools (one was already mentioned, and recently IBM acquired Guardium, a company specialized in auditing) It could help us providing more help if we knew what were the needs. Also consider the amount of information you plan to generate. Are you prepared to process it? Regards. On Thu, Mar 4, 2010 at 10:14 PM, CHANDAN PAI <chandanpai@yahoo.com> wrote: > I have an application which I would like to audit at a detailed level. I > have > Informix 11.5 FC5 auditing turned on. The application can make more than > 1000 > events in a 60 second period. I can sequentially see all the audit events > that > happened on different tables. I was wondering if I can capture the actual > SQL > that was issued by the application user corresponding to the audit event... > like for ACTB. > > For now all I can see for such an event is > 0:ACTB:csp_db:dmuser:env_code:160 > > Thanks > Chandan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6d7eea0df598a04813e05df