Solved

Adding a new AD column to a VB script

Posted on 2011-02-27
3
239 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 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
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.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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