?
Solved

Need help with Active Directory Export Script

Posted on 2004-11-08
7
Medium Priority
?
1,010 Views
Last Modified: 2012-05-05


Below I have put together a script which exports user information from a specific OU in my organization. The script works great but I'm still not completely satisfied with it. If possible, I'd like some help to accomplish the following tasks.

1. Right now there are numerous blank rows, this occurs whenever the script passes over a security group. In order to clean up the excel spreadsheet, I'd like to eliminate (exclude) security groups.
2. I would also like the script to exclude any user account that has SVC or Admin in the sAMAccountName name.

I'm more of a messaging engineer with AD experience, much of what I have below is pieced together from my findings on MSDN. I also received some help from BlueDevilFan to clean up the street address field. So please dumb it down for me when you respond. =)

Thanks in advance!
Brian




*******************************
Begin Script
*******************************


' Created for AD User Report
' This script exports the following fields
' samaccountname, displayname, description, streetaddress, I,
' st, postalcode, mail, mailnickname, homedb, homedrive, homedirectory,
' whencreated, employeeid extensionattribute4, manager, telephone
' Each line is tab delimited to be used for the XLS format


'Global variables
Dim oContainer
Dim OutPutFile
Dim FileSystem

'Initialize global variables
Set FileSystem = WScript.CreateObject("Scripting.FileSystemObject")
Set OutPutFile = FileSystem.CreateTextFile("CORP AD Monthly User Report.xls", True)

OutPutFile.WriteLine      "Username" & vbtab & "Description" & vbtab & "Street Address" & vbtab & "City" & vbtab & "State" & vbtab & 
"Zip Code" & vbtab & "Display Name" & vbtab & "Email Address" & vbtab & "Email Alias" & vbtab & "Exchange Home Server" & vbtab & "Home Drive" & vbtab & "Home Directory" & vbtab & "Created" & vbtab & "EmployeeID" & vbtab & "extensionAttribute4" & vbtab & "Manager" & vbtab & "Telephone Number"

Set oContainer = GetObject("LDAP://OU=Users,OU=Accounts,DC=Corp,DC=Contoso,DC=com")

'Enumerate Container
EnumerateUsers oContainer

'Clean up
OutPutFile.Close
Set FileSystem = Nothing
Set oContainer = Nothing

WScript.Echo "Finished"
WScript.Quit(0)
Sub EnumerateUsers(oCont)
Dim oUser
For Each oUser In oCont
Select Case LCase(oUser.Class)
Case "user"


' User Account Login
If Not IsEmpty(oUser.sAMAccountName) Then
OutPutFile.Write oUser.sAMAccountName & vbtab
'End If
 

' User Account Description
OutPutFile.Write oUser.description & vbtab

' User Account Address Information
' First line is to search and replace returns with a space

strAddress = Replace(oUser.streetAddress, vbCrLf, " ")
    OutPutFile.Write strAddress & vbtab
    OutPutFile.Write oUser.L & vbtab
    OutPutFile.Write oUser.st & vbtab
    OutPutFile.Write oUser.postalCode & vbtab

' User Account Display Name
OutPutFile.Write oUser.displayName & vbtab

'Email Information
OutPutFile.Write oUser.mail & vbtab
OutPutFile.Write oUser.mailNickname & vbtab
' Distinguished Path of Mailbox Home Server
OutPutFile.Write oUser.msExchHomeServerName & vbtab

' User Home Directory Information
OutPutFile.Write oUser.homedrive & vbtab
OutPutFile.Write oUser.homeDirectory & vbtab

' User Account General
OutPutFile.Write oUser.whencreated & vbtab
OutPutFile.Write oUser.employeeID & vbtab
OutPutFile.Write oUser.extensionAttribute4 & vbtab
OutPutFile.Write oUser.manager & vbtab

' User Account Phone Number
OutPutFile.Write oUser.telephoneNumber & vbtab

Case "organizationalunit" , "container"

EnumerateUsers oUser
End Select
OutPutFile.WriteLine
Next
End Sub
0
Comment
Question by:BNettles73
  • 4
  • 3
7 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 12524540
Hi again, Brian.

I've revised the script as you requested.  The blank lines are definitely gone and I think I got rid of the accounts with svc or admin in their names, but I'm not sure since none of my accounts have either of those in their names, leaving me unable to test that part.  I also spaced your script out to make it easier to read.  

I hope that does it, but if not let me know.

-- BDF


'*******************************
'Begin Script
'*******************************


