Skip to Content

Detailed Auditing Take Two

Nov 08, 2016 at 10:55 AM


avatar image

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...



10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Volker Stöffler 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.



Show 1 Share
10 |10000 characters needed characters left 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.

Raymond Lackey 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!

10 |10000 characters needed characters left characters exceeded