Find Users in AD with Excel and VBScript

[itsmaky]

Hello,

I've got a list of first and last names in Excel for which I need to find Active Directory usernames. There are several hundred, so searching manually isn't feasible. I'd prefer to use VBscript instead. I've been able to find some scripts on the web which will query AD based on givenName and sn attributes, but I'm just not certain how to tie those scripts into a script which will go down the list in Excel and output usernames based on first/last names.


Normally I'm able to frankenstein together vbscripts to do just about anything I need as a MS network admin, but this one has me stumped.

Any help would be greatly appreciated!
Kevin

[chris128]

Did you get this working yet or are you still looking for a solution?
Also, are you wanting a VBscript, VBA or VB.NET example?

[itsmaky]

I haven't been able to find a solution yet. I'm trying to do this with a VBscript. So far, the closest i've found is the script below, , but it doesn't do exactly what I need.

_________________________________________________________________________
Query AD for user using first and last name

Const ADS_SCOPE_SUBTREE = 2

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
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\test.xls")
objExcel.Visible = True

i = 1

Do Until objExcel.Cells(i, 1).Value = ""
strName = objExcel.Cells(i,1)

strFirstname = left(strName, instr(strName, " ")-1)
strSurname = mid(strName, instr(strName, " ")+1, 1)

objCommand.CommandText = _
"SELECT sAMAccountName FROM 'LDAP://dc=dot,dc=state,dc=oh,dc=us' WHERE " _
& "givenName='" & strName & "' AND sn='" & strSurnameInitial & "'"
Set objRecordSet = objCommand.Execute

If objRecordset.RecordCount = 1 Then
objExcel.Cells(i,2) = "Found"
Else
objExcel.Cells(i,2) = "Not found"
End If

i = i + 1
objRecordset.Close
Loop

Thanks for the reply!
Kevin

[chris128]

Well can you explain in more detail exactly what you're trying to do? Are you saying that you want to run through this list of first and last names in Excel and check to see if an AD account exists for that user? If so then you'll have to explain to me in what way that script you posted doesnt do what you want.

[itsmaky]

I've got an Excel spreadsheet with users' first names in column A and last name in column B. I need to write a VBscript to use this spreadsheet in order to query Active Directory for usernames and output those to column C.

The script that I posted is the closest I could find to what I'm looking for but here are the problems with it:

It seems to append the first name initial onto the last name. i.e. Bob Smith becomes bsmith. Then it searches for that username and outputs "Found" or "Not Found" to column C.

This won't work for my environment or situation. Firstly, we're a large organization so we might have four Bob Smiths working for us. Therefore, bsmith would only account for one of their AD usernames. Secondly, the goal is to find AD logon names (usernames) based on the user's first and last name then output the logon name to column C, not just Found or Not Found.

I got a list of 2000 first and last names, and they said, "add these users to this group", so I have no idea what their AD usernames are. That's what I'm trying to find.

Thanks,
Kevin

[chris128]

In the "Add Group" dialog box in AD you dont need to type a username, you can just type the user's full name and it will ask you which one you want to choose if there is more than one.
I guess that might not help if you have 2000 as I assume you are planning to add them all to this group via script instead of manually?

Anyway, if you have 4 Bob Smith's then how is the program going to know which one to output into column C if it is just searching the First and last name fields of the AD account?

[itsmaky]

I'd be using a script to add them to the group. Searching manually really isn't an option, but you raise a good question about finding multiple accounts. I think much more often than having two Bob Smiths, we have a Bill Smith and a Bob Smith, which would result in usernames bsmith and
bsmith2. I could deal with duplicate identical names manually, I don't think there are too many.
Thanks,
Kevin

[chris128]

OK well I could put something together for you in VB.NET that pulls a list of names from a text file and does what you want, would that be any use or does it need to be vbscript? (you might be able to work out how to do the same thing in vbscript from my .NET example anyway)

[itsmaky]

.NET would be fine, I am eternally indebted to you for your help!
Thanks,
Kevin

[chris128]

Not yet your not :P I'll be at home in about an hour and a half (damn rush hour traffic) so I'll try and build something then