' Created for AD User Report
' This script exports the following fields
' samaccountname, displayname, description, streetaddress, I,
' st, postalcode, mail, mailnickname, homedb, homedrive, homedirectory,
' whencreated, employeeid extensionattribute4, manager, telephone
' Each line is tab delimited to be used for the XLS format


'Global variables
Dim oContainer
Dim OutPutFile
Dim FileSystem

'Initialize global variables
Set FileSystem = WScript.CreateObject("Scripting.FileSystemObject")
Set OutPutFile = FileSystem.CreateTextFile("CORP AD Monthly User Report.xls", True)

OutPutFile.WriteLine     "Username" & vbtab & "Description" & vbtab & "Street Address" & vbtab & "City" & vbtab & "State" & vbtab & "Zip Code" & vbtab & "Display Name" & vbtab & "Email Address" & vbtab & "Email Alias" & vbtab & "Exchange Home Server" & vbtab & "Home Drive" & vbtab & "Home Directory" & vbtab & "Created" & vbtab & "EmployeeID" & vbtab & "extensionAttribute4" & vbtab & "Manager" & vbtab & "Telephone Number"

'Set oContainer = GetObject("LDAP://OU=Users,OU=Accounts,DC=Corp,DC=Contoso,DC=com")
Set oContainer = GetObject("LDAP://OU=FM,OU=Region,DC=sero,DC=fns,DC=pri")

'Enumerate Container
EnumerateUsers oContainer

'Clean up
OutPutFile.Close
Set FileSystem = Nothing
Set oContainer = Nothing

WScript.Echo "Finished"
WScript.Quit(0)


Sub EnumerateUsers(oCont)
      Dim oUser, bUser
      For Each oUser In oCont
            bUser = False
            Select Case LCase(oUser.Class)
                  Case "user"

                        ' User Account Login
                        If Not IsEmpty(oUser.sAMAccountName) Then
                              If Not Instr(1,LCase(oUser.sAMAccountName),"svc",vbTextCompare) > 0 Then
                                    If Not Instr(1,LCase(oUser.sAMAccountName),"admin",vbTextCompare) > 0 Then
                                          OutPutFile.Write oUser.sAMAccountName & vbtab
                                          bUser = True
                                    Else
                                          Exit For
                                    End If
                              Else
                                    Exit For
                              End If
                        End If

                        ' User Account Description
                        OutPutFile.Write oUser.description & vbtab
      
                        ' User Account Address Information
                        ' First line is to search and replace returns with a space

                        strAddress = Replace(oUser.streetAddress, vbCrLf, " ")
                      OutPutFile.Write strAddress & vbtab
                      OutPutFile.Write oUser.L & vbtab
                      OutPutFile.Write oUser.st & vbtab
                      OutPutFile.Write oUser.postalCode & vbtab
      
                        ' User Account Display Name
                        OutPutFile.Write oUser.displayName & vbtab
            
                        'Email Information
                        OutPutFile.Write oUser.mail & vbtab
                        OutPutFile.Write oUser.mailNickname & vbtab
                        ' Distinguished Path of Mailbox Home Server
                        OutPutFile.Write oUser.msExchHomeServerName & vbtab

                        ' User Home Directory Information
                        OutPutFile.Write oUser.homedrive & vbtab
                        OutPutFile.Write oUser.homeDirectory & vbtab

                        ' User Account General
                        OutPutFile.Write oUser.whencreated & vbtab
                        OutPutFile.Write oUser.employeeID & vbtab
                        OutPutFile.Write oUser.extensionAttribute4 & vbtab
                        OutPutFile.Write oUser.manager & vbtab
      
                        ' User Account Phone Number
                        OutPutFile.Write oUser.telephoneNumber & vbtab
            
                  Case "organizationalunit", "container"
            
                        EnumerateUsers oUser
            End Select
            If bUser Then
                  OutPutFile.WriteLine
            End If
      Next
End Sub
0
 
LVL 12

Author Comment

by:BNettles73
ID: 12524713

It works great concerning skipping over the security accounts, but the first time it hits an account with admin it ends. I have about 500 security groups before the A's start - about 47 users down an account with admin appears ... the script ends at this particular user.

Thanks again ... I really appreciate you helping me with this!

Brian
0
 
LVL 76

Accepted Solution

by:
David Lee earned 2000 total points
ID: 12524887
Oops.  I see the problem.  That was the part I couldn't test.  Try this instead.


'*******************************
'Begin Script
'*******************************


' Created for AD User Report
' This script exports the following fields
' samaccountname, displayname, description, streetaddress, I,
' st, postalcode, mail, mailnickname, homedb, homedrive, homedirectory,
' whencreated, employeeid extensionattribute4, manager, telephone
' Each line is tab delimited to be used for the XLS format


