nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Report for Inactive Users

Karen_Morley

Looking for a report to use for Content Server 10.0 (Oracle Database) that can be modified to show all users who have not logged in for a period of 30 days, 60 days, 180 days, etc. Have tried various versions of ones posted on this site but with no success. Does anyone have a report that will provide this information? Thank you.

Find more posts tagged with

Comments

Colin_Schmidt

That's not too difficult. There's an Audit ID for logging in. It would be a combination of a Date and the Audit ID not showing. Is this Oracle or SQL Server or ?

Colin J. Schmidt

Opentext Content Server Support Admin

colin.schmidt@mwv.com
+1 804.444 5012 (Direct)

MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Wed, Jan 21, 2015 at 11:14 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Report for Inactive Users Posted bykaren.morley@goodrich.com (Morley, Karen)On 01/21/2015 11:08 AM

Looking for a report to use for Content Server 10.0 (Oracle Database) that can be modified to show all users who have not logged in for a period of 30 days, 60 days, 180 days, etc. Have tried various versions of ones posted on this site but with no success. Does anyone have a report that will provide this information? Thank you.
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation (mwv.com) or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Colin_Schmidt

select k.id, k.name "ID", k.firstname||' '||k.lastname "FullName"

from kuaf k

where k.type = 0 and k.deleted = 0

and (select max(da.auditdate) from dauditnew da where da.auditid = 23 and da.performerid = k.id) < (CURRENT_DATE - 30)

I can't vouch for the above being the best possible code, but you get the point.

Colin J.

On Wed, Jan 21, 2015 at 11:21 AM, Colin Schmidt <colin.schmidt@mwv.com> wrote:

That's not too difficult. There's an Audit ID for logging in. It would be a combination of a Date and the Audit ID not showing. Is this Oracle or SQL Server or ?

Colin J.

On Wed, Jan 21, 2015 at 11:14 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:
Report for Inactive Users Posted bykaren.morley@goodrich.com (Morley, Karen)On 01/21/2015 11:08 AM

Looking for a report to use for Content Server 10.0 (Oracle Database) that can be modified to show all users who have not logged in for a period of 30 days, 60 days, 180 days, etc. Have tried various versions of ones posted on this site but with no success. Does anyone have a report that will provide this information? Thank you.
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Karen_Morley

Thank you. I ran this and it appears to be working. I more the business owner, application manager and not IT support so I am not familiar with how to write the reports. The only question have is what is the significance of the number 23 in the statement? Also, we are on Unix and database is Oracle.

select k.id, k.name "ID", k.firstname||' '||k.lastname "FullName" from kuaf k where k.type = 0 and k.deleted = 0 and (select max(da.auditdate) from dauditnew da where da.auditid = 23 and da.performerid = k.id) < (CURRENT_DATE - 30)

Colin_Schmidt

Unix will make no difference. The DB will.

The "23" means a Login. The code is searching for the last Login (MAX(da.auditdate)) and compares it to the Current date - 30 days. If it's older than that, the User hasn't logged in in the past 30 days. You can find codes in a Champion Toolkit manual in the KC (Schema Companion Reference).

Colin J.

On Wed, Jan 21, 2015 at 1:59 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Report for Inactive Users 2 Posted bykaren.morley@goodrich.com (Morley, Karen)On 01/21/2015 01:54 PM

Thank you. I ran this and it appears to be working. I more the business owner, application manager and not IT support so I am not familiar with how to write the reports. The only question have is what is the significance of the number 23 in the statement? Also, we are on Unix and database is Oracle.select k.id, k.name "ID", k.firstname||' '||k.lastname "FullName" from kuaf k where k.type = 0 and k.deleted = 0 and (select max(da.auditdate) from dauditnew da where da.auditid = 23 and da.performerid = k.id) < (CURRENT_DATE - 30)
[To post a comment, use the normal reply function]Topic:Report for Inactive UsersForum:Content Server LiveReports ForumContent Server:Knowledge Center

Matthew_Turner

Hi,

Would you have the equivalant available to this post from Karen but for Microsoft SQL ? , Likewise I have used other reports and tried to get it working but im not getting what I wanted. If you could post it that would be great.

Kind Regards

Matt

Colin_Schmidt

Yes, I'll see what it'll take later today.

Colin J.

On Mon, Feb 9, 2015 at 9:39 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Re Report for Inactive Users 2 Posted bymatthew.turner@derbyshire.gov.uk (Turner, Matthew)On 02/09/2015 09:34 AM

Hi,Would you have the equivalant available to this post from Karen but for Microsoft SQL ? , Likewise I have used other reports and tried to get it working but im not getting what I wanted. If you could post it that would be great.Kind RegardsMatt
[To post a comment, use the normal reply function]
Topic: Report for Inactive Users
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Colin_Schmidt

