Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only

  • 1.  Query Inactive Users In a Specific Security Group

    Posted 06/08/22 12:58 PM
    Hi,

    In attempt to manage inactive users who are part of a specific Security Group for Mobile access, I'm attempting to extend the query below:

    select * from maxuser
    where status='ACTIVE'
    and sysuser=0
    and not exists (select userid from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and attemptdate>getdate()-180)
    order by userid

    While trying to join the additional table to get the user's full name (person) and security group info (groupuser) , and include logintracking.clienthost field (ie. and clienthost like 'EMM_%'), I'm getting inaccurate results from what I'm hoping to achieve.

    Has anyone written a query for this scenario? I've managed to find examples for one or the other, but not both (ie. Security Group access + Login Inactivity). Likely that I'm overcomplicating it. Any guidance is appreciated.

    Cheers,
    Matt


    ------------------------------
    Matt F
    ------------------------------

    #Maximo
    #AssetandFacilitiesManagement


  • 2.  RE: Query Inactive Users In a Specific Security Group

    Posted 06/08/22 01:05 PM
    Matt,

    Does this get you what you want? Obviously you'll need to replace the Group Name in the second Exists statement.

    select
    maxuser.userid, maxuser.status, maxuser.pwexpiration
    , person.personid, person.displayname
from maxuser
join person on maxuser.personid=person.personid
where
    maxuser.status='ACTIVE'
    and maxuser.sysuser=0
    and not exists (select 1 from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and clienthost like 'EMM_%' and attemptdate>getdate()-180)
    and exists (select 1 from groupuser where groupuser.userid=maxuser.userid and groupuser.groupname='MAXADMIN')
order by maxuser.userid
;​


    ------------------------------
    Tim Ferrill
    Solutions Consultant
    Intelligent Technology Solutions
    tferrill@webuildits.com
    www.webuildits.com
    @tferrill/@webuildits
    ------------------------------



  • 3.  RE: Query Inactive Users In a Specific Security Group

    Posted 06/08/22 01:30 PM
    Hey Tim,

    Thanks for the prompt response! This is looking better already. So far so good on my initial checks. I appreciate it!

    Is there a way for me to include logintracking.attemptdate to confirm the last login date as well within this same query? Or will I have to compare this with another individual user query check on the logintracking table?

    Attempting to join logintracking to this query did not work out so nicely in the results.

    ------------------------------
    Matt F
    ------------------------------

    Original Message


  • 4.  RE: Query Inactive Users In a Specific Security Group

    Posted 06/08/22 01:43 PM
    Matt, Sure thing! Give this a try:

    select
    maxuser.userid, maxuser.status, maxuser.pwexpiration
    , person.personid, person.displayname
    , (select max(attemptdate) from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and clienthost like 'EMM_%') as last_attempt
from maxuser
join person on maxuser.personid=person.personid
where
    maxuser.status='ACTIVE'
    and maxuser.sysuser=0
    and not exists (select 1 from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and clienthost like 'EMM_%' and attemptdate>getdate()-180)
    and exists (select 1 from groupuser where groupuser.userid=maxuser.userid and groupuser.groupname='MAXADMIN')
order by maxuser.userid
;​


    ------------------------------
    Tim Ferrill
    Solutions Consultant
    Intelligent Technology Solutions
    tferrill@webuildits.com
    www.webuildits.com
    @tferrill/@webuildits
    ------------------------------

    Original Message


  • 5.  RE: Query Inactive Users In a Specific Security Group

    Posted 06/08/22 01:52 PM
    Tim, 

    You're a gem. This is great. I sometimes forget the use of including multiple selects within the initial select and not needing to always rely on a join. Thank you for the SQL refresher today!

    ------------------------------
    Matt F
    ------------------------------

    Original Message


Related Content