[chris128]

Havent forgotten about this... its just harder than I thought I'll try get it working tonight though and post up the working code

[chris128]

OK so I finally got it working. I've been testing it over a VPN so its been fairly slow but not too bad. So I assume running on the LAN it wont take too long but cant make any promises (especially if you have over 2000 users for it to query).
This is probably going to seem quite long winded and complicated but trust me its not, its just spaced out and full of explanations

I decided to use an Excel file like you so that it was easier for you to use, however this does mean that to be able to use my code your going to need to install the Microsoft Office 2003 PIAs (pretty small install) from here: http://www.microsoft.com/downloads/d...displaylang=en

My spreadsheet looks like this:


Hopefully its pretty similar to yours.
Anyway, once you've installed the PIAs, just open up a new project in VB 2008/2005 and leave the initial form name as Form1 and add a reference to System.DirectoryServices and Microsoft Excel 11.0 Object Library then just copy and paste this into your form code (overwritting anything thats already in there).

Read the explanation below the code before using this code on your spreadsheet!

Imports System.DirectoryServices
Imports Microsoft.Office.Interop
 
Public Class Form1
 
    Private Sub Gobtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Gobtn.Click
        Try
            Dim RootEntry As New DirectoryEntry("LDAP://OU=UsersOU,dc=yourdomain,dc=local", "domainadmin", "password") '### CHANGE ###
            Dim AccountSearcher As New DirectorySearcher
            Dim UserList As New Collections.ArrayList
            Dim excelapp As New Excel.Application
            Dim UserSheet As Excel.Worksheet = excelapp.Workbooks.Open("C:\Userlist.xls").Worksheets("Sheet1")
            Dim Counter As Integer = 0
            Dim Foundlist As New Collections.ArrayList
 
 
            For i As Integer = 3 To 8  '### CHANGE ###
                UserList.Add(UserSheet.Cells(i, "B").Value & UserSheet.Cells(i, "C").Value) '### CHANGE ###
            Next
 
            With AccountSearcher
                .SearchRoot = RootEntry
                .Filter = "(ObjectClass=User)"
                .PropertiesToLoad.Add("sAMAccountname")
                .PropertiesToLoad.Add("givenName")
                .PropertiesToLoad.Add("sn")
                .PageSize = 1000
            End With
 
            'Loop through each account found by the ADsearcher
            For Each account As SearchResult In AccountSearcher.FindAll
                'Store the current first and last name so we dont have to keep fetching them
                Dim CurrentGN As String = account.GetDirectoryEntry.Properties("givenName").Value
                Dim CurrentSN As String = account.GetDirectoryEntry.Properties("sn").Value
                'Check to see if first and last name attributes are emtpy
                If CurrentGN <> Nothing AndAlso CurrentSN <> Nothing Then
                    'If the first and last name are not empty then we store the username
                    Dim CurrentSAM As String = account.GetDirectoryEntry.Properties("sAMAccountName").Value
                    'Loop through every name in the excel file and compare it with the current account's first and last name
                    For i As Integer = 0 To UserList.Count - 1
                        If UserList(i).ToString = (CurrentGN & CurrentSN) Then
                            Counter = i + 3 '### CHANGE ###
                            If Foundlist.Contains(CurrentGN & CurrentSN) Then
                                'If we already searched for this first name and last name then highlight the cell yellow
                                UserSheet.Cells(Counter, "D").Interior.ColorIndex = 36  '### CHANGE ###
                            End If
                            UserSheet.Cells(Counter, "D").Value = CurrentSAM  '### CHANGE ###
                            Foundlist.Add(CurrentGN & CurrentSN)
                            RichTextBox1.AppendText("Found Username " & CurrentSAM & " for " & CurrentGN & " " & CurrentSN & vbNewLine)
                            Application.DoEvents()
                        End If
                    Next
                End If
            Next
 
            'Show alert when finished looping through users
            MessageBox.Show("Processing Complete", "Complete", MessageBoxButtons.OK, MessageBoxIcon.Information)
 
            'Save and close Excel file
            excelapp.Workbooks("Userlist.xls").Save()  '### CHANGE ###
            excelapp.Workbooks.Close()
 
            'Alert if error occurrs
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Oh Dear...", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            'Clean up by killing all Excel processes
            For Each excelproc As Process In Process.GetProcessesByName("EXCEL")
                excelproc.Kill()
            Next
        End Try
    End Sub
 
