Solved

Adding a new AD column to a VB script

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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

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

In this article, I am going to show you how to simulate a multi-site Lab environment on a single Hyper-V host. I use this method successfully in my own lab to simulate three fully routed global AD Sites on a Windows 10 Hyper-V host.
This article runs through the process of deploying a single EXE application selectively to a group of user.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

763 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