1. Please help support our sponsors by considering their products and services.
    Our sponsors enable us to maintain high-speed Internet connection and fast webservers.
    They support this free information and knowledge exchange forum service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions

[HOWTO] Nice TSM Admin Query

Discussion in 'Scripting' started by DanGiles, May 16, 2017.

  1. DanGiles

    DanGiles ADSM.ORG Senior Member

    Joined:
    Oct 25, 2002
    Messages:
    611
    Likes Received:
    15
    Occupation:
    Sr. Storage Admin
    Location:
    Toronto, Ont. Canada
    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:

    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
     
    cilesiz likes this.
  2.  

Share This Page