End Class

As you can see I've added a few comments in there but not really enough to fully explain whats going on.
Basically, if you arent that bothered about understanding it and just want it to work then the bits you need to change have all got a comment on that line saying '### CHANGE ### so hopefully you can spot them.
Basically, you need to change the domain and OU its looking in and the location/name of the Excel file. These should be fairly obvious to you, but the bits that might not be so obvious are the counter values. I'm sure there will be a better way of doing it but all I am doing in this line:

For i As Integer = 3 To 8  '### CHANGE ###

is saying loop through rows 3 to 8 in the excel document. You can see the column letters in the line below that one, so obviously change both of these to suit your spreadsheet.
Essentially all you need to do is go through the code and look for any lines with ###CHANGE### at the end of them and alter the column name or row numbers to reflect what is in your spreadsheet.

This line:

Counter = i + 3 '### CHANGE ###

just needs changing to i + whatever number of rows down from 0 your first user's name is. As you can see from my spreadsheet screenshot, my names start on row 3, hence the i + 3.

Make a backup of your spreadsheet first just in case you miss one of the values off or get it wrong etc.

As we mentioned earlier, there is no way for the program to determine which names match which username in the event of two names being the same so the best I could do was alert you to which names have been querried more than once so that you know one of them is incorrect. I do this in this line:

'If we already searched for this first name and last name then highlight the cell yellow
UserSheet.Cells(Counter, "D").Interior.ColorIndex = 36  '### CHANGE ###

So any usernames that might not be correct will be highlghted in yellow. Any usernames that could not be found will just be left blank. You can see this in my final output spreadsheet here:



Oh and you will also need a RichTextBox control on your form named RichTextBox1 as I use that to output the progress of the process (as you can see in the screenshot further down).


Make sure you havent got any other excel documents open when you run this because at the end it will terminate all EXCEL.EXE processes

I designed the GUI like this just so it was usable:



Obviously I've blanked out the real names and usernames but you get the idea. The progress log gets updated each time a new username is added to the spreadsheet, I just thought it would be a good idea so that you could see how far it had got or if it was going wrong etc.

I hope that helps! It better do after it took me nearly 2 hours :P haha

Oh yeah, its not designed to be run more than once without being closed and opened again - Yeah I know thats pretty naff but its getting late and I figured you could just implement some "clean up" code yourself

PS anyone else feel free to correct my code if I have done anything wrong or if you have any better ideas but it all worked fine when I tested it so hopefully its all good.

Chris

[chris128]

Get chance to try it out yet?

[chris128]

Ah well I'm guessing you've either already got it sorted or have just forgotten about this thread. Hope my code helps someone else out in the future.

[slayerx]

This is exactly what I am looking for. Thank you for all your hard work. I do have some questions though.

how do you create the log events?
I am running it and i can see it talk to excel, but it does't write back to it. I cant tell where the problem lies, and the log would be very helpful.

[chris128]

What exactly do you mean by "log events" ? Do you get any exceptions thrown or does it just not do anything? Also, what do you see in your richtextbox that the progress gets output to?

Oh and you have changed all of the lines of my code that have '###CHANGE### atfer them to suit your file names etc haven't you?

[crimsoneye]

Hi Chris,

You did a hell of a good job it seems but I am getting the following error when I check the code in VB 2008:

"Error 1 Handles clause requires a WithEvents variable defined in the containing type or one of its base types. C:\AD.usernames\Form1.vb 6 97 AD.usernames"

This refers to line 6 and the Handles clause in it : "Private Sub Gobtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Gobtn.Click"

I am not a programmer so I am way off my competence here. I am just an AD admin and want to extract some specific usernames from my directory. Can you please help out?

