Tracking end-user activity

[Anon Anon]

I have been look into how to track user activity within our database.  We have two compliance needs one is to track all inquiry activity and secondarily we need to track the add, modify, delete activity.  

It appears from my research so far that there may be journal reporting software solution for the add, modify, delete portion of the requirements.  Though one product as-tracer appears to be no longer available and the other I have found mentioned ISP journal reporting has very sketchy descriptions of what it does and what the output would look like/if user ids are captured.  Does anyone have any information on these products or other solutions to this compliance issue.  We are processing between 400,000 and 600,000 transactions daily.  

 

[Laura Rochon]

Hi,

I've used Journal Reporter from ISP for years, and I LOVE this product.
Very easy to use, very efficient, and the reports are great.
Furthermore, the support is exceptional. and no, I'm not getting paid
to say this.

For your other compliance of tracking inquiry activity, it depends what
level of tracking you need. If you need to keep track at the record
level, you might have to write a database procedure to do so. This
will be a lot of overhead however. I would presume that you wouldn't
need this for all records, but just some critical ones.

Good luck,
Laura Rochon
Hera Evolution inc


On 30/04/2014 8:58 AM, CA IDMS IUA EIUA Global User CommunityMessage
Boards wrote:
>

I have been look into how to track user activity within our database.
We have two compliance needs one is to track all inquiry activity and
secondarily we need to track the add, modify, delete activity.

It appears from my research so far that there may be journal reporting
software solution for the add, modify, delete portion of the
requirements. Though one product as-tracer appears to be no longer
available and the other I have found mentioned ISP journal reporting
has very sketchy descriptions of what it does and what the output
would look like/if user ids are captured. Does anyone have any
information on these products or other solutions to this compliance
issue. We are processing between 400,000 and 600,000 transactions daily.

Posted by:svander
--
CA Communities Message Boards
114806348
[e-mail]
https://communities.ca.com

[Tommy Petersen]

The problem you will have is that IDMS does not track the records that were retrieved.  You can create journal entry points for retrieval run units, but they would not have anything specific about the retrieval, only a summary.  You would have a problem with local running batch jobs, unless you put security in place to control who can access the database files.

A database procedure may be a solution, but even if you monitor only certain records it could affect performance, and you would need to make sure that nobody can introduce a schema/subschema that does not include the database procedure.  I am not sure how you could collect the information from the database procedure.  This is just an idea, I have never tried so it may not be possible, but maybe you could write an SMF record from the database procedure.

[Legacy User]

A database procedure would be an option but you have to remember, database procedures, regardless of what language they are written in, cannot issue DML verbs to the same rununit they have been called for.

The way I did this in another life was to issue PUT QUEUE’s to record the record’s being retrieved, stored, modified, etc, and then had a queue triggered task that read the queue and wrote the audit records to the audit area. By using queue, if the CV crashes before the records are written to the audit area, they’ll be there when the CV comes back up and the trigger task has catch up work to do, but eventually, it gets caught up and things begin working “normally”.

The biggest problem you’ll have is if you need these audit record from batch programs as well. With TCP now supported all over the place, maybe you could engineer a solution for the batch process to connect to a CV based process via TCP and then the batch program would communicate to CV that way. Problem there is that the CV could be down when the batch program runs so you’re back to the state you’re in now, no auditing.

An alternative to all of this may be to have a separate, small, CV where all the audit data goes. It would stay up 24/7/365 and receive all audit data, maybe via TCP.

Good Luck, and keep us posted what you end up doing.

Charles (Chuck) Hardee
Senior Systems Engineer/Database Administration
CCG Information Technology
Thermo Fisher Scientific
300 Industry Drive
Pittsburgh, PA 15275
Direct: 724-517-2633
FAX: [phone]
[e-mail]

From: CA IDMS IUA EIUA Global User CommunityMessage Boards [mailto:[e-mail]]
Sent: Wednesday, May 07, 2014 12:50 PM
To: [e-mail]
Subject: [Application Developer Issues] RE: Tracking end-user activity


The problem you will have is that IDMS does not track the records that were retrieved. You can create journal entry points for retrieval run units, but they would not have anything specific about the retrieval, only a summary. You would have a problem with local running batch jobs, unless you put security in place to control who can access the database files.

A database procedure may be a solution, but even if you monitor only certain records it could affect performance, and you would need to make sure that nobody can introduce a schema/subschema that does not include the database procedure. I am not sure how you could collect the information from the database procedure. This is just an idea, I have never tried so it may not be possible, but maybe you could write an SMF record from the database procedure.
Posted by:tommy.petersen
--
CA Communities Message Boards
115666639
[e-mail]<mailto:[e-mail]>
https://communities.ca.com

[Gary Cherlet]

We used a database procedure  which wrote to SMF - so efficient that when benchmarked with and without the database procedure there was no measurable impact.

HTH - cheers - Gary

[Legacy User]

How did you extract your information from SMF?


Charles (Chuck) Hardee
Senior Systems Engineer/Database Administration
CCG Information Technology
Thermo Fisher Scientific
300 Industry Drive
Pittsburgh, PA 15275
Direct: 724-517-2633
FAX: [phone]
[e-mail]

From: CA IDMS IUA EIUA Global User CommunityMessage Boards [mailto:[e-mail]]
Sent: Wednesday, May 07, 2014 7:01 PM
To: [e-mail]
Subject: [Application Developer Issues] RE: Tracking end-user activity


We used a database procedure which wrote to SMF - so efficient that when benchmarked with and without the database procedure there was no measurable impact.

HTH - cheers - Gary
Posted by:Gary_Cherlet
--
CA Communities Message Boards
115736749
[e-mail]<mailto:[e-mail]>
https://communities.ca.com

[Gary Cherlet]

For ongoing "production" reports there was a suite of SAS reports built on top of Merrill (?) - we also used Culprit for ad-hoc as we had more Culprit skills in-house than SAS skills after our system programmers were outsourced! Cheers - Gary

[rob.klan.1]

I have been involved in like processes and for audit reasons.

The data was written to GTF as “USR” entries instead of using SMF.

This was for ATM/POS transactions.

The cost of producing this GTF record was 000.002% of the transactional cost.

I only post in support of Gary’s statement, “was no measurable impact”.


From: CA IDMS IUA EIUA Global User CommunityMessage Boards [mailto:[e-mail]]
Sent: Wednesday, May 07, 2014 7:01 PM
To: [e-mail]
Subject: [Application Developer Issues] RE: Tracking end-user activity


We used a database procedure which wrote to SMF - so efficient that when benchmarked with and without the database procedure there was no measurable impact.

HTH - cheers - Gary
Posted by:Gary_Cherlet
--
CA Communities Message Boards
115736749
[e-mail]<mailto:[e-mail]>
https://communities.ca.com