إضغط لتفاصيل الإعلانات



Results 1 to 1 of 1
Share
  1. #1
    Join Date
    Nov 2007
    Location
    Arab world!
    Posts
    6,169
    Blog Entries
    4
    Rep Power
    10

    Thumbs up 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
    Last edited by Mohamed Fouad; 10-02-2011 at 12:53 PM.


  2. Facebook Comments - تعليقـك على الفيس بوك يسعدنا ويطور مجهوداتنـا


  3. Forum Ads:

  4. Forum Ads:

    اضفط هنا لمعرفة تفاصيل الإعلانات بالموقع


  5. Forum Ads:

    -->

Similar Threads

  1. Replies: 0
    Last Post: 23-03-2013, 01:58 AM
  2. Replies: 0
    Last Post: 26-12-2010, 06:31 PM
  3. IT Company needs CEO - GM - Project Manager - Marketing Manager - Sales Manager Jobs
    By Mohamed Fouad in forum Information Technology (IT)
    Replies: 0
    Last Post: 23-09-2010, 10:35 PM
  4. some ways to Export Active Directory data
    By Mohamed Fouad in forum Microsoft
    Replies: 2
    Last Post: 05-05-2010, 04:36 PM
  5. Replies: 5
    Last Post: 30-01-2010, 03:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

أقسام المنتدى

الروابط النصية

تابع جروبنا على الفيس بوك

صفحة Egypt Engineers على الفيس بوك

تابعنا على linkedin

جروبنا على الياهو جروب