Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Adding a new AD column to a VB script

Posted on 2011-02-27
3
Medium Priority
?
242 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Always backup Domain, SYSVOL etc.using processes according to Microsoft Best Practices. This is meant as a disaster recovery process for small environments that did not implement backup processes and did not run a secondary domain controller that ne…
Here's a look at newsworthy articles and community happenings during the last month.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
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.

604 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