SmartConnector for IBM DB2 Multiple Instance UDB Audit File

0 Likes
over 8 years ago

Labels:

SmartConnector Configuration Guides - File
Comment List
Anonymous
  • Hi Mark,

    First of all congratulations on your move to HPE...

    Now that you are on the other side of the wall, have you been able to get any traction on solving some of the other pain points that have been aired on the forums with regards to the DB2 SmartConnector? :-)

    Cheers,

    Carl

  • Hi Roy,

    The JIRA for support of DB2 10.5 is CON-15703.

    I was told on Monday of this week (April 4th) that supporting 10.5 has finally been re-prioritized for development.

    The log format changes between 10.1 and 10.5 are very minor (a few extra columns) so I'm hoping that we will see this supported in one of the next two SmartConnector releases.  No promises of course but if you (and any other customers) waiting for 10.5 support also open a ticket and reference the above JIRA, the priority for getting this supported will increase further.

    Having said that, my DB2 DBAs just told me that 11.0 will be released soon so we are going to be starting this process all over again really soon to get the latest version supported!  At least for the next one, I will be opening the JIRA for DB2 11.0 as soon as I have a spinning copy of this version in my environment.

    Best regards,

    Carl

  • Is there any info available about the support for version 10.5?

  • Yes, DB2 *.del text based files are exported along with a binary auditlobs file.  The Execute and Context messages have a position reference to the SQL statements stored in the binary file. I understand statements can be long and ArcSight fields are limited.  Even if truncated, having the statement text allows the analyst and rules to have context as to what is going on.  ArcSight would simply need to use reference position information to access the statement and then include it in the CEF.

    I too experience non-ASCII characters in the logs and same ends up in the ArcSight field. I reported this, yet have not focused on these transaction reference IDs. 

    I'm currently working with parsing specific to 9.7.  I'm glad you bring up these version and I hope ArcSight will also fix these versions.  I will add JIRA CON-15703 to my request also.

    Carl, thanks so much for replying.

  • Hi Mark,

    I'm fighting on and off with DB2 as well.  You are working with the *del files right?

    Looking at IBM's documentation, the StatementText is only seen in the Context and Execute audit categories.  This field is a CLOB of size 8M which I'm assuming means maximum size of 8*1024*1024.  Not sure what, if any, field in a CEF event can accommodate that size.  For my companies purposes, we don't currently require those types of events so I'm only looking at this from the outside... but if the SQL statement can be that large, is Arcsight the right tool to collect them?  Do you see the StatementText (SQL query) in the 15th field of the raw context.del or execute.del files?  If they are aren't in the files that Arcsight parses, you for sure won't see the events in Logger/ESM.  I'm just curious if you see the SQL statements and how large the statements are if you see them.

    I have a few items that you might be able to answer that are currently causing me headaches.  Hoping you can provide insight.

    Do you experience/have a solution for getting non-ASCII characters in the log files? I'm 99% sure that the non-ASCII characters come from the "Local Transaction ID"  and the "Global Transaction ID"  fields.  Both of these fields are described as "VARCHAR(10) FOR BIT DATA" and I'm thinking at this point that my DBA needs to tweak his config to convert the bit data to ASCII but I only started looking at this specific issue at the beginning of the week and am waiting for more info from my DBAs. 

    Also, unless my internal DBAs are adding values to each line of the CSV files, the number of tokens in the each of DB2 parsers does not match the number in almost all 8 DB2 audit log types.  I see this for DB2 10.1 and am curious if you see the same thing.

    Finally, if you are looking for the SC to support DB2 10.5, I have an enhancement opened for that one.  If you need it, please add let support know.  The JIRA number is CON-15703.


    Best

    Carl

  • My Notes:

    Here is a problem statement:  “The ArcSight SmartConnector for IBM DB2 Database Audit logs are missing a critical piece of information as to what is happening. The SmartConnector is NOT sending any database statements”.

    FAQs:

    Is something broken? The code works. Yet the process of mapping and providing the correct information seems incomplete or wrong.

    Is vendor (IBM) logging everything proper? Yes, the data is in the auditlobs binary file, offset given.

    Are there known ways to get the statements? Yes, documented on IBM public website.

    Is it documented that the SmartConnector will provide this? Yes, Pages 12,13 state ArcSight Message will contain StatementText.

    Today, I have asked for escalation to an appropriate level in hopes for a good resolve. #FingersCrossed

  • Note: In my experience with this connector; a situation exists in which the message field is not getting the desirable data.

    The event.massage field seems to contain a reference to the Audit LOBS binary file. Example: "auditlobs.699.144/"  The event.message field should contain something like "select * from table where blah blah" so that the analyst can see what is actually occurring. A reference location not desirable and once the connector processes files I typically delete.

    I have submitted and awaiting Feature Request: CON-11350

Related Discussions
Recommended