AD User reports

 

Ldap plus AD Help Desk Professional Tool User Reports give detailed information on users, below you can find detailed information on how these reports are created.

 

Active Directory All Users Report

All Users report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 subtreescope

 

Active Directory Diabled Users Report

All Disabled Users report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and userAccountControl:1.2.840.113556.1.4.803:=2
subtreescope

 

Active Directory Enabled Users Report

All Enabled Users report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and userAccountControl:1.2.840.113556.1.4.803:!=2
subtreescope

 

Active Directory Locked Out Users Report

All Locked Out Users report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,lockoutTime from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and lockoutTime>=1
subtreescope

 

Active Directory Users with Logon script

All Users with Logon Script report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,scriptPath from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and scriptPath is not null
subtreescope

 

Active Directory Users without Logon script

All Users without Logon Script report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,scriptPath from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and scriptPath is null
subtreescope

 

Active Directory Users with Managers

All Users with Managers report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,manager from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and manager is not null
subtreescope

 

Active Directory Users without Managers

All Users without Managers report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,manager from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and manager is null
subtreescope

 

Active Directory Recently Deleted Users

All Recently Deleted Users report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectClass='user' and isDeleted=TRUE subtreescope

 

Active Directory Users with Password Never Expires

All Users with Password Never Expires report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and userAccountControl:1.2.840.113556.1.4.803:=65536 subtreescope

 

Active Directory Users with Password Always Expire

All Users with Password Always Expire report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and userAccountControl:1.2.840.113556.1.4.803:!=65536 subtreescope

 

Active Directory Users with Encrypted Password Allowed

All Users with Encrypted Password report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and userAccountControl:1.2.840.113556.1.4.803:=128
subtreescope

 

Active Directory Users with Password Changed in Last 30 Days

All Users with Password Changed in Last 60 Days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,pwdLastSet from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and pwdLastSet!=0 and pwdLastSet>= CurrentTimestamp -30 days subtreescope

 

Active Directory Users with Password Not Changed in Last 60 Days

All Users with Password Changed in Last 60 Days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,pwdLastSet from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and pwdLastSet!=0 and pwdLastSet>= CurrentTimestamp -60 days subtreescope

 

Active Directory Users with Password Changed Between

All Users with password not changed between the dates specified report is created using the following sqlldap statement, you can specify from and to timestamp in the popup dialog.

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,pwdLastSet from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and pwdLastSet!=0 and pwdLastSet>=$Timestamp1 and pwdLastSet<=$Timestamp2 subtreescope

 

Active Directory Users with Password Not Changed in Last 30 Days

All Users with Password not Changed in Last 30 Days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,pwdLastSet from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and pwdLastSet!=0 and pwdLastSet<= CurrentTimestamp -30 days subtreescope

 

Active Directory All Users Password must change at next logon

All Users with password must change at next logon report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,pwdLastSet from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and pwdLastSet=0
subtreescope

 

Active Directory All Users who entered bad password in last 7 days

All Users who entered bad password in last 7 days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,badPasswordTime from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and badPasswordTime>= CurrentTimestamp -7 days subtreescope

 

Active Directory All Users who entered bad password in last 30 days

All Users who entered bad password in last 30 days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,badPasswordTime from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and badPasswordTime>= CurrentTimestamp -30 days subtreescope

 

Active Directory All Users who entered bad password between..

All Users who entered bad password between the dates specified report is created using the following sqlldap statement, you can specify from and to timestamp in the popup dialog.

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,badPasswordTime from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and badPasswordTime>=$Timestamp1 and badPasswordTime<=$Timestamp2 subtreescope

 

Active Directory All Users Created in Last 30 Days Report

All Users Created in Last 30 Days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and createTimestamp >= CurrentTimestamp -30 days
subtreescope

 

Active Directory All Users Created in Last 60 Days Report

All Users Created in Last 60 Days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and createTimestamp >= CurrentTimestamp -60 days
subtreescope

 

Active Directory All Users Created in Last 90 Days Report

All Users Created in Last 90 Days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and createTimestamp >= CurrentTimestamp -90 days
subtreescope

 

Active Directory All Users Created between.. Report

All Users Created between the dates specified report is created using the following sqlldap statement, you can specify from and to timestamp in the popup dialog.

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and createTimestamp >=$Timestamp1 and createTimestamp <= $Timestamp2 subtreescope

 

Active Directory All Users Modified Today Report

All Users Modified Today report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and modifyTimestamp <= CurrentEndofDay and modifyTimestamp >=CurrentStartofDay subtreescope

 

Active Directory All Users Modified Yesterday Report

All Users Modified Yesterday report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and modifyTimestamp <= CurrentEndofDay -1 days and modifyTimestamp >=CurrentStartofDay -1 days subtreescope

 

Active Directory All Users Modified in Last 7 Days Report

All Users Modified in Last 7 Days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and modifyTimestamp >= CurrentTimestamp -7 days
subtreescope

 

