Need help with Active Directory Export Script



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
LVL 12
BNettles73Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David LeeCommented:
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
BNettles73Author Commented:

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
David LeeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BNettles73Author Commented:

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
David LeeCommented:
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
David LeeCommented:
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
BNettles73Author Commented:

It worked! Thanks again =)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.