Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Adding a new AD column to a VB script

Posted on 2011-02-27
3
237 Views
Last Modified: 2012-05-11
HI All a simple one I hope,
I had a script worked on for me and it contains an output of all staff/users in our system but I need now to add more columns to the output.
In the first instance I need to add a column for their job title. But there will be more over time so I need to know where and what to insert into this script to give me what I need.
I have had a few tries and to no avail. Could someone please show me where the new lines and additions need to go. I can find the correct attributes and values easy enough but I have no real scope on VB to edit this script to give me the versatility I need.
Script is below. Please let me know if you need more.
Thanks in advance
Craig
MrBungle50
Const PROPERTIES_TO_LOAD = "sAMAccountName,name,givenName,sn,userAccountControl,memberOf"
Const REPORT_FILE = "C:\UserReport.csv"
 
Const ADS_UF_ACCOUNTDISABLE = 2
 
Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
 
Dim objCommand : Set objCommand = Createobject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
 
Dim objRootDSE : Set objRootDSE = GetObject("LDAP://RootDSE")
 
objCommand.CommandText = "<LDAP://" & objRootDSE.Get("defaultNamingContext") & ">;" & _
  "(&(objectClass=user)(objectCategory=person));" & PROPERTIES_TO_LOAD & ";subtree"
 
Dim objFSO  : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objFile : Set objFile = objFSO.OpenTextFile(REPORT_FILE, 2, True, 0)
 
objFile.WriteLine "Username,Name,FirstName,LastName,Status,MemberOf"
 
Dim objRecordSet        : Set objRecordSet = objCommand.Execute
Dim arrPropertiesToLoad : arrPropertiesToLoad = Split(PROPERTIES_TO_LOAD , ",")
 
Dim arrValues()
Do Until objRecordSet.EOF
  Dim strProperty, strValue : Dim i : i = 0
  For Each strProperty in arrPropertiesToLoad
    If IsNull(objRecordSet.Fields(strProperty)) Then
      strValue = ""
    Else
      Select Case strProperty
        Case "userAccountControl"
          If objRecordSet.Fields("userAccountControl") AND ADS_UF_ACCOUNTDISABLE Then
            strValue = "Disabled"
          Else
            strValue = "Enabled"
          End If
        Case "memberOf" : strValue = Join(objRecordSet.Fields("memberOf"), ";")
        Case Else       : strValue = objRecordSet.Fields(strProperty)
      End Select
    End If
 
    ReDim Preserve arrValues(i)
    arrValues(i) = strValue : i = i + 1
  Next
 
  objFile.WriteLine """" & Join(arrValues, """,""") & """"
 
  objRecordSet.MoveNext
Loop

Open in new window

0
Comment
Question by:mrbungle50
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 250 total points
ID: 34993317
Hi, for the most part, it looks like just changing this line:
Const PROPERTIES_TO_LOAD = "sAMAccountName,name,givenName,sn,userAccountControl,memberOf"

to this
Const PROPERTIES_TO_LOAD = "sAMAccountName,name,givenName,sn,userAccountControl,memberOf,Title"

and this line:
objFile.WriteLine "Username,Name,FirstName,LastName,Status,MemberOf"

to this
objFile.WriteLine "Username,Name,FirstName,LastName,Status,MemberOf,JobTitle"

It depends on the data type whether you'll need to add something else, but for strings like Title, it should work fine.

Regards,

Rob.
0
 

Author Closing Comment

by:mrbungle50
ID: 34993347
Thanks Rob, you've got missed the first part when I was mucking about and then added spaces like a clown!
Your tips made it for me, got it in one.
Thanks a million, enjoy the points.
Cheers
Craig
MrBungle50
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34993349
No worries. Thanks for the grade.

Regards,

Rob.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
This article explains the steps required to use the default Photos screensaver to display branding/corporate images
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question