Tuesday, 31 March 2015

Active Directory LDAP Filter Queries


Active Directory Reports out of the box reports are divided into the following categories.

1.Active Directory User Reports
2.Active Directory Group Reports
3.Active Directory Computer Reports
4.Active Directory Exchange Reports
5.Active Directory GPO Reports
6.Active Directory OU Reports
7.Active Directory Security Reports
8.Active Directory NTFS Reports
9.Active Directory Other Reports


1. Active Directory User Reports:-

1.1 General Reports
   
     All Users:-
          It provides the details of all the users in the selected scope.
          
           "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370))"

    Users With Empty Attributes:-
          It provides the list of users whose specified attributes are empty.    
         
           "(&(objectCategory=Person)(objectClass=user)(&(!attribute1=*)(!attribute2=*)))"

    Managers:-
          It provides details of all the managers in the selected scope.     
         
          “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)                                 (manager=*))”

    Users without Managers:-
          It provides the list of users who do not have any managers assigned to them.
         
           “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
             (!manager=*))"

    Manager Based Users:-
          It provides the list of users that directly report to the selected user (Manager). The users
          listed in report are those who have the manager property set to this selected user.
         
          "(& (objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
           (manager=managerDN))"

     Users in more than One Group:-
          It provides the details of users who belong to more than one group.
         
          "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
           (memberOf=*))"

     Recently Created Users:-
          It provides the details of the user accounts created recently.
       
          "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
           (createTimeStamp>=givenTime))"


    Recently Modified Users:-
          This report generates the lists of user accounts modified recently.
       
           "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
            (modifyTimeStamp>=givenTime))"

    Users with Logon Script:-
         This report generates the list of users who have logon scripts.
          Logon scripts are those which run automatically when the user logon.    
   
         "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
           (scriptPath=*))"

    Users without Logon Script:-
        This report generates the list of users who don’t have logon scripts.
        Logon scripts are those which run automatically when the user logon.
     
        "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
           (!scriptPath=*))"

    Users with Profile:-
         This report generates the list of users who have profile path.    
     
         “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
           (profilePath=*))”

   Users without Profile:-
         This report generates the list of users who do not have profile path.
       
         “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
           (! profilePath=*))”

    Users with Share:-
This report generates the list of users who have share.

“(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
            (homedirectory=*))”

    Users without Share:-
This report generates the list of users who do not have share.

“(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
          (!homedirectory=*))”

     All Deleted Users:-
This report generates the list of all deleted users in the domain.

"(&(objectClass=user)(!objectClass=computer)(isDeleted=TRUE))"

     Recently Deleted Users:-
This report generates the list of all user account deleted recently in the domain.

"(&(objectClass=user)(!objectClass=computer)(isDeleted=TRUE)
  (whenChanged>=givenTime))"

1.2.Account Status Report

     Enabled Users:-
This report generates the list of all enbled user accounts.

“(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
(!userAccountControl:1.2.840.113556.1.4.803:=2))”

     Enabled Locked Users:-
This report generates the list of enabled and locked user accounts.

“(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
(!userAccountControl:1.2.840.113556.1.4.803:=2)(lockouttime>=1))”

     Enabled Unlocked Users:-
This report generates the list of enabled and unlocked user accounts.

“(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
(!userAccountControl:1.2.840.113556.1.4.803:=2)(!lockouttime>=1))”

     Disabled Users:-
This report generates the list of all disabled user accounts.

"(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
(userAccountControl:1.2.840.113556.1.4.803:=2))"


     Disabled Locked Users:-
This report generates the list of all disabled and locked user accounts.