'Global variables
Dim oContainer
Dim OutPutFile
Dim FileSystem

'Initialize global variables
Set FileSystem = WScript.CreateObject("Scripting.FileSystemObject")
Set OutPutFile = FileSystem.CreateTextFile("CORP AD Monthly User Report.xls", True)

OutPutFile.WriteLine     "Username" & vbtab & "Description" & vbtab & "Street Address" & vbtab & "City" & vbtab & "State" & vbtab & "Zip Code" & vbtab & "Display Name" & vbtab & "Email Address" & vbtab & "Email Alias" & vbtab & "Exchange Home Server" & vbtab & "Home Drive" & vbtab & "Home Directory" & vbtab & "Created" & vbtab & "EmployeeID" & vbtab & "extensionAttribute4" & vbtab & "Manager" & vbtab & "Telephone Number"

'Set oContainer = GetObject("LDAP://OU=Users,OU=Accounts,DC=Corp,DC=Contoso,DC=com")
Set oContainer = GetObject("LDAP://OU=FM,OU=Region,DC=sero,DC=fns,DC=pri")

'Enumerate Container
EnumerateUsers oContainer

'Clean up
OutPutFile.Close
Set FileSystem = Nothing
Set oContainer = Nothing

WScript.Echo "Finished"
WScript.Quit(0)


Sub EnumerateUsers(oCont)
      Dim oUser, bUser
      For Each oUser In oCont
            bUser = False
            Select Case LCase(oUser.Class)
                  Case "user"

                        ' User Account Login
                        If Not IsEmpty(oUser.sAMAccountName) Then
                              If Not Instr(1,LCase(oUser.sAMAccountName),"svc",vbTextCompare) > 0 Then
                                    If Not Instr(1,LCase(oUser.sAMAccountName),"admin",vbTextCompare) > 0 Then
                                          bUser = True
                                          OutPutFile.Write oUser.sAMAccountName & vbtab
                                          ' User Account Description
                                          OutPutFile.Write oUser.description & vbtab
      
                                          ' User Account Address Information
                                          ' First line is to search and replace returns with a space

                                          strAddress = Replace(oUser.streetAddress, vbCrLf, " ")
                                        OutPutFile.Write strAddress & vbtab
                                        OutPutFile.Write oUser.L & vbtab
                                        OutPutFile.Write oUser.st & vbtab
                                        OutPutFile.Write oUser.postalCode & vbtab
      
                                          ' User Account Display Name
                                          OutPutFile.Write oUser.displayName & vbtab
            
                                          'Email Information
                                          OutPutFile.Write oUser.mail & vbtab
                                          OutPutFile.Write oUser.mailNickname & vbtab
                                          ' Distinguished Path of Mailbox Home Server
                                          OutPutFile.Write oUser.msExchHomeServerName & vbtab

                                          ' User Home Directory Information
                                          OutPutFile.Write oUser.homedrive & vbtab
                                          OutPutFile.Write oUser.homeDirectory & vbtab

                                          ' User Account General
                                          OutPutFile.Write oUser.whencreated & vbtab
                                          OutPutFile.Write oUser.employeeID & vbtab
                                          OutPutFile.Write oUser.extensionAttribute4 & vbtab
                                          OutPutFile.Write oUser.manager & vbtab
      
                                          ' User Account Phone Number
                                          OutPutFile.Write oUser.telephoneNumber & vbtab
                                    End If
                              End If
                        End If
            

                  Case "organizationalunit", "container"
            
                        EnumerateUsers oUser
            End Select
            If bUser Then
                  OutPutFile.WriteLine
            End If
      Next
End Sub
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Author Comment

by:BNettles73
ID: 12525377

Works great ... you've been extremely helpful! Thanks!


The only things I wouldn't mind doing, but aren't really that big of a deal are -

1. Filter disabled accounts
2. Compress the file post execution and email (I'm pretty sure I can acccomplish this with a separate batch file)

Now I can set this script to run on one of my batch servers and generate monthly reports for our controller =)


Thanks again BDF!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 12525892
No problem, Brian.  Glad I could help.  I'll look into filtering disabled accounts.  Compressing and emailing the results should be pretty easy to do from a batch file.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 12526415
To eliminate disabled accounts, try swapping this line of code

    If Not IsEmpty(oUser.sAMAccountName) Then

for this

    If (Not IsEmpty(oUser.sAMAccountName)) And (Not oUser.accountDisabled) Then
0
 
LVL 12

Author Comment

by:BNettles73
ID: 12527249

It worked! Thanks again =)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

850 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