SQL query to generate dormant report based on different endpoints

[poonammhetre]

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

[naveenss]

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?

[poonammhetre]

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

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;

[poonammhetre]

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

[rushikeshvartak]

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'