Solved: VBA. Get list of computers from Active Directory for a particular OU

Create Account

Microsoft Access

Questions

Followers

Top Experts

Skini Mini

VBA. Get list of computers from Active Directory for a particular OU

Guys, could you please give me a hand with function in VBA?
I would like to take a list of computers from Active Directory and insert it into Access table. With computer name, I also would like to get and insert into table attributes: description, whenChanged, whenCreated, lastLogonTimestamp, lastLogon
OU where computers stored are:
1) eur.XXX.com/FRP/SMZ/SMZ Workstations 7; eur.XXX.com/FRP/SMZ/SMZ Workstations 7 Lite
2) Domen-controler which is needed to use: EURSMZ-HUB31.eur.XXX.com

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Jim P.🇺🇸

The lastLogonTimestamp is the number of seconds since 01/01/1753.

I wrote up an article on how to convert it.

Convert AD time to real time

Skini Mini

ASKER

Why, thank you. But it's not a solution to my question.

Jim P.🇺🇸

I know. The way I would do it is that I dump the results to a text file:

dsquery computer -limit 0 | dsget computer -dn -samid  -disabled  >  "\\Server\Share\temp\%COMPUTERNAM%_complist.txt"

dsquery * -filter "(&(objectCategory=computer)(objectClass=computer))" -limit 0 -attr distinguishedName samid sAMAccountName lastLogon > \\Server\Share\temp\%COMPUTERNAM%_compLognn.txt$"

Open in new window

In my case we run about 80 hosted domains that I really can't do a direct access with Access.

So I had to do the compromise and set out a share that only had user/server information in a central point, and then write to that.

Here is some code for querying AD from VBA..

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Skini Mini

ASKER

Yeah you could manually export data from Active Directory into text file and then insert it into Access date base (what I'm kinda do right now). But I would like to automatize this.

Vadim Rapp🇺🇸

Here's a sample:

    Dim c As Object, c2 as object, rs As Object, rs2 as object
    Set c = CreateObject("adodb.connection")
    Set c2 = CreateObject("adodb.connection")
    c.Open "Provider=ADsDSOObject"
    Set rs = CreateObject("adodb.recordset")
    Set rs2 = CreateObject("adodb.recordset")
    rs.Open "SELECT cn,whenchanged, (other fields of interest) FROM 'LDAP://ou=MyOU,dc=my,dc=domain,dc=com' WHERE objectcategory='computer'", c
    c2.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\my-Access-database.mdb"
    rs2.open <destination table>,c2, adOpenDynamic, adLockOptimistic
    do while not rs.eof
      rs2.addnew
      rs2(destination-field) = rs("cn")
      '  (specify other fields)
      rs2.update
      rs.movenext
    loop
    rs2.update
    rs2.close
    rs.Close
    c.Close
    c2.close

Open in new window

Skini Mini

ASKER

Thank you, vadimrapp1. It wasn't pointed out but I was going to run the function from MS Access. Anyway let's simplify and pretend I want to put computers into array. I'm sorry that need more samples.

LDAP://ou=MyOU

Open in new window

1) ^^ how do I put MyOU there?

LDAP://ou=eur.XXX.com/FRP/SMZ/SMZ Workstations 7

Open in new window

it that right?
2) How can I put computers and their attributes into array?

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Vadim Rapp🇺🇸

If I understand correctly, eur.XXX.com is your domain, not OU. In any case, if domain is eur.xxx.com and OU is MyOU, then ldap://ou=myou,dc=eur,dc=domain,dc=com

Regarding the array, after you opened the recordset "rs",

dim MyArray(), i as integer
redim myarray(rs.recordcount-1)
i=0
for i=0 to rs.recordcount-1
myarray(i)=rs(0)
rs.movenext
next i

The above, however, only assigns computer name. If you need all attributes, you have to either use arrays of custom types, or several arrays. We suggest to contact a competent programmer.

RobSampson🇦🇺

Hi, you can also try this VBS code, which will query the two OUs (from arrOUs) and output some attributes, after storing the values into a disconnected recordset.

Const adVarChar = 200
Const MaxCharacters = 255

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "ComputerName", adVarChar, MaxCharacters
DataList.Fields.Append "adsPath", adVarChar, MaxCharacters
DataList.Fields.Append "Description", adVarChar, MaxCharacters
DataList.Open
	
Set objFSO = CreateObject("Scripting.FileSystemObject")

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
arrOUs = Array( _
	"EURSMZ-HUB31.eur.XXX.com/OU=SMZ Windows 7,OU=SMZ,OU=FRP,DC=eur,DC=xxx,DC=com", _
	"EURSMZ-HUB31.eur.XXX.com/OU=SMZ Windows 7 Lite,OU=SMZ,OU=FRP,DC=eur,DC=xxx,DC=com" _
	)

For Each strOU In arrOUs
	objCommand.CommandText = "<LDAP://" & strOU & ">;(&(objectCategory=Computer));adsPath;Subtree"
	Set objRecordSet = objCommand.Execute
	
	objRecordSet.MoveFirst
	Do Until objRecordSet.EOF
		Set objComputer = GetObject(objRecordSet("adsPath"))
		DataList.AddNew
		DataList("ComputerName") = objComputer.Name
		DataList("adsPath") = objComputer.adsPath
		DataList("Description") = objComputer.Description
		DataList.Update
		objRecordSet.MoveNext
	Loop
Next

DataList.Sort = "ComputerName"

DataList.MoveFirst
Do Until DataList.EOF
	WScript.Echo """" & DataList("ComputerName") & """,""" & DataList("adsPath") & """,""" & DataList("description") & """"
	DataList.MoveNext
Loop
DataList.Close

WScript.Echo "Done."

Open in new window

For some attributes like LastLogonTimeStamp, you need to query all domain controllers, since it is not a replicated attribute, so the script becomes much more complex.

Regards,

Rob.

Skini Mini

ASKER

Thank you RobSampson. I've tried your code and there was error: 8007203A - The server is not operational on line: Set objRecordSet = objCommand.Execute

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

RobSampson🇦🇺

Did you make sure the server and LDAP paths on lines 20 and 21 we're set correctly to your domain controller and OU path?

Skini Mini

ASKER

There is error: no such object on the server. Could you please take a look at screenshot?

Vadim Rapp🇺🇸

Little clarification re. the code I posted. In order to query the objects from sub-OU, such as when SMZ is within FRP, the syntax is

ldap://ou=SMZ,ou=FRP,dc=eur,dc=alcoa,dc=com

Example of an array with custom type:

Type mytype
var1 As Integer
var2 As string
End Type
sub main
declare myarray() as mytype
redim myarray(5)
myarray(0).var1=3
myarray(0).var2="my value"
end sub

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

ASKER CERTIFIED SOLUTION

RobSampson🇦🇺

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

Skini Mini

ASKER

Thank you, we finally get it working. I've created a topic with questions about this function like: "how to increase the amount of records that function returns", "how the get other attributes from AD". If you're interested here is a link: "VB script to query computers from AD": https://www.experts-exchange.com/questions/28468695/VB-script-to-query-computers-from-AD.html

Microsoft Access

Questions

Followers

Top Experts

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.