?
Solved

Adding a new AD column to a VB script

Posted on 2011-02-27
3
Medium Priority
?
241 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
[X]
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
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article demonstrates probably the easiest way to configure domain-wide tier isolation within Active Directory. If you do not know tier isolation read https://technet.microsoft.com/en-us/windows-server-docs/security/securing-privileged-access/s…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
Suggested Courses
Course of the Month11 days, 21 hours left to enroll

752 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