Skip to main content

Export users and their manager as a SamAccountName and to export some other custom attibutes

 

I got a request from our HR to modify the below script to let Manager of the users appear as its SamAccountName like "First.Last" or as login name. I did that on the users itself but can't do that for his/her manager field?
Also add a custom attribute as "PayrollManager"

Could you please helping me to modify the command?


Code:
csvde -f  "\\server\users.csv" -l "DN, objectclass, objectcategory,  givenName, sn, displayname, operatingSystem, operatingSystemversion,  operatingSystemServicePack, department, mail, title,  physicalDeliveryOfficeName, telephone, Company,  directReports, employeeID, manager, mobile, HireDate, streetAddress,  homePhone, whenCreated, memberof, telephoneNumber, pager,sAMAccountName"  -r objectCategory=person
To add an attribute to the script, you must make changes in three places. First, you must add the attribute to the list of attribute values retrieved by ADO. For example: add "PayrollManager"

Code:
' Comma delimited list of attribute values to retrieve.
strAttributes = "distinguishedName,sAMAccountName,givenName,sn,displayName," _
  & "department,mail,title,physicalDeliveryOfficeName,telephoneNumber," _
  & "company,employeeID,manager,mobile,streetAddress," _
  & "homePhone,whenCreated,pager,hireDate,directReports,memberOf,payrollManager"
Here, the order of the attributes does not matter. Second, you must
add the new attribute to the statement that writes a header line. Here the order must match the modification you make in step three. I willl add the new attribute to the end of the list:

Code:
' Output header line.
Wscript.Echo """DN"",""sAMAccountName"",""givenName"",""sn"",""displayName"",""" _
  & """department"",""mail"",""title"",""physicalDeliveryOfficeName"",""" _
  & """telephoneNumber"",""company"",""employeeID"",""manager"",""mobile"",""" _
  & """streetAddress"",""homePhone"",""whenCreated"",""pager"",""" _
  & """hireDate"",""directReports"",""memberOf"",""payrollManager"""
The trick here is to get the quotes and commas correct. The trick is that any double quote charactes in a quoted string must be doubled. That means the following statement:

Code:
Wscript.Echo """DN"",""sAMAccountName"",""givenName"""
Willl result in the following:




HTML Code:
"<DN value>","<sAMAccountName value>","<given name>"



Finally, you must add the new attribute to the statement that outputs attribute values. For example:

Code:
' Output values comma delimited, enclosed in quotes.
 Wscript.Echo """" & adoRecordset.Fields("distinguishedName").Value & """," _
  & QuoteValue(adoRecordset.Fields("sAMAccountName").Value) & "," _
  & QuoteValue(adoRecordset.Fields("givenName").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("sn").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("displayName").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("department").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("mail").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("title").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("physicalDeliveryOfficeName").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("telephoneNumber").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("company").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("employeeID").Value & "") & "," _
  & QuoteValue(strNTName) & "," _
  & QuoteValue(adoRecordset.Fields("mobile").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("streetAddress").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("homePhone").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("whenCreated").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("pager").Value & "") & "," _
  & QuoteValue(adoRecordset.Fields("hireDate").Value & "") & "," _
  & QuoteValue(strReports) & "," _
  & QuoteValue(strGroups) & "," _
  & QuoteValue(adoRecordset.Fields("payrollManager").Value & "")
I hope this makes sense.


The result for the code will be as this one.


Code:
Option Explicit

Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strDN
Dim objManagers, arrGroups, strGroups, arrReports, strReports
Dim strManager, strNTName, strItem

' Create dictionary object.
Set objManagers = CreateObject("Scripting.Dictionary")
objManagers.CompareMode = vbTextCompare

' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = ""

' Filter on all managers.
strFilter = "(directReports=*)"

' Comma delimited list of attribute values to retrieve.
strAttributes = "distinguishedName,sAMAccountName"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF
  ' Retrieve values and populate dictionary object with unique names.
  strDN = adoRecordset.Fields("distinguishedName").Value
  strName = adoRecordset.Fields("sAMAccountName").Value
  If (objManagers.Exists(strDN) = False) Then
    objManagers(strDN) = strName
  End If
  ' Move to the next record in the recordset.
  adoRecordset.MoveNext
Loop
adoRecordset.Close

' Filter on all user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "distinguishedName,sAMAccountName,givenName,sn,displayName," _
  & "department,mail,title,physicalDeliveryOfficeName,telephoneNumber," _
  & "company,employeeID,manager,mobile,streetAddress," _
  & "homePhone,whenCreated,pager,hireDate,directReports,memberOf,payrollManager"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery

' Run the query.
Set adoRecordset = adoCommand.Execute

' Output header line.
Wscript.Echo """DN"",""sAMAccountName"",""givenName"",""sn"",""displayName"",""" _
  & """department"",""mail"",""title"",""physicalDeliveryOfficeName"",""" _
  & """telephoneNumber"",""company"",""employeeID"",""manager"",""mobile"",""" _
  & """streetAddress"",""homePhone"",""whenCreated"",""pager"",""" _
  & """hireDate"",""directReports"",""memberOf"",""payrollManager"""