I am also getting Richtextbox1 name not defined? Where do I define that. I have modified all other parts in the script that needed modification.

Thanks in advance!

[crimsoneye]

Okay,

Fixed the first error with adding a declaration before the Private sub :

"Dim WithEvents Gobtn As New Button"

Only the Richtextbox1 error left now.

Cheers

[chris128]

It certainly shows that you are not a programmer
Take out that Dim WithEvents etc etc line you just added in. What you need to do (to fix both errors) is drag a Button and a RichTextBox from the toolbox in visual studio onto your form. Then set the button name to GoBtn.

[crimsoneye]

Thanks for that! I know I am lame so - sorry about the stupid question. And thanks for the advice! I am trying this out and will update with the result

[chris128]

No worries, hope it works for you

[crimsoneye]

Hi again, another obstacle... maybe a simple one but I am not sure what is wrong:

A first chance exception of type 'System.DirectoryServices.DirectoryServicesCOMException' occurred in System.DirectoryServices.dll

That's the error msg. saying an invalid syntax has been specified and then the above. I think it may have to do with the authenticaton to the domain. I can run AD from here currently and I am hooked up to the Domain. Do I have to set up a connection in any other way to query LDAP apart from the 2 refs in the scrpit?

I have put the root of the domain only ("LDAP://dc=domain,dc=com,username,password") do I have to specify an OU as well or I can just put the root in?

Thanks

[chris128]

You have put the speech marks in the wrong place. Everything inside of speech marks is treated as one single string (string basically means text). So you need to change it from this:

("LDAP://dc=domain,dc=com,username,password")

to this:

("LDAP://dc=domain,dc=com","username","password")

[crimsoneye]

All working now! Yipee thanks for all the advice! Great stuff

Take care

[crimsoneye]

Me again,

One more quick question - Can you tell me how to mod the code if it doesn't find nothing under GN and SN to write output : "User account disabled or Not Found" ?

I have several disabled accounts in the directory and it doesn't count them as valid I'd rather have a message there than an empty cell...


thank you!

[chris128]

That would require more than just a very simple change (although it wouldnt be anything massive) and at the moment I havent got time to do it and test it right now I'm afraid.
I would say your best option would be to highlight the column that the usernames end up in and do a Find and Replace

[crimsoneye]

I guess I am being too demanding now Thank you I will follow your advice!

Have a nice one !

[skapadane]

OK so I finally got it working. I've been testing it over a VPN so its been fairly slow but not too bad. So I assume running on the LAN it wont take too long but cant make any promises (especially if you have over 2000 users for it to query).
This is probably going to seem quite long winded and complicated but trust me its not, its just spaced out and full of explanations

I decided to use an Excel file like you so that it was easier for you to use, however this does mean that to be able to use my code your going to need to install the Microsoft Office 2003 PIAs (pretty small install) from here: http://www.microsoft.com/downloads/d...displaylang=en

My spreadsheet looks like this:


Hopefully its pretty similar to yours.
Anyway, once you've installed the PIAs, just open up a new project in VB 2008/2005 and leave the initial form name as Form1 and add a reference to System.DirectoryServices and Microsoft Excel 11.0 Object Library then just copy and paste this into your form code (overwritting anything thats already in there).

Read the explanation below the code before using this code on your spreadsheet!

Imports System.DirectoryServices
Imports Microsoft.Office.Interop
 
