query to retrieve Audit-trail of usergroups

Sowmini_Jayapal

Please provide me query to view when a user is added/removed to/from group in livelink 7.0 version and MS Server database.Thanks in advance

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

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

Hi Sowmini,

 

Basic queries can be made through the browser interface via the Admin.Index pages.

  Admin Pages

     System Administration

        Administer Event Auditing

           Query Audit Log

 

This runs function ?func=admin.auditdsp

 

The page is mainly aimed at audit queries against DTree objects, but if you ignore the Target Items: block and select the Event Type

MembersChanged you will see how groups have items added or removed.

If you choose MembershipChanged then you see things from the group membership viewpoint, where a user or group is added to or removed from a group.

 

The disadvantage of this is that you cannot filter by group or member and the likelihood is that there are a large number of changes in a production system.

If you want to run queries directly against the DAuditNew table in the database to see the same sort of information then for SQL server the following samples would work.

 

AuditID = 33 means an audit of a Group having a member (a User or Group) added (Value2) or removed (Value1).

SELECT AuditStr, AuditDate, 'Group = ' + T1.Name, 'Delete Member = ' + T2.Name, 'Add Member = ' + T3.name
FROM DAuditNew DA left outer join KUAF T1 on T1.ID = DA.UserID
left outer join KUAF T2 on T2.ID = cast(cast(DA.Value1 as nvarchar(10)) as int)
left outer join KUAF T3 on T3.ID = cast(cast(DA.Value2 as nvarchar(10)) as int)
where AuditID = 33 order by 3,2,4,5

AuditID = 30 means an audit of a User (or Group)  being added a member of a Group (Value2) or removed from a Group (Value1).
SELECT AuditStr, AuditDate, 'Member = ' + T1.Name, 'Delete Member from Group = ' + T2.Name, 'Add Member to Group = ' + T3.name
FROM DAuditNew DA left outer join [ll971utf8].[ll971utf8].[KUAF] T1 on T1.ID = DA.UserID
left outer join KUAF T2 on T2.ID = cast(cast(DA.Value1 as nvarchar(10)) as int)
left outer join KUAF T3 on T3.ID = cast(cast(DA.Value2 as nvarchar(10)) as int)
where AuditID = 30 order by 3,2,4,5

For anyone watching this who wants something that works in Oracle...

 

select Auditstr, to_char(AuditDate,'YY-MON-DD HH24:MI:SS') AuditDateTime,
Userid, 'Group = ' || T1.Name as GroupName,
'Deleted Member = ' || T2.Name as Delmember, 'Added Member = ' || T3.Name as AddMember
from dauditnew DA, Kuaf T1, Kuaf T2, Kuaf T3
where DA.eventid>=33489 and auditid=33 and to_number(DA.Userid) = T1.ID(+)
 and to_number(DA.Value1)=T2.ID(+) and to_number(DA.Value2)=T3.ID(+)

 

 

select Auditstr, to_char(AuditDate,'YY-MON-DD HH24:MI:SS') AuditDateTime,
Userid, 'Member = ' || T1.Name as MemberName,
'Del from = ' || T2.Name as DelFromGroup, 'Add to = ' || T3.Name as AddToGroup
from dauditnew DA, Kuaf T1, Kuaf T2, Kuaf T3
where DA.eventid>=33489 and auditid=30 and to_number(DA.Userid) = T1.ID(+)
 and to_number(DA.Value1)=T2.ID(+) and to_number(DA.Value2)=T3.ID(+)

 

 

This should get you started on formatting a query to meet your own requirements.

Good luck!

 

Regards

Lindsay

 

Open Text
European Escalation Team

 

---

From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2010 August 10, Tue 18:43
To: eLink Recipient
Subject: query to retrieve Audit-trail of usergroups

 

query to retrieve Audit-trail of usergroups

Posted by sjayapal@csc.com (Jayapal, Sowmini) on 2010/08/10 13:42

Please provide me query to view when a user is added/removed to/from group in livelink 7.0 version and MS Server database. Thanks in advance