Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/04/2024 03:52 AM
Hello,
We have requirement as below.
Generate dormancy report for AD based on below conditions.
User | AD lastlogin | Endpoint1 lastlogin | Endpoint2 login date stored on user profile (CPx) | ||
pmhetre | >190 | >190 | >190 | Dormant | |
pmhetre1 | >190 | >190 | Dormant | ||
pmhetre2 | >190 | >190 | Dormant | ||
pmhetre3 | >190 | Dormant |
Can you help with sql query for dormant accounts which can check the last login on different endpoints ?
eg. if user has account on all 3 endpoints then lastlogindate should be > than 190 for all 3 accounts.
if it has account only on endpoint 1 or endpoint2 then check the lastlogindate only for those accounts.
if it has account only on AD then check only AD account.
Can anyone help with query similar to this requirement?
Thanks,
Poonam
07/04/2024 04:00 AM
Hi @poonammhetre I believe the user can have 'N' number of accounts and your requirement is to check the condition on all the 'N' accounts. Is that correct?
07/04/2024 04:03 AM
@naveenss User can have 1 account per endpoint. eg.
user can have AD , endpoint1 and endpoint2 all 3 accounts
user can have AD and point1 account
user can have AD and endpoint2 account
user can have only AD account.
in any of the above condition if lastlogindate for the accounts is greater than 190 days then that AD account will be marked as Dormant.
07/04/2024 08:47 AM
Sample
SELECT
username,
`AD Last logon`,
`EP1 Last logon`,
`EP2 Last logon`,
CASE
WHEN (`AD Last logon` > 190 OR `AD Last logon` IS NOT NULL)
AND (`EP1 Last logon` > 190 OR `EP1 Last logon` IS NOT NULL)
AND (`EP2 Last logon` > 190 OR `EP2 Last logon` IS NOT NULL)
THEN 'Dormant'
ELSE 'Active'
END AS status
FROM (
SELECT
username,
(SELECT DATEDIFF(CURDATE(), LASTLOGONDATE)
FROM accounts
WHERE name = users.username AND endpointkey = 1 LIMIT 1) AS `AD Last logon`,
(SELECT DATEDIFF(CURDATE(), LASTLOGONDATE)
FROM accounts
WHERE name = users.username AND endpointkey = 2 LIMIT 1) AS `EP1 Last logon`,
(SELECT DATEDIFF(CURDATE(), LASTLOGONDATE)
FROM accounts
WHERE name = users.username AND endpointkey = 3 LIMIT 1) AS `EP2 Last logon`
FROM users
WHERE username = '137658'
) rv;
07/05/2024 03:43 AM
@rushikeshvartak How to modify the requery to return only dormant accounts?
07/05/2024 06:50 AM
SELECT
username,
`AD Last logon`,
`EP1 Last logon`,
`EP2 Last logon`,
CASE
WHEN (`AD Last logon` > 190 OR `AD Last logon` IS NOT NULL)
AND (`EP1 Last logon` > 190 OR `EP1 Last logon` IS NOT NULL)
AND (`EP2 Last logon` > 190 OR `EP2 Last logon` IS NOT NULL)
THEN 'Dormant'
ELSE 'Active'
END AS status
FROM (
SELECT
username,
(SELECT DATEDIFF(CURDATE(), LASTLOGONDATE)
FROM accounts
WHERE name = users.username AND endpointkey = 1 LIMIT 1) AS `AD Last logon`,
(SELECT DATEDIFF(CURDATE(), LASTLOGONDATE)
FROM accounts
WHERE name = users.username AND endpointkey = 2 LIMIT 1) AS `EP1 Last logon`,
(SELECT DATEDIFF(CURDATE(), LASTLOGONDATE)
FROM accounts
WHERE name = users.username AND endpointkey = 3 LIMIT 1) AS `EP2 Last logon`
FROM users
WHERE username = '137658'
) rv where status='Dormant'