“(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
(userAccountControl:1.2.840.113556.1.4.803:=2)(lockouttime>=1))”

     Disabled or Locked Users:-
         This report generates the list of all disabled or locked user accounts.
       
          “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
            (|(userAccountControl:1.2.840.113556.1.4.803:=2)(lockouttime>=1)))”

     Disabled Unlocked Users:-
         This report generates the list of all disabled and unlocked user accounts.
       
         “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
          (userAccountControl:1.2.840.113556.1.4.803:=2)(!lockouttime>=1))”

     Locked Out Users:-
         This report generates the list of all user accounts that have been locked out.
       
         "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
          (lockouttime>=1))"

     Unlocked Users:-
         This report generates the list of all user accounts that have been unlocked.
       
         “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
           (!lockouttime>=1))”

     Account Expired Users:-
         This report generates the list of all user accounts that have expired.
     
         "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
          (!accountExpires=0)(!accountExpires=9223372036854775807)
          (accountExpires<=currentTime))"

     Recently Account Expired Users:-
         This report generates the list of all user accounts that have expired in the given number of
          days.
     
         "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
           (!accountExpires=0)(!accountExpires=9223372036854775807)                               
           (accountExpires<=currentTime)(accountExpires>=givenTime))"

     Soon-to-Expire User Accounts:-
          This report generates the list of all user accounts that will expire within the given number of                 days.
       
          "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
           (!accountExpires=0)(!accountExpires=9223372036854775807)
           (!accountExpires<=currentTime)(accountExpires<=givenTime))"

     Account Never Expires:-
          This report generates the list of all user accounts which will never expire.
       
           "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
             (|(accountExpires=0)(accountExpires=9223372036854775807)))"

     Account Expires Between:-
         This report generates the list of user accounts that expires within the given period of days.
     
         “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
          (!accountExpires=0)(!accountExpires=9223372036854775807)
          (accountExpires>={giventime1})(accountExpires<={giventime2}))”

     Users with Account Set to Expire:-
         This report generates the list of users whose account set to expire.
       
         “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
          (!accountExpires=0)(!accountExpires=9223372036854775807))”

1.3.Logon Reports:-

     Inactive Users:-

        "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
          (|(!lastlogon=*)(lastlogon<=givenTime)))"

     Recently Logged on Users:-

        "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
          (lastlogon>=givenTime))"

     Users Never Logged On:-

         "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
           (|(lastlogon=0)(!lastlogon=*)))"

     Recently Bad Logged on Users:-

          "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
            (badPasswordTime>=givenTime))"

1.4.Password Reports:-
     
     Users whose Password Never Expires:-

           "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
             (userAccountControl:1.2.840.113556.1.4.803:=65536))"

     Password Expired Users:-

           "(&(objectCategory=person)(objectClass=user)(!(sAMAccountType=805306370))
             (!userAccountControl:1.2.840.113556.1.4.803:=65536)(!pwdLastSet=0)                                              (pwdLastSet<=timebased on maximum password age))"

     Soon-to-Expire User Passwords:-

          "(&(objectCategory=person)(objectClass=user)(!(sAMAccountType=805306370))
             (!userAccountControl:1.2.840.113556.1.4.803:=65536)(!pwdLastSet<={0})
             (pwdLastSet<=time based on maximum password age and the given time))"


     Password Expires Between:-

          "(&(objectCategory=person)(objectClass=user)(!(sAMAccountType=805306370))
            (!userAccountControl:1.2.840.113556.1.4.803:=65536)(pwdLastSet>={giventime1})
            (pwdLastSet<={giventime2}))"

     Password Changed Users:-

          "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
             (!pwdLastSet=0)(!pwdLastSet<=givenTime))"

     Password Unchanged Users:-

           "(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
             (!pwdLastSet=0)(!pwdLastSet>=givenTime))"

     Users with Password Set to Expire:-

           “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
             (!userAccountControl:1.2.840.113556.1.4.803:=65536))”

     Password Required Users:-

            “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
              (!userAccountControl:1.2.840.113556.1.4.803:=32))”

     Password Not Required Users:-

            “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
              (userAccountControl:1.2.840.113556.1.4.803:=32))”

     Password must change on next Logon:-

              “(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)
                (pwdlastset=0))”


2. Active Directory Group Reports:-


2.1. General Reports

     All Groups:-

          "(objectCategory=group)"

     Groups with Members:-

           “(&(objectCategory=group)(member=*))”

     Groups without Members:-

            "(&(objectCategory=group)(!member=*))"

     Managed Groups:-

             "(&(objectCategory=group)(managedby=*))"

     Unmanaged Groups:-

             "(&(objectCategory=group)(!managedby=*))"

     All Deleted Groups:-

            "(&(objectClass=user)(!objectClass=computer)(isDeleted=TRUE))"

     Recently Deleted Groups:-

            "(&(objectClass=group)(isDeleted=TRUE)(whenChanged>=givenTime))"

2.2.Type and Scope Reports:-

     Security Groups:-

           "(&(objectCategory=group)(groupType:1.2.840.113556.1.4.804:=2147483648))"

     Distribution Groups:-

            "(&(objectCategory=group)(!groupType:1.2.840.113556.1.4.804:=2147483648))"


3. Active Directory Computer Reports :-

3.1. General Reports :-

     All Computers:-

          "(&(objectcategory=computer)(objectClass=computer))"

     Workstations:-

           "(&(objectCategory=computer)(objectClass=computer)
                 (userAccountControl:1.2.840.113556.1.4.803:=4096))"