Solved

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

Posted on 2011-02-18
19
424 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
  • 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

707 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

11 Experts available now in Live!

Get 1:1 Help Now