Solved

VB Script:  Pull Active Users and Verify off Another Text File

Posted on 2011-02-18
19
432 Views
Last Modified: 2012-05-11
Hi Experts,

     I'm curious, if one of you guys/gals has a script that will go out to Active Directory and pull the sAMAccountNames of all Active Accoutns in two OU's within our Active Directory environment.  The OU paths would be as such (I can fill those in):  UserAccounts\xxx\xxx and UserAccounts\XXX OU trees.  I'd then like to bounce the list of Active Users in AD against a a simple Master Text File that contains just the sAMAccountNames of Unix users.   What the Script doesn't see in the Master Text File it would then note that in an output file (.csv file)  Any help you can provide with this is GREATLY APPRECIATED!!!

 Example
0
Comment
Question by:itsmevic
[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
  • 8
  • 7
  • 3
  • +1
19 Comments
 
LVL 9

Expert Comment

by:Tomas Valenta
ID: 34924093
I have script which create excel file with list of user values from OU from domain com.domain.ad. This could be the start:


Const ADS_SCOPE_SUBTREE = 2
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Last name"
objExcel.Cells(1, 2).Value = "First name"
objExcel.Cells(1, 3).Value = "Department"
objExcel.Cells(1, 4).Value = "Mobile"
objExcel.Cells(1, 5).Value = "Logon name"

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
objCommand.CommandText = _
    "SELECT givenName, SN, department, telephoneNumber, mobile, sAMAccountName FROM " _
        & "'LDAP://OU=Users,OU=PRG,dc=com,dc=domain,dc=ad' WHERE " _
            & "objectCategory='user'"  
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 2

Do Until objRecordSet.EOF
    objExcel.Cells(x, 1).Value = objRecordSet.Fields("SN").Value
    objExcel.Cells(x, 2).Value = objRecordSet.Fields("givenName").Value
    objExcel.Cells(x, 3).Value = objRecordSet.Fields("department").Value
    objExcel.Cells(x, 4).Value = objRecordSet.Fields("mobile").Value
    objExcel.Cells(x, 5).Value = objRecordSet.Fields("sAMAccountName").Value
    x = x + 1
    objRecordSet.MoveNext
Loop

Set objRange = objExcel.Range("A1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("C1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("D1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("A1").SpecialCells(11)
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")

Open in new window

0
 
LVL 12

Expert Comment

by:prashanthd
ID: 34924128
Hi

Try this code. Run as cscript vbsfile> output.txt, output.txt will have the required info
' Get OU
'
strOU ="OU=UserAccounts,DC=domain,DC=com" 'UserAccounts\xxx\xxx 
strOU2="OU=UserAccounts,DC=domain,DC=com"'UserAccounts\XXX
inputfile="c:\master.txt" 'unix user list

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(inputfile, 1)

strreadall=objtextfile.ReadAll

' Create connection to AD
'
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"

' Create command
'
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000

' Execute command to get all users in First OU
'
objCommand.CommandText = _
  "<LDAP://" & strOU & ">;" & _
  "(&(objectclass=user)(objectcategory=person));" & _
  "sAMAccountName;subtree"
Set objRecordSet = objCommand.Execute

' Show info for each user in OU
'
Do Until objRecordSet.EOF

  'WScript.Echo objRecordSet.Fields("sAMAccountName").Value
  samname=objRecordSet.Fields("sAMAccountName").Value

	If InStr(strreadall,samname)=0 Then
		WScript.Echo samname & " - Not in Unix Master List"
	End If
  ' Move to the next user
  '
  objRecordSet.MoveNext

Loop

objRecordSet.Close

' Execute command to get all users in Second OU
'
objCommand.CommandText = _
  "<LDAP://" & strOU2 & ">;" & _
  "(&(objectclass=user)(objectcategory=person));" & _
  "sAMAccountName;subtree"
Set objRecordSet = objCommand.Execute

' Show info for each user in OU
'
Do Until objRecordSet.EOF

  'WScript.Echo objRecordSet.Fields("sAMAccountName").Value
	samname=objRecordSet.Fields("sAMAccountName").Value
	
	If InStr(strreadall,samname)=0 Then
		WScript.Echo samname & " - Not in Unix Master List"
	End If
  ' Move to the next user
  '
  objRecordSet.MoveNext

Loop

' Clean up
'
objRecordSet.Close


Set objRecordSet = Nothing
Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing

Open in new window

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34924936
Hi there,

This should get you enabled accounts only, compared to the master list, and put AD accounts that don't exist in the master list, into Results.txt.

Regards,

Rob.
strMasterList = "MasterList.txt"
strResults = "UsersNotInMasterList.txt"

arrOUs = Array( _
	"OU=UserAccounts,OU=Office1,DC=domain,DC=com", _
	"OU=UserAccounts,OU=Office2,DC=domain,DC=com" _
	)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1
Set objMaster = CreateObject("Scripting.Dictionary")
Set objMasterFile = objFSO.OpenTextFile(strMasterList, ForReading, False)
While Not objMasterFile.AtEndOfStream
	strAccount = LCase(objMasterFile.ReadLine)
	If objMaster.Exists(strAccount) = False Then objMaster.Add strAccount, 0
Wend
objMasterFile.Close

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000

Set objResults = objFSO.CreateTextFile(strResults, True)
For Each strOU In arrOUs
	objCommand.CommandText = "<LDAP://" & strOU & ">; (&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)));adsPath,samAccountName;Subtree"  
	Set objRecordSet = objCommand.Execute
	Do Until objRecordSet.EOF
		strSamAccountName = objRecordSet.Fields("samAccountName").Value
		If objMaster.Exists(LCase(strSamAccountName)) = False Then objResults.WriteLine strSamAccountName
	    objRecordSet.MoveNext
	Loop
Next
objResults.Close

MsgBox "Finished. Please see " & strResults

Open in new window

0
Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 

Author Comment

by:itsmevic
ID: 34928440
Hi Rob,

     Great to hear from you again, hope you are well.   I ran your script above and for some reason it's erroring out on me:

************************************************************************************************
Script:  C:\Users\xxxxx\Desktop\Unix-UserMappingToAD.vbs
Line:    30
Char:   2
Error:  There is no such object on the server.

Code:  80040E37
Source:  Active Directory
************************************************************************************************

Again, thanks for chiming in on this.  
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34929720
Hi, you'll need to make sure these are correct:
"OU=UserAccounts,OU=Office1,DC=domain,DC=com"

remember that the OU's are specified backwards, so
domain.com\MySites\MainOffice\Users

is referenced like this
"OU=Users,OU=MainOffice,OU=MySites,DC=Domain,DC=Com"

Regards,

Rob.
0
 

Author Comment

by:itsmevic
ID: 34930442
Hi Rob,

   Double check the path and set those appropriately.  However still getting the same error on line 30 w/character 2.  I work in Notepad++, not sure if your using that or not....  What's interesting is I ran prashanthd code as well and got the same exact error.  
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 34931017
Try this, it doesn't require the Domain's DC parts, so only specify the full OU path, and see if that works.  One note....if you're referencing the default Computers or Users OU, then you need to specify CN=Users, and not OU=Users.  For some reason, the default objects are "containers" and not "organizationalUnits".

Regards,

Rob.
strMasterList = "MasterList.txt"
strResults = "UsersNotInMasterList.txt"

arrOUs = Array( _
	"OU=UserAccounts,OU=Office1,", _
	"OU=UserAccounts,OU=Office2," _
	)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1
Set objMaster = CreateObject("Scripting.Dictionary")
Set objMasterFile = objFSO.OpenTextFile(strMasterList, ForReading, False)
While Not objMasterFile.AtEndOfStream
	strAccount = LCase(objMasterFile.ReadLine)
	If objMaster.Exists(strAccount) = False Then objMaster.Add strAccount, 0
Wend
objMasterFile.Close

Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("defaultNamingContext")
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000

Set objResults = objFSO.CreateTextFile(strResults, True)
For Each strOU In arrOUs
	If Right(strOU, 1) <> "," Then strOU = strOU & ","
	strCommandText = "<LDAP://" & strOU & strDomain & ">; (&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)));adsPath,samAccountName;Subtree"  
	MsgBox "Command to execute: " & VbCrLf & strCommandText
	objCommand.CommandText = strCommandText
	Set objRecordSet = objCommand.Execute
	Do Until objRecordSet.EOF
		strSamAccountName = objRecordSet.Fields("samAccountName").Value
		If objMaster.Exists(LCase(strSamAccountName)) = False Then objResults.WriteLine strSamAccountName
	    objRecordSet.MoveNext
	Loop
Next
objResults.Close

MsgBox "Finished. Please see " & strResults

Open in new window

0
 
LVL 12

Expert Comment

by:prashanthd
ID: 34941835
Try this
Dim NextTime As Date = Now        ' Current date and time.
NextTime = NextTime.AddDays(1)  ' Increment/Decrement

Open in new window

0
 
LVL 12

Expert Comment

by:prashanthd
ID: 34941837
Please ignore above wrong post
0
 

Author Comment

by:itsmevic
ID: 34945918
Hi Rob,

     Your latest script provided did bring back a populated results file.  The Master text file contains around 4000 sAMAccountNames, the file produced by the script generated around 49,000 sAMAccountNames.  I'm thinking this has something to do with what you mentioned about changing the str path from  OU=Users to CN=Users.    Your correct, I will be only searching through 2 OU's in my Active Directory tree, not the entire tree.   Can you tell me where I need to change OU= to CN=  ?   I think once we do this it will generate accurate numbers in comparison to the Master text file.  Again, thank you so much for your help.  You are appreciated!  

I'm also going to try prashanthd code again, but remove the DC part to see if that helps too...
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34947799
Hi, glad we got something.

I assume you only used
"CN=Users,"

in the array then?

Using CN= over OU= depends on how your user accounts are structured in their OUs, and ONLY applies to the default Users container.

For example, if you have the users separated like so:
domain.com/Users/Office1/UserAccounts

then you would use:
"OU=UserAccounts,OU=Office1,CN=Users,"

because the Users container from the root of the domain is the default one, and therefore is a CN, not an OU.

However, if your accounts were in a path that complete consisted of user created OUs, like:
domain.com/Office1/UserAccounts

then you would use:
"OU=UserAccounts,OU=Office1,"

because they are all manually created organizational units.

Hope that helps.

Regards,

Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34947822
If you're still struggling with how to form the adsPath, then what you can do is place a test user account in the OU you need to know the path of, log in as that user, then run the following code:
 
Set objADSysInfo = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objADSysInfo.UserName)
strOU = Left(Mid(objUser.Parent, 8), InStr(Mid(objUser.Parent, 8), ",DC="))
InputBox "Your account is in the following OU:", "OU Path", strOU

Open in new window


You will then see an InputBox that has the OU text, exactly as you need to put it in the script.

Regards,

Rob.
0
 

Author Comment

by:itsmevic
ID: 34949085
Sounds like a plan.  I'll run it tomorrow and let you know.  : )
0
 

Author Comment

by:itsmevic
ID: 34956686
Hi Rob, Yep I think everything is pathed in the OU format rather than the CN.  I also run Active Directory Explorer on my end and drilled down to those two OU's I'm needing and in the path field at the top, it did NOT indicate anything i.e. CN=, it was all OU=,OU=,OU= so I think we are good to go with that.
 
What I did differently, because it was pulling so many more accounts than what was shown in the Master text file was included the other sub OU's under the primary OU in the array listing.  That helped reduce the number of accounts it found for some reason.  Unfortunately, because it's pulling  all ACTIVE ACCOUNTs it's still pulling way more than what is listed in the Master text file.  The other OU's look great it's the very last OU i.e. NORTHWEST that it's finding around 9,200 accounts.  The Master text file only contains 4,900 or so accounts.    

The OU structure the script is presently scanning is as such.  All the hits look good until you get to the last OU where it is pulling 9200 accounts, which I assume are those it does not see in the Master text file as well as ALL Active Accounts in that OU.  Perhaps we just need a way of telling the script, yes do pull ACTIVE ACCOUNTS but only pull those ACTIVE ACCOUNTS THAT it does not see in the Master Text file.  I hope that makes sense.

                                                                                         Accts it does not see in Master Txt File
arrOUs = Array(
OU=NORTH,OU=ABC,OU=TEX,OU=UserAccounts,", _                     11
"OU=SOUTH,OU=ABC,OU=TEX,OU=UserAccounts,", _                     2
"OU=EAST,OU=ABC,OU=TEX,OU=UserAccounts,", _                      81
"OU=WEST,OU=ABC,OU=TEX,OU=UserAccounts,", _                     70
"OU=SOUTHWEST,OU=ABC,OU=TEX,OU=UserAccounts,", _           0
"OU=NORTHEAST,OU=ABC,OU=TEX,OU=UserAccounts,", _          426
"OU=NORTHWEST,OU=ABC,OU=TEX,OU=UserAccounts,", _             7
"OU=SOUTHEAST,OU=ABC,OU=TEX,OU=UserAccounts,", _            53
"OU=NORTHEAST,OU=ABC,OU=TEX,OU=UserAccounts," _          9200
)
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34957686
I'm a bit confused as to why it's doing this.  Here's what it should be doing:
1) Read the mast list accounts into the dictionary object called objMaster
2) Go through each OU in arrOUs
3) For each OU, get the list of ENABLED user accounts only
4) Check if each user is in the objMaster dictionary object
5) If it does exist, do nothing, if the account is NOT in the master list, output it to file

That's it.  However, maybe you have further sub OUs in the NORTHEAST OU that you do not want to check?  If that's the case, then change:
      strCommandText = "<LDAP://" & strOU & strDomain & ">; (&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)));adsPath,samAccountName;Subtree"  

to:
      strCommandText = "<LDAP://" & strOU & strDomain & ">; (&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)));adsPath,samAccountName;OneLevel"

Regards,

Rob.
0
 

Author Comment

by:itsmevic
ID: 34958666
Will do, I'll check that and let you know Rob.  Thank you again!  
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35015636
Hi, did you manage to get back to try this out?

Rob.
0
 

Author Closing Comment

by:itsmevic
ID: 35021219
Thanks! Great input!
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35022344
No problem. Thanks for the grade.

Rob.
0

Featured Post

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

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

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

705 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