Active Directory All Users Modified in Last 30 Days Report

All Users Modified in Last 30 Days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and modifyTimestamp >= CurrentTimestamp -30 days
subtreescope

 

Active Directory All Users Modified in Last 60 Days Report

All Users Modified in Last 60 Days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and modifyTimestamp >= CurrentTimestamp -60 days
subtreescope

 

Active Directory All Users Modified in Last 90 Days Report

All Users Modified in Last 90 Days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and modifyTimestamp >= CurrentTimestamp -90 days
subtreescope

 

Active Directory All Users Modified between.. Report

All Users Modified between the dates specified report is created using the following sqlldap statement, you can specify from and to timestamp in the popup dialog.

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and modifyTimestamp >=$Timestamp1 and modifyTimestamp <= $Timestamp2 subtreescope

 

Active Directory All Users Specified Attribute is null or not present

All Users where specified attribute is null report report is created using the following sqlldap statement, you can specifiy the attribute in the popup dialog.

Select mail, cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and $Attribute is null subtreescope

 

Active Directory All Users Specified Attribute is not null or present

All Users where specified attribute is not null report is created using the following sqlldap statement, you can specifiy the attribute in the popup dialog.

Select mail, cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and $Attribute is not null subtreescope

 

Active Directory All Account Expired Users Report

All account expired users report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,accountExpires from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and accountExpires!=0 and accountExpires!=9223372036854775807 and accountExpires<= CurrentTimestamp subtreescope

 

Active Directory All Account Never Expire Users Report

All user accounts which will never expire report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,accountExpires from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and accountExpires!=0 and accountExpires!=9223372036854775807 and accountExpires<= CurrentTimestamp subtreescope

 

Active Directory All Users Account Expired in the Last 7 days Report

All user accounts expired in the last 7 days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,accountExpires from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and accountExpires!=0 and accountExpires!=9223372036854775807 and accountExpires>= CurrentTimestamp -7 days and accountExpires<= CurrentTimestamp
subtreescope

 

Active Directory All Users Account which will expire in the next 30 days Report

All user accounts will expire in the next 30 days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,accountExpires from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and accountExpires!=0 and accountExpires!=9223372036854775807 and accountExpires<= CurrentTimestamp + 30 days and accountExpires>= CurrentTimestamp
subtreescope

 

Active Directory All Users Account which expired/expire between..

All users account which are expired/expire between the dates specified report is created using the following sqlldap statement, you can specify from and to timestamp in the popup dialog.

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,accountExpires from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and accountExpires!=0 and accountExpires!=9223372036854775807 and accountExpires>=$Timestamp1 and accountExpires<=$Timestamp2 subtreescope

 

Active Directory All Users Account Lockedout between..

All Locked out user accounts between the dates specified report is created using the following sqlldap statement, you can specify from and to timestamp in the popup dialog.

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,lockoutTime from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and lockoutTime>=1 and lockoutTime>=$Timestamp1 and lockoutTime<=$Timestamp2 subtreescope

 

Active Directory All Users with Dial-in Access allowed Report

All account with dial-in access allowed report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,msNPCallingStationID from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and msNPCallingStationID=TRUE
subtreescope

 

Active Directory All Users with Dial-in Access Denied Report

All account with dial-in access denied report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,msNPCallingStationID from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and msNPCallingStationID=FALSE
subtreescope

 

Active Directory All Users who never logged on

All user who never logged on report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,msNPCallingStationID from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and msNPCallingStationID=FALSE
subtreescope

 

Active Directory All Users who logged on Today

All user who logged on Today report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,lastLogon from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and lastlogon <= CurrentEndofDay and lastlogon >=CurrentStartofDay subtreescope

 

Active Directory All Users who logged on Yesterday

All user who logged on Yesterday report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,lastLogon from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and lastlogon <= CurrentEndofDay -1 days and lastlogon >=CurrentStartofDay -1 days subtreescope

 

Active Directory All Users who logged in last 7 days

All user who logged in last 7 days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,lastLogon from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and lastlogon <= CurrentEndofDay -1 days and lastlogon >=CurrentStartofDay -7 days subtreescope

 

Active Directory All Users who logged in last 30 days

All user who logged in last 30 days report is created using the following sqlldap statement

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,lastLogon from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and lastlogon <= CurrentEndofDay -1 days and lastlogon >=CurrentStartofDay -7 days subtreescope

 

Active Directory All Users who logged in between..

All user who logged in between the dates specified report is created using the following sqlldap statement, you can specify from and to timestamp in the popup dialog.

Select cn,creatorsName,createTimeStamp,modifiersName,modifyTimeStamp,sAMAccountName,userAccountControl,lastLogon from RootDSE where objectCategory='person' and objectClass='user' and sAMAccountType!=805306370 and lastlogon <= CurrentEndofDay -1 days and lastlogon >=CurrentStartofDay -7 days subtreescope

 

 


Ldapsoft Home page Ldap plus AD Help Desk Professional Tool Home Page