Groups and Users Audit infomation

I have a requirement where i have to get all the audit information for a given group (Group A),like 1) when a users are added, removed in the Group A, 2) when another group (Group added, removed in Group A. I am sucessful on first part, but i am finding difficult to get the second info, (i.e, GroupB audit info on Group A) if any one can help me on this would be grateful.

Find more posts tagged with

Comments

Lindsay_Davies

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi Umashanker,

There is only the information in DAuditNew.

AuditID = 33 or AuditStr=MembersChanged defines the audits for a group having members added or removed.

This auditable event needs to be enabled in the Admin.Index pages.

FYI - Seen from the Member perspective, the audit event is called MembershipChanged - AuditID=30.

The audit entry merely records the KUAF.ID of the User or Group being removed or added - in the column Value1 or Value 2 respectively.

So it requires a join to KUAF to expand that to the name of the item.

Here is a sample query that shows the addition and removal of a user and a group from one particular group.

This was written for Oracle.

If you use SQL server, then you won't need to_char, concatenation is not "||" but "+" and the easy outer join indicator will not work - "(+)".

In your case, rather than filtering on auditdate > sysdate -1 (the changes made in the last 24 hours) you will probably explicitly enter the group ID that you want to work with and output all the events.

i.e. userid=<integer from kuaf.ID for your group> - in my case userid=98563 would be required.

I have left in the columns UserID, Value1 and Value2 for clarity - they provide the foreign key to the derived string in the next column output.

I have also indicated whether the new/old member is a user or a group.

select to_char(auditdate, 'YY-Mon-DD hh24:mi:ss') "Date",

userid, k1.name "GroupName",
value1, decode(k2.type,0,'User=',1,'Group=') || k2.name "Remove",

         value2, decode(k3.type,0,'User=',1,'Group=') || k3.name "Add"
from dauditnew,kuaf k1, kuaf k2, kuaf k3
where auditdate > sysdate -1 and auditid=33
and userid=k1.id and value1=k2.id(+) and value2=k3.id(+)

Date                   USERID GroupName            VALUE1   Remove          VALUE2   Add
------------------ ---------- -------------------- -------- --------------- -------- ---------------
11-Aug-18 10:01:47      98563 Level 1 Continent                             136331   User=maers
11-Aug-18 10:02:14      98563 Level 1 Continent                             231433   Group=NewGroup1
11-Aug-18 10:06:05      98563 Level 1 Continent    136331   User=maers
11-Aug-18 10:06:11      98563 Level 1 Continent    231433   Group=NewGroup1

I hope this is the sort of thing you wanted or at least gives you an example of the techniques you can use.

Regards

Lindsay

Open Text UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 August 18, Thu 09:17
To: eLink Recipient
Subject: Groups and Users Audit infomation

Groups and Users Audit infomation

Posted by umashanker.krishnamoorthy@sce.com (Krishnamoorthy, Umashanker) On 2011/08/18 04:15

I have a requirement where i have to get all the audit information for a given group (Group A),like 1) when a users are added, removed in the Group A, 2) when another group (Group

added, removed in Group A. I am sucessful on first part, but i am finding difficult to get the second info, (i.e, GroupB audit info on Group A) if any one can help me on this would be grateful.

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Livelink Server:	knowledge-wlweb01