• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • Last Modified:

Best way to run a query/report to check for accounts expiring within 21 days.

I have the syntax I want to run.  Just can't figure out what would be the best way to run this against all of our production databases at once.  Tried going through Enterprise manager but none of the reporting tools or jobs seemed to do what I wanted them to do.  Just want to know if anyone has done anything like this.  Your words and wisdom would be much appreciated.

here is the report I want to run:

SELECT A.NAME, B.USERNAME, B.ACCOUNT_STATUS, B.EXPIRY_DATE,

    TRUNC(B.EXPIRY_DATE)-TRUNC(SYSDATE) as num_days

FROM v$database A, DBA_USERS B

WHERE TRUNC(B.EXPIRY_DATE) - TRUNC(SYSDATE) < 30

ORDER BY num_days desc;



Thanks,

-Kyle
0
WCH_Applications
Asked:
WCH_Applications
1 Solution
 
OP_ZaharinCommented:
- you can create a pl/sql procedure and schedule it sa a job to run daily. output it to a table or a file as report and email the report to your email daily.
0
 
mrjoltcolaCommented:
You can setup remote database links and run it all in one single script.

select a.*, 'ORA1' from dba_users@ora1 a
union
select b.*, 'ORA2'  from dba_users@ora2 b
union
...

Otherwise, you'll have to write a script (such as Perl) that connects to each database in a loop.
0
 
schwertnerCommented:
set pagesize 500
set linesize 200
set trimspool on
column “EXPIRE DATE” format a20
select username as “USER NAME”, expiry_date as “EXPIRE DATE”, account_status
from dba_users
where expiry_date < sysdate+21and account_status IN ( ‘OPEN’, ‘EXPIRED(GRACE)’ )
order by account_status, expiry_date, username
/
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
WCH_ApplicationsAuthor Commented:
I've been takling to some of our guys, we want one report that shows all expiring (or expired) user accounts across all (production) databases.  If we can link all of the account names with their e-mail addresses, we might be able to ALSO solve an issue for the user-password change program.  This way we could identify users with expiring passwords AND notify them of a method were they can change their own passwords with minimal work on our part.
0
 
slightwv (䄆 Netminder) Commented:
To do it from a single source, you need something that can see everything.  This will likely, as others have suggested, need some script/program.

Have you considered changing the databases to some central authentication method?
0
 
htonkovCommented:
On each database create a view REPORTVIEW with that query in some schema which has enough privileges.

You might want to use some batch file like:

sqlplus user1/pass1@db1 @%1
sqlplus user2/pass2@db2 @%1
sqlplus user3/pass3@db3 @%1
sqlplus user4/pass4@db4 @%1
....

Open in new window


and drag'n'drop sql script with view DDL.

Now, create DBlinks to each schema
In some empty schema:

create table MYDBLINKS(
DBLINKNAME varchar2(30)
);

Open in new window


create also a table, call it REPORT, widh all colums from a view you will query, plus column DBLINKNAME varchar2(30)

now:

BEGIN
FOR i in (select * from MYDBLINKS) LOOP
execute immediate 'insert into report(dblinkname,  NAME, USERNAME, ACCOUNT_STATUS, EXPIRY_DATE, num_days) from select ''' || i.dblinkname || ''' , NAME, USERNAME, ACCOUNT_STATUS, EXPIRY_DATE, num_days from REPORTVIEW@'|| i.dblinkname;
commit; -- commit immediately to avoid having too much dblinks open!
END LOOP;
EXCEPTION
 do sometning here
END;

Sorry for possible typos :)

Regards,
Hrvoje
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now