DanGiles
ADSM.ORG Senior Member
It took me hours to come up with this, so I wanted to post and share. I had to come up with this for audit. The output comes out like this:
Here's the select statement. If you put it in a script, it runs fine (but don't forget to double the quotes!). If you try to run as-is, it's too long and you'll need to break it up with continuation marks. Let me know what you think.
Code:
Name Last Access P/W Reset P/W Expire Locked SYSTEM POLICY OPERATOR
------------------- ------------ ----------- ------------ ------- ------- ------- ---------
XXXXX 2017-04-27 2017-04-03 62 NO Yes Yes Yes
XXXXXX 2017-05-16 2017-04-19 62 NO Yes Yes Yes
IBM-OC-TSMTRV1 2017-05-16 2014-12-22 0 NO Yes Yes Yes
XXXXXXX 2017-05-15 2017-04-04 62 NO Yes Yes Yes
SERVER_CONSOLE NO Yes Yes Yes
VMPROXYADMIN 2017-05-02 2016-08-03 0 NO Yes Yes Yes
XXXXXXX 2017-05-02 2017-03-29 62 NO Yes
XXXXXXXX 2016-10-31 2016-10-31 62 NO Yes
XXXXXXX 2017-05-16 2017-04-18 62 NO Yes
REPORTER 2017-05-16 2015-06-04 0 NO Yes
Here's the select statement. If you put it in a script, it runs fine (but don't forget to double the quotes!). If you try to run as-is, it's too long and you'll need to break it up with continuation marks. Let me know what you think.
Code:
select cast(coalesce(c.admin,d.admin_name) as char(18)) as "Name",coalesce(c.acc,d.lastacc) as "Last Access", coalesce(c.pw,d.pwset) as "P/W Reset",coalesce(c.exp,d.passexp) as "P/W Expire",coalesce(c.lock,d.locked) as "Locked",system,policy,operator from (select coalesce(a.admin_name,b.admin_name) as admin,coalesce(a.lastacc,b.lastacc) as acc, coalesce(a.pwset,b.pwset) as pw,coalesce(a.passexp,b.passexp) as exp,coalesce(a.locked,b.locked) as lock,policy,operator from (select admin_name,date(lastacc_time) as lastacc,date(pwset_time) as pwset,passexp,locked,'Yes' as Policy from admins where policy_priv<>'No') as a full outer join (select admin_name,date(lastacc_time) as lastacc,date(pwset_time) as pwset,passexp,locked,'Yes' as operator from admins where operator_priv<>'No') as b on a.admin_name=b.admin_name ) as c full outer join (select admin_name,date(lastacc_time) as lastacc,date(pwset_time) as pwset,passexp,locked,'Yes' as system from admins where system_priv<>'No') as d on c.admin=d.admin_name