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

Post a Comment

Popular posts from this blog

Integration with vCloud Director failing after NSXT upgrade to 4.1.2.0 certificate expired

  Issue Clarification: after upgrade from 3.1.3 to 4.1.2.0 observed certificate to be expired related to various internal services.   Issue Verification: after Upgrade from 3.1.3 to 4.1.2.0 observed certificate to be expired related to various internal services.   Root Cause Identification: >>we confirmed the issue to be related to the below KB NSX alarms indicating certificates have expired or are expiring (94898)   Root Cause Justification:   There are two main factors that can contribute to this behaviour: NSX Managers have many certificates for internal services. In version NSX 3.2.1, Cluster Boot Manager (CBM) service certificates were incorrectly given a validity period of 825 days instead of 100 years. This was corrected to 100 years in NSX 3.2.3. However any environment originally installed on NSX 3.2.1 will have the internal CBM Corfu certs expire after 825 regardless of upgrade to the fixed version or not. On NSX-T 3.2.x interna...
Post a Comment
Read more

Calculate how much data can be transferred in 24 hours based on link speed in data center

  In case you are planning for migration via DIA or IPVPN link and as example you have 200Mb stable speed so you could calculate using the below formula. (( 200Mb /8)x60x60x24) /1024/1024 = 2TB /per day In case you have different speed you could replace the 200Mb by any rate to calculate as example below. (( 5 00Mb /8)x60x60x24) /1024/1024 =  5.15TB  /per day So approximate each 100Mb would allow around 1TB per day.
Post a Comment
Read more

Device expanded/shrank messages are reported in the VMkernel log for VMFS-5

    Symptoms A VMFS-5 datastore is no longer visible in vSphere 5 datastores view. A VMFS-5 datastore is no longer mounted in the vSphere 5 datastores view. In the  /var/log/vmkernel.log  file, you see an entry similar to: .. cpu1:44722)WARNING: LVM: 2884: [naa.6006048c7bc7febbf4db26ae0c3263cb:1] Device shrank (actual size 18424453 blocks, stored size 18424507 blocks) A VMFS-5 datastore is mounted in the vSphere 5 datastores view, but in the  /var/log/vmkernel.log  file you see an entry similar to: .. cpu0:44828)LVM: 2891: [naa.6006048c7bc7febbf4db26ae0c3263cb:1] Device expanded (actual size 18424506 blocks, stored size 18422953 blocks)   Purpose This article provides steps to correct the VMFS-5 partition table entry using  partedUtil . For more information see  Using the partedUtil command line utility on ESX and ESXi (1036609) .   Cause The device size discrepancy is caused by an incorrect ending sector for the VMFS-5 partition on the ...
Post a Comment
Read more