Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

SQL query to generate dormant report based on different endpoints

poonammhetre
New Contributor II
New Contributor II

Hello,

We have requirement as below.

Generate dormancy report for AD based on below conditions. 

UserAD lastloginEndpoint1 lastloginEndpoint2 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

5 REPLIES 5

naveenss
All-Star
All-Star

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?

 

Regards,
Naveen Sakleshpur
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

poonammhetre
New Contributor II
New Contributor II

@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.

rushikeshvartak
All-Star
All-Star

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;

rushikeshvartak_0-1720108045570.png

 


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

poonammhetre
New Contributor II
New Contributor II

@rushikeshvartak  How to modify the requery to return only dormant accounts?

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'

 


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.