Solved

VB Script Needed to extract user information from active directory

Posted on 2011-03-09
3
370 Views
Last Modified: 2012-05-11
I am needing to gain a list of users where their Department equals something specific.  For example if Department = Merchandising I need a csv file that contains the users:

first name,last name,username,email address

It would be nice if when the script is executed it would pop up a window asking for the department name to query.
0
Comment
Question by:seaninman
  • 2
3 Comments
 
LVL 12

Expert Comment

by:prashanthd
Comment Utility
Try the following code....


On Error Resume Next

dpname="" 'mention departname to check
outputfile="C:\output.csv"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(outputfile, 2)


Dim objRootDSE, strDomain, strUsername, objConnection, objCommand, objRecordSet, strDN

' Get domain components
Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("DefaultNamingContext")

' Set ADO connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

' Set ADO command
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = "SELECT givenName,sn,mail,samaccountname FROM 'LDAP://" & strDomain & "' WHERE objectCategory='person' and department='"& dpname &"'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
' Set recordset to hold the query result
Set objRecordSet = objCommand.Execute

objTextFile.WriteLine "First Name,Last Name, AccountName, Mail"

Do While Not objRecordSet.EOF
   
    strfName = objGroup.Get("givenName")
    strlName = objGroup.Get("sn")
    strmail = objGroup.Get("mail")
    strSAMAccountName = objGroup.Get("sAMAccountName")
   
    objTextFile.WriteLine strfName &","& strlName &","& strSAMAccountName &","& strmail
   
    objRecordSet.MoveNext
Loop

objTextFile.Close

WScript.Echo "Done, Check output file"
0
 
LVL 8

Accepted Solution

by:
jawa29 earned 500 total points
Comment Utility
Hi seaninman

This should do what your asking.

Jawa29


Const ForWriting = 2

Set oRoot = GetObject("LDAP://RootDSE")
vDomain = oRoot.Get("DefaultNamingContext") 

vDepartment = InputBox("Department","Department","Enter Department")

Set oConnection = CreateObject("ADODB.Connection")
Set oCommand =   CreateObject("ADODB.Command")
oConnection.Provider = "ADsDSOObject"
oConnection.Open "Active Directory Provider"
Set oCommand.ActiveConnection = oConnection
oCommand.Properties("Page Size") = 1000

oCommand.CommandText = "<LDAP://" & vDomain & ">;(&(!userAccountControl:1.2.840.113556.1.4.803:=65536)((objectCategory=Person)(department=" & vDepartment & ")));" &_
	"givenName, sn, mail, samAccountName,;Subtree"
Set oRS = oCommand.Execute

sTxt = "Firstname,Surname,Logon,Email" & vbCrLf
Do While Not oRS.EOF
	sTxt = sTxt & oRS("givenName") & "," & oRS("sn") & "," & oRS("mail") & "," & oRS("samAccountName") & vbCrLf
	oRS.MoveNext
Loop

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oTextFile = oFSO.OpenTextFile("c:\UserExport.txt", ForWriting, True)
oTextFile.WriteLine(sTxt)
oTextFile.Close

Open in new window

0
 
LVL 12

Expert Comment

by:prashanthd
Comment Utility
hmmm...some errors in my earlier code, you can try this
'On Error Resume Next

dpname="IEMC" 'mention departname to check
outputfile="C:\output.csv"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.createTextFile(outputfile, 2)

Const ADS_SCOPE_SUBTREE = 2

Dim objRootDSE, strDomain, strUsername, objConnection, objCommand, objRecordSet, strDN

' Get domain components
Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("DefaultNamingContext")

' Set ADO connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

' Set ADO command
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = "SELECT givenName,sn,mail,samaccountname FROM 'LDAP://" & strDomain & "' WHERE 

objectCategory='person' and department='"& dpname &"'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
' Set recordset to hold the query result
Set objRecordSet = objCommand.Execute

objTextFile.WriteLine "First Name,Last Name, AccountName, Mail"

Do While Not objRecordSet.EOF 
    
    strfName = objRecordSet.Fields("givenName").Value
    strlName = objRecordSet.Fields("sn").Value
    strmail = objRecordSet.Fields("mail").Value
    strSAMAccountName = objRecordSet.Fields("sAMAccountName").Value
    
    objTextFile.WriteLine strfName &","& strlName &","& strSAMAccountName &","& strmail
wscript.echo strfName &","& strlName &","& strSAMAccountName &","& strmail
    
    objRecordSet.MoveNext
Loop

objTextFile.Close

WScript.Echo "Done, Check output file"

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now