List active users?

[cdnadmin]

This document was generated from CDN thread

Created by: Neil Bowman on 19-07-2012 02:54:36 PM
All,
  Does anyone know a way to list only the user accounts that have signed in within say the last 20 days? I know you can filter on "active" but that only applies if you deactivate the accounts.
 
Thanks!

Subject: RE: List active users?
Replied by: Ratnadeep Ray on 30-07-2012 12:52:24 AM

All,
  Does anyone know a way to list only the user accounts that have signed in within say the last 20 days? I know you can filter on "active" but that only applies if you deactivate the accounts.
 
Thanks!

 
Hi Neil, 
 
Please use the following sql query to fetch the records of those users who signed in within last 20 days:- 
 
select * from user_ where lastlogindate>='<date and time after which you want the data';
 
Hope this answers your question. 
 
Regards, 
Ratnadeep. 

Subject: RE: List active users?
Replied by: Ben Sunderland on 30-07-2012 01:18:08 AM
We use this , which displays in sydney time zone (to adjust, just change X/24 where X is your offset to GMT).
For last 20 days , change the where clause to '<= 20'.


Note, pretty sure 'lastlogindate' is the second last login time.

SELECT
CONCAT(CONCAT(firstname, ' '),lastname) as user_name,
  to_char(logindate+10/24, 'Dy DD-Mon-YYYY HH24:MI:SS') as SydneyTime
FROM USER_
where (SYSDATE  ) - to_date(to_char(logindate, 'DD-Mon-YYYY')) <= 10
order by logindate desc;

Subject: RE: List active users?
Replied by: Neil Bowman on 30-07-2012 07:10:34 AM
Thank you both, that helps a lot - one final question though (as I'm not really a linux/unix/db admin)... I'm assuming these commands should be run from the SQL utility on the RDB node, correct? Since the update to 3.0 I've been unable to log in to the SQL utility using the standard sqlplus / as sysdba command (or even sudo version there of) is there some trick to it in 3.0?

Thanks again,
Neil

Subject: RE: List active users?
Replied by: Ben Sunderland on 31-07-2012 02:39:05 AM
on RDBMS node - drop to shell then....

[admin@awq2rdb01 ~]$ sudo su - oracle
-bash-4.1$ sqlplus / as sysdba

Subject: RE: List active users?
Replied by: Neil Bowman on 02-08-2012 08:10:40 AM
Ben, thanks again, I'm able to get in to the sqlplus utility now... however I'm not able to run the command you gave me. Belowe is the modified string I'm using:

SELECT CONCAT(CONCAT(firstname, ' '),lastname) as user_name,to_char(logindate-5/24, 'Dy DD-Mon-YYYY HH24:MI:SS') as SydneyTime FROM user_ where (SYSDATE ) - to_date(to_char(logindate, 'DD-Mon-YYYY')) <= 20 order by logindate desc;

Changed the +10/24 to -5/24 for EST, and the <= 10 to <= 20 for the wider time frame. I'm getting an error that the table name maybe is incorrect?

ERROR at line 1:
ORA-00942: table or view does not exist

Did I put something in wrong, or is there another command I need first?

Subject: RE: List active users?
Replied by: Neil Bowman on 02-08-2012 09:20:44 AM
Ok, fixed part of my own problem and found a few other things helpful, but maybe you can help me finish this off.

1) I had to use "alter session set current_schema = QUADDB" and changed the table name to all caps (USER_). One or both of those fixed the initial error I was getting, so now I can pull the data.
2) I found some data on doing a CSV export (which may be helpful if I get a large amount returned!) using the following commands:

set colsep ,;
set pagesize 1000;
set trimspool on;
set headsep on;
spool /tmp/myfile.csv

That worked well enough, except that it does not appear to split the data into 2 columns like I'd wanted. I am able to copy the data and move it around in excel after copying it to a machine but I'd really like to simplify the process if possible.