Do you wondering if users really need a analyst access?

Document created by jmayer Champion on Mar 22, 2016
Version 1Show Document
  • View in full screen mode

Hello all,

In our organization we don't like to provide access to users if they don't use...

You may say don't give it if they don't have justify it but you know how this goes... Always a way to justify it..

Yes I'm maybe too rigid but maybe someone have the same principle Therefore I sharing this simple SQL query below to retrieve the analysts that have not logged into SDM since more than 90 days:

 

SELECT ca_contact.contact_type AS contact_type, ca_contact.inactive AS inactive, ca_contact.userid, acctyp_v2.sym AS access_type, acctyp_v2.licensed

FROM  ca_contact LEFT OUTER JOIN usp_contact ON ca_contact.contact_uuid = usp_contact.contact_uuid LEFT OUTER JOIN acctyp_v2 ON usp_contact.c_acctyp_id = acctyp_v2.id

WHERE ca_contact.contact_uuid NOT IN (select contact from session_log where login_time > (DATEDIFF(s, '1970/01/01 00:00:00', GETDATE())-7776000))

AND ca_contact.inactive=0 and acctyp_v2.licensed=1

AND (ca_contact.contact_type > 2305 AND ca_contact.contact_type != 2308)

AND (acctyp_v2.id != 2402 AND acctyp_v2.id != 10002)

 

and of course next action is to....

.... putting them back to employee with the below query :

 

UPDATE usp_contact SET c_acctyp_id=2405 where contact_uuid IN

(SELECT  ca_contact.contact_uuid AS contact

FROM  ca_contact

  LEFT OUTER JOIN usp_contact ON ca_contact.contact_uuid = usp_contact.contact_uuid

  LEFT OUTER JOIN acctyp_v2 ON usp_contact.c_acctyp_id = acctyp_v2.id

  WHERE ca_contact.contact_uuid NOT IN

   (select contact from session_log where login_time > (DATEDIFF(s, '1970/01/01 00:00:00', GETDATE())-7776000))

   AND ca_contact.inactive=0

   AND acctyp_v2.licensed=1

   AND (ca_contact.contact_type > 2305 AND ca_contact.contact_type != 2308)

   AND (acctyp_v2.id != 2402 AND acctyp_v2.id != 10002)

)

 

not to forget the pdm_cache_refresh -t usp_contact to refresh the object with the last mdb data

 

You can change the number of day back by modify the duration in bold:

 

30 days = 2592000

90 days = 7776000

6 months(180 days) = 15552000

1 Year(365 days) = 31536000

 

Hope this help someone.

 

Don't hesitate to share your thoughts

 

/J

Attachments

    Outcomes