Report to Show any AD accounts disabled in the last 30 days

[Community_User]

Originally posted on June 25 2020 at 10:45 UTC

Hello,

I have been trying to create an analytical report which will shows me which AD user accounts have been disabled in the last 30 days. So the account would have gone from Active (1) to Inactive (2) in that time frame.

I have been fudging around, and I think the below shows what accounts have been disabled in the last 30 days but only if the account was created in the last 30 days? Our AD endpoint for AD is 1. I think I also need to change the endpoint key below somewhere to 1?

Thanks if you can help at all.

SELECT DISTINCT a.name 'Account Name', e.displayname 'Application Name', a.CREATED_ON 'Account Created', a.lastlogondate 'Last Logon', CASE WHEN a.status = 2 THEN 'Inactive' ELSE a.status END AS 'Account Status' FROM accounts a, endpoints e, securitysystems s WHERE a.endpointkey = e.endpointkey AND e.securitysystemkey = s.systemkey AND a.status IN (2 , 'Active', 'Manually Provisioned') and a.accountkey in (SELECT accountkey FROM user_accounts ua) AND DATEDIFF(CURDATE(), CREATED_ON) <= 30 ORDER BY s.systemname;

[Community_User]

Originally posted on June 25 2020 at 14:42 UTC

Hi David,

Please try to use updatedate instead of created_on to check the disabled accounts in last 30 days.

DATEDIFF(CURDATE(), a.updatedate)

Also, here's the Saviynt schema documentation for details on the tables and columns for your reference

https://saviynt.freshdesk.com/a/solutions/articles/43000521404

[Community_User · Accepted solution]

Originally posted on June 26 2020 at 07:52 UTC

Thank you very much.