' Enumerate recordset and display in comma delimited format.
Do Until adoRecordset.EOF
  ' Retrieve memberOf multi-valued attribute.
  arrGroups = adoRecordset.Fields("memberOf").Value
  If IsNull(arrGroups) Then
    strGroups = ""
  Else
    ' Create semicolon delimited list of groups.
    For Each strItem In arrGroups
      If (strGroups = "") Then
        strGroups = strItem
      Else
        strGroups = strGroups & ";" & strItem
      End If
    Next
  End If
  ' Retrieve multi-valued directReports attribute.
  arrReports = adoRecordset.Fields("directReports").Value
  If IsNull(arrReports) Then
    strReports = ""
  Else
    ' Create semicolon delimited list of reports.
    For Each strItem In arrReports
      If (strReports = "") Then
        strReports = strItem
      Else
        strReports = strReports & ";" & strItem
      End If
    Next
  End If
  ' Retrieve sAMAccountName of manager.
  strManager = adoRecordset.Fields("manager").Value & ""
  If (objManagers.Exists(strManager) = True) Then
    strNTName = objManagers(strManager)
  Else
    strNTName = ""
  End If
  ' Output values comma delimited, enclosed in quotes.
  Wscript.Echo """" & adoRecordset.Fields("distinguishedName").Value & """," _
    & QuoteValue(adoRecordset.Fields("sAMAccountName").Value) & "," _
    & QuoteValue(adoRecordset.Fields("givenName").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("sn").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("displayName").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("department").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("mail").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("title").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("physicalDeliveryOfficeName").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("telephoneNumber").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("company").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("employeeID").Value & "") & "," _
    & QuoteValue(strNTName) & "," _
    & QuoteValue(adoRecordset.Fields("mobile").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("streetAddress").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("homePhone").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("whenCreated").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("pager").Value & "") & "," _
    & QuoteValue(adoRecordset.Fields("hireDate").Value & "") & "," _
    & QuoteValue(strReports) & "," _
    & QuoteValue(strGroups) & "," _
    & QuoteValue(adoRecordset.Fields("payrollManager").Value & "")
  adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close
adoConnection.Close

Function QuoteValue(ByVal strValue)
  ' Quote all values that have an embedded comma.
  If (InStr(strValue, ",") > 0) Then
    QuoteValue = """" & strValue & """"
  Else
    QuoteValue = strValue
  End If
End Function

Comments

Popular posts from this blog

Recreating a missing VMFS datastore partition in VMware vSphere 5.x and 6.x

    Symptoms A datastore has become inaccessible. A VMFS partition table is missing.   Purpose The partition table is required only during a rescan. This means that the datastore may become inaccessible on a host during a rescan if the VMFS partition was deleted after the last rescan. The partition table is physically located on the LUN, so all vSphere hosts that have access to this LUN can see the change has taken place. However, only the hosts that do a rescan will be affected.   This article provides information on: Determining whether this is the same problem Resolving the problem   Cause This issue occurs because the VMFS partition can be deleted by deleting the datastore from the vSphere Client. This is prevented by the software, if the datastore is in use. It can also happen if a physical server has access to the LUN on the SAN and does an install, for example.   Resolution To resolve this issue: Run the  partedUtil  command on the host with the issues and verify if your output

ما هى ال FSMO Roles

  بأختصار ال FSMO Roles هى اختصار ل Flexible Single Operation Master و هى عباره عن 5 Roles فى ال Active Directory و هما بينقسموا لقسمين A - Forest Roles 1- Schema Master Role و هى ال Role اللى بتتحكم فى ال schema و بيكون فى Schema Master Role واحد فى ال Forest بيكون موجود على Domain Controller و بيتم التحكم فيها من خلال ال Active Directory Schema Snap in in MMC بس بعد ما يتعمل Schema Register بواسطه الامر التالى من ال Cmd regsvr32 schmmgmt.dll 2-Domin Naming Master و هى ال Role المسئوله عن تسميه ال Domains و بتتأكد ان مفيش 2 Domain ليهم نفس الاسم فى ال Forest و بيتم التحكم فيها من خلال ال Active Directory Domains & Trusts B- Domain Roles 1-PDC Emulator و هى ال Role اللى بتتحكم فى ال Password change فى ال domain و بتتحكم فى ال time synchronization و هى تعتبر المكان الافتراضى لل GPO's و هى تعتبر Domain Role مش زى الاتنين الاولانيين و بيتم التحكم فيها من خلال ال Active directory Users & Computers عن طريق عمل كليك يمين على اسم الدومين و نختار operations master فى تاب ال PDC Emu

Unlock the VMware VM vmdk file

  Unlock the VMware VM vmdk file Kill -9 PID Sometimes a file or set of files in a VMFS become locked and any attempts to edit them or delete will give a device or resource busy error, even though the vm associated with the files is not running. If the vm is running then you would need to stop the vm to manipulate the files. If you know that the vm is stopped then you need to find the ESX server that has the files locked and then stop the process that is locking the file(s). 1. Logon to the ESX host where the VM was last known to be running. 2.  vmkfstools -D /vmfs/volumes/path/to/file  to dump information on the file into /var/log/vmkernel 3.  less /var/log/vmkernel  and scroll to the bottom, you will see output like below: a. Nov 29 15:49:17 vm22 vmkernel: 2:00:15:18.435 cpu6:1038)FS3: 130: &lt;START vmware-16.log&gt; b. Nov 29 15:49:17 vm22 vmkernel: 2:00:15:18.435 cpu6:1038)Lock [type 10c00001 offset 30439424 v 21, hb offset 4154368 c. Nov 29 15:49:17 vm22 vmkernel: gen 664