Public Class Form1
 
    Private Sub Gobtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Gobtn.Click
        Try
            Dim RootEntry As New DirectoryEntry("LDAP://OU=UsersOU,dc=yourdomain,dc=local", "domainadmin", "password") '### CHANGE ###
            Dim AccountSearcher As New DirectorySearcher
            Dim UserList As New Collections.ArrayList
            Dim excelapp As New Excel.Application
            Dim UserSheet As Excel.Worksheet = excelapp.Workbooks.Open("C:\Userlist.xls").Worksheets("Sheet1")
            Dim Counter As Integer = 0
            Dim Foundlist As New Collections.ArrayList
 
 
            For i As Integer = 3 To 8  '### CHANGE ###
                UserList.Add(UserSheet.Cells(i, "B").Value & UserSheet.Cells(i, "C").Value) '### CHANGE ###
            Next
 
            With AccountSearcher
                .SearchRoot = RootEntry
                .Filter = "(ObjectClass=User)"
                .PropertiesToLoad.Add("sAMAccountname")
                .PropertiesToLoad.Add("givenName")
                .PropertiesToLoad.Add("sn")
                .PageSize = 1000
            End With
 
            'Loop through each account found by the ADsearcher
            For Each account As SearchResult In AccountSearcher.FindAll
                'Store the current first and last name so we dont have to keep fetching them
                Dim CurrentGN As String = account.GetDirectoryEntry.Properties("givenName").Value
                Dim CurrentSN As String = account.GetDirectoryEntry.Properties("sn").Value
                'Check to see if first and last name attributes are emtpy
                If CurrentGN <> Nothing AndAlso CurrentSN <> Nothing Then
                    'If the first and last name are not empty then we store the username
                    Dim CurrentSAM As String = account.GetDirectoryEntry.Properties("sAMAccountName").Value
                    'Loop through every name in the excel file and compare it with the current account's first and last name
                    For i As Integer = 0 To UserList.Count - 1
                        If UserList(i).ToString = (CurrentGN & CurrentSN) Then
                            Counter = i + 3 '### CHANGE ###
                            If Foundlist.Contains(CurrentGN & CurrentSN) Then
                                'If we already searched for this first name and last name then highlight the cell yellow
                                UserSheet.Cells(Counter, "D").Interior.ColorIndex = 36  '### CHANGE ###
                            End If
                            UserSheet.Cells(Counter, "D").Value = CurrentSAM  '### CHANGE ###
                            Foundlist.Add(CurrentGN & CurrentSN)
                            RichTextBox1.AppendText("Found Username " & CurrentSAM & " for " & CurrentGN & " " & CurrentSN & vbNewLine)
                            Application.DoEvents()
                        End If
                    Next
                End If
            Next
 
            'Show alert when finished looping through users
            MessageBox.Show("Processing Complete", "Complete", MessageBoxButtons.OK, MessageBoxIcon.Information)
 
            'Save and close Excel file
            excelapp.Workbooks("Userlist.xls").Save()  '### CHANGE ###
            excelapp.Workbooks.Close()
 
            'Alert if error occurrs
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Oh Dear...", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            'Clean up by killing all Excel processes
            For Each excelproc As Process In Process.GetProcessesByName("EXCEL")
                excelproc.Kill()
            Next
        End Try
    End Sub
 
End Class

As you can see I've added a few comments in there but not really enough to fully explain whats going on.
Basically, if you arent that bothered about understanding it and just want it to work then the bits you need to change have all got a comment on that line saying '### CHANGE ### so hopefully you can spot them.
Basically, you need to change the domain and OU its looking in and the location/name of the Excel file. These should be fairly obvious to you, but the bits that might not be so obvious are the counter values. I'm sure there will be a better way of doing it but all I am doing in this line:

For i As Integer = 3 To 8  '### CHANGE ###

is saying loop through rows 3 to 8 in the excel document. You can see the column letters in the line below that one, so obviously change both of these to suit your spreadsheet.
Essentially all you need to do is go through the code and look for any lines with ###CHANGE### at the end of them and alter the column name or row numbers to reflect what is in your spreadsheet.

This line:

Counter = i + 3 '### CHANGE ###

just needs changing to i + whatever number of rows down from 0 your first user's name is. As you can see from my spreadsheet screenshot, my names start on row 3, hence the i + 3.

Make a backup of your spreadsheet first just in case you miss one of the values off or get it wrong etc.

As we mentioned earlier, there is no way for the program to determine which names match which username in the event of two names being the same so the best I could do was alert you to which names have been querried more than once so that you know one of them is incorrect. I do this in this line:

'If we already searched for this first name and last name then highlight the cell yellow
UserSheet.Cells(Counter, "D").Interior.ColorIndex = 36  '### CHANGE ###

