[HOWTO] Nice TSM Admin Query

[DanGiles]

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:

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.

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