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

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")

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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.  

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.

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.  

Try this

Dim NextTime As Date = Now        ' Current date and time.
NextTime = NextTime.AddDays(1)  ' Increment/Decrement

Select allOpen in new window

Please ignore above wrong post

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...

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.

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

Select allOpen 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.

Sounds like a plan.  I'll run it tomorrow and let you know.  : )

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
)

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.

Will do, I'll check that and let you know Rob.  Thank you again!  

Hi, did you manage to get back to try this out?

Rob.

Thanks! Great input!

No problem. Thanks for the grade.

Rob.