So any usernames that might not be correct will be highlghted in yellow. Any usernames that could not be found will just be left blank. You can see this in my final output spreadsheet here:



Oh and you will also need a RichTextBox control on your form named RichTextBox1 as I use that to output the progress of the process (as you can see in the screenshot further down).


Make sure you havent got any other excel documents open when you run this because at the end it will terminate all EXCEL.EXE processes

I designed the GUI like this just so it was usable:



Obviously I've blanked out the real names and usernames but you get the idea. The progress log gets updated each time a new username is added to the spreadsheet, I just thought it would be a good idea so that you could see how far it had got or if it was going wrong etc.

I hope that helps! It better do after it took me nearly 2 hours :P haha

Oh yeah, its not designed to be run more than once without being closed and opened again - Yeah I know thats pretty naff but its getting late and I figured you could just implement some "clean up" code yourself

PS anyone else feel free to correct my code if I have done anything wrong or if you have any better ideas but it all worked fine when I tested it so hopefully its all good.

Chris

Thanks sir, But i have one doubt that is it vb and vbscripting are same because i am new and only basic knowledge of vbscripting for system administration task . i just started creating script in vbscriping

[chris128]

Yes VB.NET (which is what this is) and vbscript are different things

[Sachin.aiyappa]

Hello,

I've got a list of first and last names in Excel for which I need to find Active Directory usernames. There are several hundred, so searching manually isn't feasible. I'd prefer to use VBscript instead. I've been able to find some scripts on the web which will query AD based on givenName and sn attributes, but I'm just not certain how to tie those scripts into a script which will go down the list in Excel and output usernames based on first/last names.


Normally I'm able to frankenstein together vbscripts to do just about anything I need as a MS network admin, but this one has me stumped.

Any help would be greatly appreciated!
Kevin

Hi All,

I need help in finding some users which i have in the excel spreadsheet using VB Script to find AD users from a excel file which has the display name

I've got a list of first and last names in Excel for which I need to find Active Directory usernames. There are several hundred, so searching manually isn't feasible. I'd prefer to use VBscript instead.

Please Help i am new to VBscripting

[chris128]

Have you actually read this thread? You seem to be asking for the exact same thing that I already provided code for in post #12

Although I did write that code nearly 3 years ago and looking back at it now there is a lot that could be improved!

[Sachin.aiyappa]

Yes i have gone throught this thread ....i belive the above script needs VB (Visual Basic) . is it posible to get it on VB Script , But on my server i dont have excel and VB (Visual Basic) is not installed

I am looking on VB Script to find AD users from a csv or txt file which has the display name ie first name and Lastname.

Read a list of users from a CSV or a txt file , determine if they exist and if they're disabled, then write the result to the spreadsheet

[chris128]

You don't have to run it from a server. Run it from your PC or any other PC that you can install Excel and VB on. If you really need to do it from VBScript rather than a VB.NET program then I'm afraid I can't help you but there are tons of examples on the internet of how to get information from AD via vbscript, here is a good resource: http://www.rlmueller.net/freecode6.htm

[Sachin.aiyappa]

I was able to find some info this works for me ....but i need some more customization as
This VB script works for me. I have 2 things running in my mind. I have the users First and Last names, delimited by a space and comma in my txt file.

1. I want to Specify the file of names in csv format.
or
2. if it can be "Parse First and Last names, delimited by a space and comma"

Also is it possible to do a double search as 1. first name and last name 2. Last name and first name. this is because some times the name entry in the txt file can be last name and first name , or first name and last name.

===============================================================
Option Explicit

Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName
Dim strFile, objFSO, objFile, strFirst, strLast, strLine, arrValues

Const ForReading = 1

' Specify the file of names.
strFile = "c:\user.txt"

' Open file for reading.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFile, ForReading)

' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"

' Comma delimited list of attribute values to retrieve.
strAttributes = "sAMAccountName"

' Read the file.
Do Until objFile.AtEndOfStream
strLine = Trim(objFile.ReadLine)
' Skip blank lines.
If (strLine <> "") Then
' Parse First and Last names, delimited by a space.
arrValues = Split(strLine, " ")
strFirst = arrValues(0)
strLast = arrValues(1)