For SQL Server 2012.... There were two key differences (both minor). The concatentation was changed from two "||" to "+". and "CURRENT_DATE" was changed to "GETDATE()".

select k.id, k.name "ID", k.firstname+' '+k.lastname "FullName"
from kuaf k
where k.type = 0 and k.deleted = 0 and (select max(da.auditdate) from dauditnew da where da.auditid = 23 and da.performerid = k.id) < (GETDATE() - 30)

Matthew_Turner

That's great, thank you Colin. Just run it and it's looking good.

Mark Runyan

Careful, I don't think that SQL will report users who have never logged in. I run a separate report for that.

select
-- u.id,
-- u.name,
u.firstname||' '||u.lastname as fullname,
u.title,
u.mailaddress,
decode(bitand(u.userprivileges, 15),15,'Enabled','Disabled') Status,
g.name as "Security Group",
d.createdate
from
livelink.kuaf u,
livelink.kuaf g,
livelink.dtree d
where
u.groupid = g.id
and d.createdby = u.id
and d.subtype = 142 -- Personal Workspace
and u.DELETED = 0
and u.id not in (select distinct performerid from livelink.dauditnew)
order by
d.createdate

I join with dtree so I can get the date the personal workspace was created which provides the date the account was created.

- Mark

Colin_Schmidt

You do have to check for a DAUDITNEW entry for : auditid = 23

not EXISTS (select da.eventid from dauditnew da where da.auditid = 23 and da.performerid = k.id)

or some other SELECT, but it has to be a negative as this is. A '23' can not exist for a User to have never have logged in.

Colin J.

On Tue, Feb 10, 2015 at 4:29 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Re Report for Inactive Users 2 Posted bymrunyan@ctiseattle.com (Runyan, Mark)On 02/10/2015 04:23 PM

Careful, I don't think that SQL will report users who have never logged in. I run a separate report for that.select
-- u.id,
-- u.name,
u.firstname||' '||u.lastname as fullname,
u.title,
u.mailaddress,
decode(bitand(u.userprivileges, 15),15,'Enabled','Disabled') Status,
g.name as "Security Group",
d.createdate
from
livelink.kuaf u,
livelink.kuaf g,
livelink.dtree d
where
u.groupid = g.id
and d.createdby = u.id
and d.subtype = 142 -- Personal Workspace
and u.DELETED = 0
and u.id not in (select distinct performerid from livelink.dauditnew)
order by
d.createdateI join with dtree so I can get the date the personal workspace was created which provides the date the account was created.- Mark
[To post a comment, use the normal reply function]
Topic: Report for Inactive Users
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

K_Karia

Hello

I am planning to use this query for inactive users for past 180 days. What does type=0 mean in the query?

I see a user whose password has expired as of Jan, 2014, but his login is showing "enabled" & he is not showing up in the resultset of your query (inactive users for past 180 days). What is the reason?

Appreciate your help.

Colin_Schmidt

KUAF.TYPE = 0 ...is a User. 1 is Group and there are others that are spelled out in a Champion Toolkit in the KC.

If their password has expired and they didn't contact support to have it reset, they don't appear unconcerned. Maybe using it as an excuse: "I can't get in some how!". Being enabled is a configuration item that you'd have to check on all instances. Some are system-wide and some aren't.

Attached are my collection of various Code lists in CS.

Colin J.

On Thu, Apr 2, 2015 at 8:10 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Re Report for Inactive Users 2 2 Posted bykena.x.karia@kp.org (Karia, Kena)On 04/02/2015 08:06 PM

HelloI am planning to use this query for inactive users for past 180 days. What does type=0 mean in the query?I see a user whose password has expired as of Jan, 2014, but his login is showing "enabled" & he is not showing up in the resultset of your query (inactive users for past 180 days). What is the reason?Appreciate your help.
[To post a comment, use the normal reply function]
Topic: Report for Inactive Users
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

LL-Table codes.xlsx

K_Karia

Sorry, got pulled into some other project for a while. Coming back to this one:

I am fairly new to Content Server....We already have a weekly job in place (the person before I took over perhaps wrote it) with a query that deletes users who have been inactive for past 180 days. But we are finding that there are some users whose logins are "enabled" & their passwords are expired in the past. That means that our query is obviously somehow not pulling in those users. It is not mandatory for users to login to this application if they have a login. Perhaps these users have never logged-in?

Not sure, but I was looking for a SQL Server query that works better. Right now, I have no idea why those logins still exist (& enabled), even though their password is expired.

Appreciate your help on this.

nikhil.sanghvi

Great, Thanks Colin.

That sheet really helped a lot