Pls can someone assist with a report that will show the number of users that have logged on to livelink in the past 190 days for each group. We are using content Server 9.7.1 running on Oracle database
Assuming that you are doing this purely in SQL, you will need to iterate through KUAF and KUAFCHILDREN to get all the users - as a group may contain other groups etc - and then join that to DAUDITNEW and look for the Login event, assuming that you have that event audited.
If you speak to your OT Account Manager, you should be able to get the schema and supporting documents, under an NDA which should allow a SQL / DB person to create the query for you.
Thanks. I came up with this sql, but i see no results. I cant fidn where the logic is wrong
SELECT kg.ID, kg.NAME, kg.TYPE, kg.deleted, decode(ku.type,0,'Users',1,'Sub-Group',ku.type) as "Member Type", DAuditNew.AuditDate, COUNT (*) FROM kuaf kg, kuaf ku, kuafchildren kc, DAuditNew WHERE kc.childid = ku.ID AND kc.ID = kg.ID AND kg.TYPE = 1 and DAuditNew.AuditStr='Login' and DAuditNew.auditdate > (current_date - 190) group by kg.id, kg.name, kg.type, kg.deleted, decode(ku.type,0,'Users',1,'Sub-Group',ku.type), DAuditNew.AuditDate order by id, name, type, deleted, "Member Type", AuditDate desc