' Construct filter.
strFilter = "(anr=" & strFirst & " " & strLast & ")"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery

' Run the query.
Set adoRecordset = adoCommand.Execute

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF
' Retrieve values and display.
strName = adoRecordset.Fields("sAMAccountName").Value
Wscript.Echo strFirst & "," & strLast & "," & strName
' Move to the next record in the recordset.
adoRecordset.MoveNext
Loop
adoRecordset.Close
End If
Loop

' Clean up.
objFile.Close
adoConnection.Close

[Gyancher]

Dear all,

My name is Marcel Duran, and I am working on something that looks the same. It reads computer names from an excel file and then populates extra information based on the computer name.

Extra info is: Username, full name, if the computer has a screensaver lock on, ip address, offline / online, OS version, SP OS, Build version and Mac Address.

My problem is that I still need to find a way to extract the full name of the username that is populated in the username field.

Below is the script so far: ( I am just a beginner scripter, so sorry for any bad scripting)

---------------------
<start vbscript>

'==========================================================================
' Computer Information Detection Script
'
' Description:
' Detect is a computer is Online or Offline
' If computer is online, extract IP Number, OS version + SP + Build info
' and current logged on user.
'
' populate File_name.xls from A2 down with computer names to scan
'
' Created by: Marcel Duran.
' on: 04-27-2011
'
' version 1.5: added MAC Address
' version 1.4: added screensaver detection
' version 1.3: second published version
'==========================================================================

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
intRow = 2

Set Fso = CreateObject("Scripting.FileSystemObject")
Set objWorkbook = objExcel.Workbooks.Open("b:File_Name.xls")
Set InputFile = objWorkbook
Do Until objExcel.Cells(intRow,1).Value = ""
strComputer = objExcel.Cells(intRow, 1).Value


objExcel.Cells(1, 1).Value = "Machine Name"
objExcel.Cells(1, 2).Value = "Username"
objExcel.Cells(1, 3).Value = "Full Name"
objExcel.Cells(1, 4).Value = "Computer Locked"
objExcel.Cells(1, 5).Value = "IP Address"
objExcel.Cells(1, 6).Value = "Status"
objExcel.Cells(1, 7).Value = "Microsoft OS"
objExcel.Cells(1, 8).Value = "SP"
objExcel.Cells(1, 9).Value = "Build"
objExcel.Cells(1, 10).Value = "MAC Address"


'==========================================================================
' The following function will resolve a computer name to its ip address
' using WMI and the Win32_PingStatus Class
'==========================================================================

Dim wmiQuery : wmiQuery = "Select * From Win32_PingStatus Where Address = '" & strComputer & "'"

Dim objWMIService : Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Dim objPing : Set objPing = objWMIService.ExecQuery(wmiQuery)
Dim objStatus

For Each objStatus in objPing
If IsNull(objStatus.StatusCode) Or objStatus.Statuscode<>0 Then
objExcel.Cells(intRow, 1).Font.ColorIndex = 3
objExcel.Cells(intRow, 5).Value = "Computer is Unreachable!"
objExcel.Cells(intRow, 6).Font.ColorIndex = 3
objExcel.Cells(intRow, 6).Value = "offline"
Else
objExcel.Cells(intRow, 5).Value = objStatus.ProtocolAddress
objExcel.Cells(intRow, 6).Font.ColorIndex = 4
objExcel.Cells(intRow, 6).Value = "online"
End If

On Error Resume Next


'==========================================================================
' The following function show the Username that is logged on
'==========================================================================

strUser="" 'User with ADMIN rights on remote PC
strUserPwd=""
strDomain="" 'If computer is member of Domain, if computer is in workgroup, keep this variable empty ("")

Set objSWbemLocator = CreateObject("WbemScripting.SWbemLocator")
if strDomain="" then
Set objSWbemServices = objSWbemLocator.ConnectServer(strComputer, "root\cimv2", strUser, strUserPwd)
else
Set objSWbemServices = objSWbemLocator.ConnectServer(strComputer, "root\cimv2", strUser, strUserPwd, "MS_409", "ntlmdomain:" + strDomain)
end if

