LDAP, CSVDE & LDIFDE

Example Return all users who have a name starts with “Gerry”

dsquery user -name Gerry*

Example Return the users who have a login name is cuicldap

dsquery user -samid cuicldap

Example dsquery command to return all attributes for a specific user

dsquery * "CN=admin,CN=Users,DC=lab2,DC=purplepi,DC=ie" -attr *

Return specific attributes for a LDAP search

C:\Users\admin>dsquery * "CN=Albert Einstein,CN=Users,DC=lab2,DC=purplepi,DC=ie" -attr mail objectGUID
  mail                       objectGUID
  [email protected]    {38AB190B-814C-45C7-8A24-4808B3D88A6D}

Example Response:

objectClass: top
objectClass: person
objectClass: organizationalPerson
objectClass: user
cn: admin
distinguishedName: CN=admin,CN=Users,DC=lab2,DC=purplepi,DC=ie
instanceType: 4
whenCreated: 04/18/2017 21:58:19
whenChanged: 01/06/2020 15:47:48
uSNCreated: 8198
memberOf: CN=Cisco_ICM_Setup,OU=Cisco_ICM,DC=lab2,DC=purplepi,DC=ie
memberOf: CN=Cisco_ICM_Config,OU=Cisco_ICM,DC=lab2,DC=purplepi,DC=ie
memberOf: CN=Domain Admins,CN=Users,DC=lab2,DC=purplepi,DC=ie
memberOf: CN=Users,CN=Builtin,DC=lab2,DC=purplepi,DC=ie
memberOf: CN=Administrators,CN=Builtin,DC=lab2,DC=purplepi,DC=ie
uSNChanged: 1151098
name: admin
objectGUID: {B18C5DA4-ABF2-492B-9A22-CC8985A91A33}
userAccountControl: 66048
badPwdCount: 0
codePage: 1252
countryCode: 1
badPasswordTime: 132228658721839030
lastLogoff: 0
lastLogon: 132228924307313910
logonHours: 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff 0xff
pwdLastSet: 131416540663230034
primaryGroupID: 513
objectSid: S-1-5-21-3443183386-3702885658-3434720438-1001
adminCount: 1
accountExpires: 0
logonCount: 65535
sAMAccountName: admin
sAMAccountType: 805306368
userPrincipalName: [email protected]
lockoutTime: 0
objectCategory: CN=Person,CN=Schema,CN=Configuration,DC=lab2,DC=purplepi,DC=ie
dSCorePropagationData: 08/09/2019 14:15:51
dSCorePropagationData: 04/19/2017 06:15:10
dSCorePropagationData: 04/18/2017 21:59:14
dSCorePropagationData: 01/01/1601 18:12:16
lastLogonTimestamp: 132227992683243026
msDS-SupportedEncryptionTypes: 0
ADsPath: LDAP://LAB2-AD-01.lab2.purplepi.ie/CN=admin,CN=Users,DC=lab2,DC=purplepi,DC=ie

Note: You cannot use CSVDE for modifying accounts, only for exporting or creation.
To modify accounts you need to use the LDIFDE command line tool.

Install The Active Directory Lightweight Directory Service Role (AD LDS) on your W2008R2 / W2012 Server
Or Download and install AD LDS for Windows 7


https://msdn.microsoft.com/en-us/library/aa772141(v=vs.85).aspx
https://technet.microsoft.com/en-us/library/cc732101(v=ws.11).aspx


csvde -f ADusers-Phone-Details.txt -r "(&(objectClass=user)(objectCategory=person))" -l "DN, objectClass, sAMAccountName, givenName, sn, displayName, telephoneNumber, ipPhone, msRTCSIP-USerEnabled, msRTCSIP-PrimaryUserAddress, department, extensionAttribute8, extensionAttribute9, manager, mobile, streetAddress, wWWHomePage, whenCreated, mail"

Example Filter

(&(&(objectclass=user)(telephonenumber=*))(sAMAccountName=alice))

Example - export users with a phone number

csvde -f ADusers-Phone-Details.txt -r "(&(objectclass=user)(telephonenumber=*))" -l "DN, objectClass, sAMAccountName, givenName, sn, displayName, telephoneNumber, mail, ipPhone, msRTCSIP-USerEnabled, msRTCSIP-PrimaryUserAddress, department, manager, mobile, streetAddress, wWWHomePage, whenCreated"
ldifde -r "(&(objectClass=user)(objectCategory=person))" -f export.ldf -l "ipPhone"

Sample Output from Exported file

dn: CN=Alice Anderson,OU=Users,OU=Accounts,DC=ucce9,DC=lab,DC=orourke,DC=tv
changetype: add
ipPhone: +35315551234

dn: CN=Supervisor Sarah,OU=Users,OU=Accounts,DC=ucce9,DC=lab,DC=orourke,DC=tv
changetype: add

Note on LDF file format. You need a “-” between modifications and it also requires a blank line following it, but only for the next user - see sample import file.

Step 1 Export User Data into CSV file format

csvde -f ADusers-Phone-Details-01.txt -r "(&(objectClass=user)(objectCategory=person))" -l "telephoneNumber,ipPhone"

Step 2 Update data and keep only the entries you want to modify - as in below CSV file format.
Filename: ADusers-Phone-Details-02.txt

DN,telephoneNumber,ipPhone
"CN=Alice Anderson,OU=Users,OU=Accounts,DC=ucce9,DC=lab,DC=orourke,DC=tv",12021,+35315551234
"CN=Bob Brown,OU=Users,OU=Accounts,DC=ucce9,DC=lab,DC=orourke,DC=tv",12022,+35316661005

Step 3 - create the template file for Log Parser (be care not to have any unwanted spaces in the template file)
Note: The log parser, removes the '+' sign contained in the CSVDE file, so I had to add it back (to ipPhone) within the template file.
Filename: AD-Telephone-LDF-Template.tpl

<LPBODY>
dn: %DN%
changetype: modify
replace: telephoneNumber
telephoneNumber: %telephoneNumber%
-
replace: ipPhone
ipPhone: +%ipPhone%
-
</LPBODY>

Step 4 - Create your LDIFDE file from your CSV file, using Log Parser as per below example

type ADusers-Phone-Details-02.txt | logparser "SELECT * FROM STDIN" -i:CSV -o:tpl -tpl:AD-Telephone-LDF-Template.tpl -q:on -stats:off > adusers-phone-details-02.ldf

Step 5 - review your LDF file
Filename: adusers-phone-details-02.ldf

dn: CN=Alice Anderson,OU=Users,OU=Accounts,DC=ucce9,DC=lab,DC=orourke,DC=tv
changetype: modify
replace: telephoneNumber
telephoneNumber: 12021
-
replace: ipPhone
ipPhone: +35315551234
-

dn: CN=Bob Brown,OU=Users,OU=Accounts,DC=ucce9,DC=lab,DC=orourke,DC=tv
changetype: modify
replace: telephoneNumber
telephoneNumber: 12022
-
replace: ipPhone
ipPhone: +35316661005
-

Step 6 - Import your LDF file to AD and review Logs

ldifde -i -f adusers-phone-details-02.ldf -j "C:\Program Files (x86)\Log Parser 2.2"
  • vendors/microsoft/ad/csvde.txt
  • Last modified: 2020/04/13 10:07
  • by gerardorourke