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

Question كيفية عمل share للـ outlook conntact لكل الـ Domain Users

  الحل بسيط جدا عايز الكونتاكت تتحدث دايما بحيث انك لما تضيف يوزر جديد يسمع في الكونتاكت اول حاجه بتدخل علي in office 2003 tools --- email account ---- add address book --- internet directory service (LDAP) type your server name then login info . mark this server require me to logon type any user on active directory and its password then save and close outlook and open it again now you will find all your active directory users in address book

3 things has to be done for better performance

  Tips from Goutham: 3 things has to be done for better performance: By default, XP displays extra graphic objects for menu items which can slow down your display. 1. To turn off these selectively... Right click My Computer Select Properties Click Advanced tab Under Performance, click Settings button To turn them all off, select Adjust for best performance Preference is to leave them all off except for Show shadows under mouse pointer and Show window contents while dragging 2. To speed up the display of the Start Menu Items, turn off the menu shadow. Right click open area of the Desktop Select Properties Click Appearance tab Click Effects button Uncheck Show shadows under menus 3. You can increase system performance by loading more of the system into memory. DO NOT attempt this with less then 512MBs of ram. Your system will become unstable. Click Start Click Run Enter regedit Click OK Go to HKEY_LOCAL_MACHINE SYSTEM CurrentControlSet Control Session Manager Memory Management Double cli

The difference between DNS and NDS

  Novell Directory Services(NDS) - Novell Directory Services (NDS) is a popular software product for managing access to computer resources and keeping track of the users of a  network , such as a company's  intranet , from a single point of administration. Using NDS, a network administrator can set up and control a  database  of users and manage them using a  directory  with an easy-to-use graphical user interface ( GUI ). Users of computers at remote locations can be added, updated, and managed centrally. Applications can be distributed electronically and maintained centrally. NDS can be installed to run under  Windows NT , Sun Microsystem's Solaris, and IBM's  OS/390  as well as under Novell's own  NetWare  so that it can be used to control a multi-platform network. NDS is generally considered an industry  benchmark  against which other products, such as Microsoft's Active Directory, must compete. Lucent Technologies plans to integrate NDS into its own QIP product