Set colSwbemObjectSet = objSWbemServices.ExecQuery("Select * From Win32_Process where name='explorer.exe'")

on error resume next

For Each Process in colSwbemObjectSet
If IsNull(objStatus.StatusCode) Or objStatus.Statuscode<>0 Then
objExcel.Cells(intRow, 2).Value = ""
else
lRet = Process.GetOwner(sUser, sDomain)
If (Err.number = 0) And (lRet = 0) Then
objExcel.Cells(intRow, 2).Value = "CORP\" & sUser
else
objExcel.Cells(intRow, 2).Value = "Error: " & err.number & ":" & err.Description
End If
End If
next


'==========================================================================
' The following function checks if the screensaver is active
'==========================================================================

on error resume next

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colProcesses = objWMIService.ExecQuery("Select * from Win32_Process")

For Each objProcess in colProcesses
If Right(objProcess.Name, 4) = ".scr" Then
objExcel.Cells(intRow, 4).Value = "The screen saver " & objProcess.Name & " Start time: " & dtmScreensaverStart
dtmStartTime = objProcess.CreationDate
dtmScreensaverStart = WMIDateStringToDate(dtmStartTime)
Else
objExcel.Cells(intRow, 4).Value = "The screen saver is not running."
End If
Next

WMIDateStringToDate = CDate(Mid(dtmBootup, 5, 2) & "/" & Mid(dtmBootup, 7, 2) & "/" & Left(dtmBootup, 4) & " " & Mid (dtmBootup, 9, 2) & ":" & Mid(dtmBootup, 11, 2) & ":" & Mid(dtmBootup,13, 2))



'==========================================================================
' The following function show the OS / version and service pack information
'==========================================================================

on error resume next

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colOSes = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")

For Each objOS in colOSes
If IsNull(objStatus.StatusCode) Or objStatus.Statuscode<>0 Then
objExcel.Cells(intRow, 4).Value = ""
objExcel.Cells(intRow, 7).Value = ""
objExcel.Cells(intRow, 8).Value = ""
objExcel.Cells(intRow, 9).Value = ""
else
objExcel.Cells(intRow, 7).Value = objOS.Caption 'Name
objExcel.Cells(intRow, 8).Value = objOS.ServicePackMajorVersion & "." & objOS.ServicePackMinorVersion
objExcel.Cells(intRow, 9).Value = objOS.Version 'Version & build
End If
Next

'==========================================================================
' The following function show the MAC address of the machine name
'==========================================================================


Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")

For Each objItem in colItems
If IsNull(objStatus.StatusCode) Or objStatus.Statuscode<>0 Then
objExcel.Cells(intRow, 10).Value = ""
Else
objExcel.Cells(intRow, 10).Value = objItem.MACAddress
End If
Next


'==========================================================================
' The following function will autosize the columns
'==========================================================================

objExcel.Range("A1:J1").Select
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit


Next
intRow = intRow + 1
Loop

------
<end of vbscript>

the file name of the excel spreadsheet that holds the computer names is File_Name.xls and is stored on my B: drive. So if you want to run it on a C: drive make sure to change the "Set objWorkbook = objExcel.Workbooks.Open("b:File_Name.xls")" to "Set objWorkbook = objExcel.Workbooks.Open("C:File_Name.xls")"

Maybe you can use this script and/or if someone knows how to get the full name in the C column, that would be great!

Kind regards,

Marcel

[adagev3]

Chris128,

I have made several small projects in VB to do certain tasks, but none as complex as this. I'm in no way an expert and I ran into this exact need today when I was given a list of 160+ names and was asked to provide the usernames. I searched for an hour or so until I ran into this thread. I made this account specifically to express my appreciation for you taking the time to put all of this together. I managed to get this working for me within 10 minutes of finding the thread and it's flawless for what I need. Forget "a better way to do it" ... it gave me exactly what I needed and it's been added to my arsenal. Anyway, thank you so much again. I am forever in your debt, good sir.