Date User Created

Can I find out what date a user record was created. I tried looking in the daudit table but I'm not sure what I am looking for. I created a report that tells me the last time a user logged in but also found that I had some users that have never logged in. I want to try and identify those user who were set up recently from those who have been set up for months(years) and have never logged in. If some knows what table to look for and how I might link it to Kuaf.id, I can write the sql myself, then I'll post the sql back to this discussion.

Find more posts tagged with

Comments

eLink User

Message from Alex Kowalenko via eLinkWhen userids are created, a personal workspace is normally also created. Therecord for this personal workspace is stored in DTree with a SubType of 142.You can relate this DTree record to KUAF.ID via DTree.UserID. Then,DTree.CreateDate will give you the user's creation date.The following SQL example lists all user names with their creation date.Select DTree.CreateDate, KUAF.Name From DTree, KUAF Where DTree.UserID = KUAF.ID and DTree.SubType = 142 Order By KUAF.Name-alex------Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Tuesday, July 02, 2002 13:10To: eLink RecipientSubject: Date User CreatedDate User CreatedPosted by UCLLUser1 on 07/02/2002 01:05 PMCan I find out what date a user record was created. I tried looking in thedaudit table but I'm not sure what I am looking for. I created a reportthat tells me the last time a user logged in but also found that I had someusers that have never logged in. I want to try and identify those user whowere set up recently from those who have been set up for months(years) andhave never logged in. If some knows what table to look for and how I mightlink it to Kuaf.id, I can write the sql myself, then I'll post the sql backto this discussion.[To reply to this thread, use your normal e-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

eLink User

Message from David Slimmon via eLinkHi there,I've never really played with this, but if you're looking for when a userwas created, you could always map it over to the CreateDate in DTree forthat user's Personal Workspace. Everything with a SubType of 142 should bea personal workspace with a listing for the date that it was created in theCreateDate column. You'd just need to find the correct entry for the useryou're looking for I guess.Have others done something similar or maybe using a different approach?If you did something like:SELECT Name, CreateDateFROM DTreeWHERE SubType=142ORDER BY CreateDateYou should find everyone (probably including deleted users) and when theircreation date. If you added in OwnerId I think you'd even get the Id's ofthe users who'd created them.Regards,Dave-----Original Message-----Date User CreatedPosted by UCLLUser1 on 07/02/2002 01:05 PMCan I find out what date a user record was created. I tried looking in thedaudit table but I'm not sure what I am looking for. I created a reportthat tells me the last time a user logged in but also found that I had someusers that have never logged in. I want to try and identify those user whowere set up recently from those who have been set up for months(years) andhave never logged in. If some knows what table to look for and how I mightlink it to Kuaf.id, I can write the sql myself, then I'll post the sql backto this discussion.

Cynthia_Philpott

Thanks this worked great, I incorporated it in my Audit report. Now I can tell if a user who has not logged in is a recent set up or just plain uninterested. I am running the report for a specific group made up of external collaborators.select a.id "User ID", a.LastName || ', ' || a.FirstName "Name", a.Name "ID", mailaddress "Email Address", DECODE (a.userprivileges,14, 'Inactive',15, 'Active', 'Other') "Priv", MAX(AuditDate) "Last Access", a.Title "Title",d.CreateDate "Setup Date" From KUAF a, DAudit, DTree d WHERE a.Type = '0' and a.Deleted = '0' and d.UserID = a.ID and d.SubType = 142 and a.id in (select id from kuaf b where b.groupid =2074 and b.deleted = 0) and a.ID = DAudit.UserID (+) Group by a.id, a.LastName, a.FirstName, a.Name, a.mailaddress, userprivileges, a.Title, d.CreateDate order by max(AuditDate)

Mary_Slane_Corona

I am working on some LiveReports to determine users that have never logged in and the last time a user logged in and I was reviewing this discussion. Question (and again, I am very much a novice struggling with some of this stuff): What does (+) mean? Thank you.

David_Spirhanzl

Message from David Spirhanzl <David.Spirhanzl@syconix.cz> via eLink

eLink

Hi Mary,

the (+) sign in Oracle SQL query is for OUTER JOIN. See the linkbelow.

http://www.techonthenet.com/sql/joins.php

David Spirhanzl

SYCONIX, a.s.

Czech Republic

From: eLink Discussion: Livelink LiveReportsDiscussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, April 17, 2009 2:36 AM
To: eLink Recipient
Subject: Question from a novice

Question from a novice		Posted by mary.corona@ejgallo.com (Corona, Mary) on 2009/04/16 20:32
In reply to: re user create date from personal workspace create		Posted by uclluser1 (Walkush (Disabled), Melissa) on 2002/07/02 14:02

[To reply to this thread, use your normal E-mail reply function.]
Topic:	Date User Created
Discussion:	Livelink LiveReports Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Lindsay_Davies

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

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

Hi Mary,

Assuming this is part of a SQL statement you found, it denotes which side of a join condition should be the outer join.

As a novice, you might like to bookmark the following site ( www.techonthenet.com) which I have personally found very useful when improving my SQL skills.

It has info on both MS Sql and Oracle, amongst other products.

Here is a quote from that site about outer joins from this page ( http://www.techonthenet.com/sql/joins.php ) ...

Outer JoinAnother type of join is called an outer join. This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).For example, select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);This SQL statement would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.The (+) after the orders.supplier_id field indicates that, if a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null> in the result set.The above SQL statement could also be written as follows: select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where orders.supplier_id(+) = suppliers.supplier_id

Good luck!

Lindsay

Open Text
European Escalations Team

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 17 April 2009 01:36
To: eLink Recipient
Subject: Question from a novice

Question from a novice		Posted by mary.corona@ejgallo.com (Corona, Mary) on 2009/04/16 20:32
In reply to: re user create date from personal workspace create		Posted by uclluser1 (Walkush (Disabled), Melissa) on 2002/07/02 14:02

Mary_Slane_Corona

David and LyndsayThank you both for your responses and providing links to helpful information. It is appreciated. Have a great weekend.(Note: This Thank You response was probably posted twice as a new Topic (Oops :-). Kept pressing the wrong option instead of responding to a post.Mary