CSVDE & LDAP queries

To export information from Active Directory using CSVDE, LDAP queries are used with CSVDE. Also, LADP queries can be used in Active Directory (AD) Users and Computers, there’s a section at the top called Saved Queries. You can right click on Saved Queries to create a new Query > click Define Query > select Custom Search from the drop down list > click Advanced. LDAP queries can be added here.

-r = LDAP search filter (Default to "(objectClass=*)")

  • = (EQUAL TO) – This LDAP argument means a certain attribute must be equal to a certain value to be true. For example, if you want to find all objects that have the first name of John, you would use:
    • (givenName=John)
  • & (logical AND) – You use this syntax when you have more than one condition, and you want all conditions in the series to be true. For example, if you want to find all of the people that have the first name of John and live in Dallas, you would use:
    • (&(givenName=John)(l=Dallas))
  • ! (logical NOT) – This operator is used to exclude objects that have a certain attribute. Suppose you need to find all objects except those that have the first name of John. You would use the following statement
    • (!givenName=John)
  • * (wildcard) – You use the wildcard operator to represent a value that could be equal to anything. One such situation might be if you wanted to find all objects that have a value for title. You would then use:
    • (title=*)
    • (givenName=Jo*)
  • | = (logical OR)
  • ~= = (approx equal)

  • >= = (greater than or equal)

  • <= = (less than or equal)

-l = List of attributes
-p = Search Scope (Base/OneLevel/Subtree)
-d = The root of the LDAP search

Some Examples

To get a list of all contacts

csvde -m -f c:\Contacts.csv –s DC-Name -d "DC=domain,DC=com" -r "(&(objectCategory=person)(objectClass=contact))"

To get all mail enabled users that aren’t disabled

csvde -m -f c:\MailEnabledUsers.csv –s DC-NAME -d "DC=domain,DC=local" -r "(&(objectCategory=person)(mailNickname=*)(objectClass=user)(!useraccountcontrol=514)(!useraccountcontrol=546)(!useraccountcontrol=66050))"

List of groups using CSVDE

csvde -m -f c:\Groups.csv -s NAME-OF-DC -d "DC=domain,DC=local" -r objectCategory=group

List of users with some attributes CSVDE

csvde -m -f c:\Users.csv -s NAME-OF-DC -d "DC=domain,DC=local" -r objectCategory=Person -l givenName,sn,description,info,telephoneNumber,ipPhone,mobile

List of non-disabled users with specific mail domain as primary email address

csvde -m -f c:\Contacts.csv –s DC-Name -d "DC=domain,DC=com" -r "(|(objectClass=user)(objectClass=group))(!useraccountcontrol=514)(!useraccountcontrol=546)(!useraccountcontrol=66050)(mail=*@domain.com)"

List of all disabled users

csvde -f c:\AllDisabled.csv -r "(|(useraccountcontrol=514)(useraccountcontrol=546)(useraccountcontrol=66050))" -l "DN, objectclass, objectcategory, sn, givenName, sAMAccountName, useraccountcontrol"

Here’s a reference, which is not complete, but should help in finding certain account states.

512 – Enabled Account
514 – Disabled Account
544 – Enabled, Password Not Required
546 – Disabled, Password Not Required
66048 – Enabled, Password Doesn’t Expire
66050 – Disabled, Password Doesn’t Expire
66080 – Enabled, Password Doesn’t Expire & Not Required
66082 – Disabled, Password Doesn’t Expire & Not Required
262656 – Enabled, Smartcard Required
262658 – Disabled, Smartcard Required
262688 – Enabled, Smartcard Required, Password Not Required
262690 – Disabled, Smartcard Required, Password Not Required
328192 – Enabled, Smartcard Required, Password Doesn’t Expire
328194 – Disabled, Smartcard Required, Password Doesn’t Expire
328224 – Enabled, Smartcard Required, Password Doesn’t Expire & Not Required
328226 – Disabled, Smartcard Required, Password Doesn’t Expire & Not Required

List of computers CSVDE

csvde -m -f c:\Computers.csv -s NAME-OF-DC -d "DC=domain,DC=local" -r objectCategory=Person -l sAMAccountName

Generic LDIFDE

ldifde -f Exportuser.ldf -s Server1 -d "dc=Export,dc=com" -p subtree -r "(&(objectCategory=person)(objectClass=User)(givenname=*))" -l "cn,givenName,objectclass,samAccountName"

Both, same export for each LDIFDE & CSVDE, comparison

ldifde -f c:\User_13-09-2010.ldf -s NAME-OF-DC -d "dc=domain,dc=local" -r "(&(objectCategory=person)(objectClass=User)(sn=*))" -l "cn,givenName,sn,description,info,telephoneNumber,ipPhone,mobile"

csvde -m -f c:\User_13-09-2010.csv -s NAME-OF-DC -d "dc=domain,dc=local" -r "(&(objectCategory=person)(objectClass=User)(sn=*))" -l "cn,givenName,sn,description,info,telephoneNumber,ipPhone,mobile"

Importing back in using LDIFDE

To Modify attributes in AD it is very important that the following format be followed for the import file, especially the "-" on a single line followed by a completely blank line on the next line. To import this file all you need to do is run: ldifde -i -f Import.ldf -s Server.

Sample Import/Modify File Format:

dn: CN=Jane Doe,OU=Staff,DC=microsoft,DC=com

changetype: modify

replace:description

description: 1000295

replace:telephoneNumber

telephoneNumber: +61212345678

replace:info

info: Consultant

replace:ipPhone

ipPhone: 1924

replace:mobile

mobile: +61401123123

Computers and Internet Uncategorized

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: