Skip to Content

Detailed Auditing Take Two

A client has a requirement for detailed user level auditing in the form of who, what, when and how accesses table and column data in their warehouse. The outcome is to clean / prune those tables / columns not used in an environment originally built on IQ in the 1999 / 2000 timeframe. I have been spinning on this for a few weeks now, using tools like iq workmon and related procs, as well as request level logging. Neither of these options provides the level of detail the client requires. For example, request level logging will show DML statements but linking that back to a user is virtually impossible. workmon is nice in that it's more of a real time tool, but it does not collect the detail required, i.e. user and tool.

I would greatly appreciate any thoughts...

Regards

Raymond

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Nov 12, 2016 at 12:30 AM

    Hi Raymond,

    extending RLL to be able to connect the entries to a user shouldn't be such a big deal. In satmp_request_time, there's a column conn_id, and with a handler for the CONNECT event, you can log which ConnectionID was used by which user at a given time.

    HTH

    Volker
    DB-TecKnowledgy

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks Volker, I have created a connect event and have had some success with at least connecting a user to a request. Also playing with sa_conn_info and properties and trying to link that to context etc. in a more real time fashion.

  • Nov 18, 2016 at 01:21 AM

    So created an event using handler Connect and event parameters which capture and write out connid, user, appinfo and time.

    Also created a scheduled event using connection_property parameters to capture detailed info including last statement, and a host of other info of interest to the client. Now everyone is happy!

    Add comment
    10|10000 characters needed characters exceeded