AD to Access

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

AD to Access

Logged in as: Guest

arrSession:exec spGetSession 2,2,42182

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> AD to Access
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1] 2 next > >>

Message

<< Older Topic Newer Topic >>

AD to Access - 1/18/2007 5:58:24 AM

twilliamsen

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline

I am trying to create an inventory database by querying the AD through ADO. I am attempting to grab the computer names from Active Directory and use those names to create an Inventory of the computers and their hardware

I have commeted some of the items out because I get errors with them but have not chose to remove them yet.

Here is my code:

[color=#008000][size=2]
' =============================================================
' Open Access Database via ADODB
' =============================================================
[/size][/color][color=#0000ff][size=2]
Const[/size][/color][size=2] adOpenStatic = 3
[/size][color=#0000ff][size=2]
Const[/size][/color][size=2] adLockOptimistic = 3
[/size][color=#0000ff][size=2]
Const[/size][/color][size=2] adUseClient = 3
[/size][color=#0000ff][size=2]
Set[/size][/color][size=2] objConnection = CreateObject("ADODB.Connection")
[/size][color=#0000ff][size=2]
Set[/size][/color][size=2] objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic
[/size][color=#008000][size=2]
'================================================================================================
' Retrieve Computer Account Names from Active Directory
'===============================================================================================
[/size][/color][size=2]
[/size][color=#008000][size=2]'Global variables
[/size][/color][size=2]
[/size][color=#0000ff][size=2]Dim[/size][/color][size=2] oContainer

[/size][color=#008000][size=2]
'Initialize global variables
[/size][/color][size=2]

[/size][color=#0000ff][size=2]
Set[/size][/color][size=2] oContainer=GetObject("LDAP://cn=Computers,DC=corp-nj,DC=mrsassociates,dc=com ")

[/size][color=#0000ff][size=2]
Const[/size][/color][size=2] ADS_SCOPE_SUBTREE = 2
[/size][color=#0000ff][size=2]
Set[/size][/color][size=2] objConnection = CreateObject("ADODB.Connection")
[/size][color=#0000ff][size=2]
Set[/size][/color][size=2] objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
[/size][color=#0000ff][size=2]
Set[/size][/color][size=2] objCOmmand.ActiveConnection = objConnection
objCommand.CommandText = _
"Select Name from 'LDAP://cn=Computers,DC=corp-nj,DC=mrsassociates,dc=com'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
[/size][color=#0000ff][size=2]
Set[/size][/color][size=2] objRecordSetAD = objCommand.Execute
objRecordSetAD.MoveFirst
[/size][color=#008000][size=2]
'Do Until objRecordSetAD.EOF
'objRecordsetAD.MoveNext
'Loop
' ==================================================================================================
' Grabs Computer Name, Manufacturer, Model, and Installed Ram from the ComputerSystem WMI Service
'===================================================================================================
[/size][/color][color=#0000ff][size=2]
set[/size][/color][size=2] strComputers = objRecordsetAD("Name")
[/size][color=#0000ff][size=2]
Set[/size][/color][size=2] objWMIService = GetObject("winmgmts:\\" & strComputer& "\root\cimv2")
[/size][color=#0000ff][size=2]
Set[/size][/color][size=2] colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem") 
[/size][color=#0000ff][size=2]
For[/size][/color][size=2] [/size][color=#0000ff][size=2]Each[/size][/color][size=2] objItem [/size][color=#0000ff][size=2]in[/size][/color][size=2] colItems 
objRecordset.AddNew
objRecordset("ComputerName") = objItem.Name
objRecordset("Manufacturer") = objItem.Manufacturer
objRecordset("Model") = objItem.Model
objRecordset("Installed Ram") = objItem.TotalPhysicalMemory
objRecordset("CPU Count") = objItem.NumberofProcessors
objRecordset.Update
[/size][color=#0000ff][size=2]
Next
[/size][/color][color=#008000][size=2]
' =========================================================================
' Queries the Processor WMI Service to grab the Processor Name
' =========================================================================
[/size][/color][color=#0000ff][size=2]
Set[/size][/color][size=2] colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Processor")
[/size][color=#0000ff][size=2]For[/size][/color][size=2] [/size][color=#0000ff][size=2]Each[/size][/color][size=2] objItem [/size][color=#0000ff][size=2]in[/size][/color][size=2] ColItems
[/size][color=#008000][size=2]
'objRecordset.AddNew
[/size][/color][size=2]
objRecordset("CPU Name") = objItem.Name
objRecordset.Update
[/size][color=#0000ff][size=2]
Next

[/size][/color][color=#008000][size=2]
'====================================================================================================
' Queries LogicalDisk WMI Service to retrieve Hard Drive Size and Free Space
'====================================================================================================
[/size][/color][color=#0000ff][size=2]
Set[/size][/color][size=2] colItems = objWMIService.ExecQuery("SELECT * FROM Win32_LogicalDisk")
[/size][color=#0000ff][size=2]For[/size][/color][size=2] [/size][color=#0000ff][size=2]Each[/size][/color][size=2] objItem [/size][color=#0000ff][size=2]in[/size][/color][size=2] ColItems
[/size][color=#008000][size=2]
'objRecordset.AddNew
[/size][/color][size=2]
objRecordset("Hard Drive Size") = objItem.Size
objRecordset("Hard Drive Free Space") = objItem.FreeSpace

objRecordset.Update
[/size][color=#0000ff][size=2]
Next
[/size][/color][color=#008000][size=2]
'====================================================================================================
' Queries the BIOS WMI Service to retrieve the BIOS Version 
'====================================================================================================
[/size][/color][color=#0000ff][size=2]
Set[/size][/color][size=2] colItems = objWMIService.ExecQuery("SELECT * FROM Win32_BIOS")
[/size][color=#0000ff][size=2]For[/size][/color][size=2] [/size][color=#0000ff][size=2]Each[/size][/color][size=2] objItem [/size][color=#0000ff][size=2]in[/size][/color][size=2] colItems
[/size][color=#008000][size=2]
'objRecordset.AddNew
[/size][/color][size=2]
objRecordset("BIOS Version") = objItem.Name

objRecordset.Update
[/size][color=#0000ff][size=2]
Next
[/size][/color][color=#008000][size=2]
'=====================================================================================================
' Queries the PhysicalMemory WMI Service to retrieve the Memory Type
'=====================================================================================================
[/size][/color][color=#0000ff][size=2]
Set[/size][/color][size=2] colItems = objWMIService.ExecQuery("SELECT * FROM Win32_PhysicalMemory")
[/size][color=#0000ff][size=2]For[/size][/color][size=2] [/size][color=#0000ff][size=2]Each[/size][/color][size=2] objItem [/size][color=#0000ff][size=2]in[/size][/color][size=2] ColItems
[/size][color=#008000][size=2]
'objRecordset.AddNew
[/size][/color][size=2]

objRecordset("RAM Speed") = objItem.Speed
[/size][color=#008000][size=2]'objRecordset("RAM Capacity") = objItem.Capacity pulls capacity of ram chip, not motherboard capacity
[/size][/color][size=2]
objRecordset.Update
[/size][color=#0000ff][size=2]
Next
Set[/size][/color][size=2] ColItems = objWMIService.ExecQuery("SELECT * FROM Win32_PhysicalMemoryArray")

[/size][color=#0000ff][size=2]For[/size][/color][size=2] [/size][color=#0000ff][size=2]Each[/size][/color][size=2] objItem [/size][color=#0000ff][size=2]In[/size][/color][size=2] colItems

objRecordSet("RAM Capacity") = objItem.MaxCapacity

objRecordset.Update

[/size][color=#0000ff][size=2]
Next
[/size][/color][color=#008000][size=2]
' =========================================================================================
' Queries NetworkAdapterConfiguration WMI Service to get the IP Address 
' Only if TCP/IP is running on target Computer
' =========================================================================================
[/size][/color][color=#0000ff][size=2]
Set[/size][/color][size=2] IPConfigSet = objWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")

[/size][color=#0000ff][size=2]
For[/size][/color][size=2] [/size][color=#0000ff][size=2]Each[/size][/color][size=2] IPConfig [/size][color=#0000ff][size=2]in[/size][/color][size=2] IPConfigSet
[/size][color=#0000ff][size=2]If[/size][/color][size=2] [/size][color=#0000ff][size=2]Not[/size][/color][size=2] IsNull(IPConfig.IPAddress) [/size][color=#0000ff][size=2]Then[/size][/color][size=2] 
[/size][color=#0000ff][size=2]For[/size][/color][size=2] i=[/size][color=#0000ff][size=2]LBound[/size][/color][size=2](IPConfig.IPAddress) [/size][color=#0000ff][size=2]to[/size][/color][size=2] [/size][color=#0000ff][size=2]UBound[/size][/color][size=2](IPConfig.IPAddress)
IPAddress = IPConfig.IPAddress(i)
objRecordset("IP Address") = IPAddress
objRecordset("MAC Address") = IPConfig.MACAddress
objRecordset.Update
[/size][color=#0000ff][size=2]Next
[/size][/color][size=2]
[/size][color=#0000ff][size=2]End[/size][/color][size=2] [/size][color=#0000ff][size=2]If
Next
[/size][/color][color=#008000][size=2]
'=================================================================================
' Closes Recordset and Connection
'=================================================================================
[/size][/color][size=2]
objRecordset.Close
objConnection.Close
objRecordsetAD.Close

[/size][color=#0000ff][size=2]
Set[/size][/color][size=2] colItems = [/size][color=#0000ff][size=2]Nothing
Set[/size][/color][size=2] objWMIService = [/size][color=#0000ff][size=2]Nothing
[/size][/color]

Post #: 1

RE: AD to Access - 1/18/2007 6:23:34 AM

dm_4ever

Posts: 2722
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

Is all the info you're collecting going into one table?

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)

Post #: 2

RE: AD to Access - 1/18/2007 6:29:57 AM

twilliamsen

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline

Yes for now. That is the easy part.

(in reply to dm_4ever)

Post #: 3

RE: AD to Access - 1/18/2007 6:35:56 AM

dm_4ever

Posts: 2722
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

If it's going into one table, then your code should look more like this:
One objRecordset.AddNew and objRecordset.Update per machine. You may want to put some error handling in there as well since there may be some machines that you will not be able to connect to via WMI due to permission issues or WMI related problems.

' =============================================================
' Open Access Database via ADODB
' =============================================================
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic

'================================================================================================
' Retrieve Computer Account Names from Active Directory
'===============================================================================================

'Global variables

Dim oContainer

'Initialize global variables

Set oContainer=GetObject("LDAP://cn=Computers,DC=corp-nj,DC=mrsassociates,dc=com ")

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.CommandText = _
"Select Name from 'LDAP://cn=Computers,DC=corp-nj,DC=mrsassociates,dc=com'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

Set objRecordSetAD = objCommand.Execute
objRecordSetAD.MoveFirst

Do Until objRecordSetAD.EOF
  objRecordset.AddNew 
  ' ==================================================================================================
  ' Grabs Computer Name, Manufacturer, Model, and Installed Ram from the ComputerSystem WMI Service
  '===================================================================================================
  
  strComputers = objRecordsetAD("Name")
  
  Set objWMIService = GetObject("winmgmts:\\" & strComputer& "\root\cimv2")
  
  Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem") 
  For Each objItem in colItems 
      objRecordset("ComputerName") = objItem.Name
      objRecordset("Manufacturer") = objItem.Manufacturer
      objRecordset("Model") = objItem.Model
      objRecordset("Installed Ram") = objItem.TotalPhysicalMemory
      objRecordset("CPU Count") = objItem.NumberofProcessors    
  Next
  
  ' =========================================================================
  ' Queries the Processor WMI Service to grab the Processor Name
  ' =========================================================================
  
  Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Processor")
  For Each objItem in ColItems
      objRecordset("CPU Name") = objItem.Name
  Next
  
  '====================================================================================================
  ' Queries LogicalDisk WMI Service to retrieve Hard Drive Size and Free Space
  '====================================================================================================
  
  Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_LogicalDisk")
  For Each objItem In ColItems
      objRecordset("Hard Drive Size") = objItem.Size
      objRecordset("Hard Drive Free Space") = objItem.FreeSpace    
  Next
  
  '====================================================================================================
  ' Queries the BIOS WMI Service to retrieve the BIOS Version 
  '====================================================================================================
  
  Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_BIOS")
  For Each objItem in colItems
      objRecordset("BIOS Version") = objItem.Name
  Next
  
  '=====================================================================================================
  ' Queries the PhysicalMemory WMI Service to retrieve the Memory Type
  '=====================================================================================================
  
  Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_PhysicalMemory")
  For Each objItem in ColItems
      objRecordset("RAM Speed") = objItem.Speed
      'objRecordset("RAM Capacity") = objItem.Capacity pulls capacity of ram chip, not motherboard capacity
  Next
  
  Set ColItems = objWMIService.ExecQuery("SELECT * FROM Win32_PhysicalMemoryArray")
  For Each objItem In colItems
      objRecordSet("RAM Capacity") = objItem.MaxCapacity
  Next
  
  ' =========================================================================================
  ' Queries NetworkAdapterConfiguration WMI Service to get the IP Address 
  ' Only if TCP/IP is running on target Computer
  ' =========================================================================================
  
  Set IPConfigSet = objWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")
  For Each IPConfig in IPConfigSet
      If Not IsNull(IPConfig.IPAddress) Then 
          For i=LBound(IPConfig.IPAddress) to UBound(IPConfig.IPAddress)
              IPAddress = IPConfig.IPAddress(i)
              objRecordset("IP Address") = IPAddress
              objRecordset("MAC Address") = IPConfig.MACAddress
          Next
      End If
  Next
objRecordset.Update
objRecordsetAD.MoveNext
Loop
'=================================================================================
' Closes Recordset and Connection
'=================================================================================

objRecordset.Close
objConnection.Close
objRecordsetAD.Close
Set colItems = Nothing
Set objWMIService = Nothing

< Message edited by dm_4ever -- 1/18/2007 6:42:47 AM >

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)

Post #: 4

RE: AD to Access - 1/18/2007 6:46:58 AM

twilliamsen

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline

Thanks for the code change, but I am getting an error on line 52

It doesn't like this

Set objWMIService = GetObject("winmgmts:\\" & strComputers & "\root\cimv2")

Which maybe a problem with this:

strComputers = objRecordsetAD.Fields("Name").Value

I get the following error:

Line 52
The remote server machine does not exist or is unavailable: 'GetObject'

< Message edited by twilliamsen -- 1/18/2007 6:49:38 AM >

(in reply to dm_4ever)

Post #: 5

RE: AD to Access - 1/18/2007 7:03:58 AM

dm_4ever

Posts: 2722
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

That probably has to do with what I told you to look out for. There may be some machine's you can't connect to (turned off, not enough permissions, WMI is broken, etc.). Have you verified strComputers = objRecordsetAD.Fields("Name").Value is providing a computername?

I don't think you need this either since you're querying AD:
Dim oContainer

'Initialize global variables

Set oContainer=GetObject("LDAP://cn=Computers,DC=corp-nj,DC=mrsassociates,dc=com ")

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)

Post #: 6

RE: AD to Access - 1/18/2007 7:08:00 AM

twilliamsen

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline

I know it works from when I queried it using that code to dump into a textfile. The computers are to left on all the time. It is company policy, but that doesn't mean they follow it.

Here is the code for dumping the computer names into a textfile

'Global variables
  Dim oContainer
  Dim OutPutFile
  Dim FileSystem
'Initialize global variables
  Set FileSystem = WScript.CreateObject("Scripting.FileSystemObject")
  Set OutPutFile = FileSystem.CreateTextFile("computerList.txt", True) 
  Set oContainer=GetObject("[link=http://www.visualbasicscript.com/ldap://cn=Computers,DC=corp-nj,DC=mrsassociates,dc=com]LDAP://cn=Computers,DC=corp-nj,DC=mrsassociates,dc=com[/link] ")
  
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.CommandText = _
  "Select Name, Location from 'LDAP://cn=Computers,DC=corp-nj,DC=mrsassociates,dc=com'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
 OutPutFile.writeline  objRecordSet.Fields("Name").Value
  objRecordSet.MoveNext
Loop

< Message edited by twilliamsen -- 1/18/2007 7:09:55 AM >

(in reply to dm_4ever)

Post #: 7

RE: AD to Access - 1/18/2007 7:19:17 AM

dm_4ever

Posts: 2722
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

You mentioned it yourself, the error is with this line: Set objWMIService = GetObject("winmgmts:\\" & strComputers & "\root\cimv2") which means you're querying AD just fine, but having issues connecting via WMI to the machine. The piece below may help get over the problem computer, but you may want to think of a better method to handle errors. Perhaps add logging for machine's you're unable to connect via WMI for whatever reason.

Do Until objRecordSetAD.EOF
   ' ==================================================================================================
   ' Grabs Computer Name, Manufacturer, Model, and Installed Ram from the ComputerSystem WMI Service
   '===================================================================================================

   strComputers = objRecordsetAD("Name")
   On Error Resume Next
   Set objWMIService = GetObject("winmgmts:\\" & strComputer& "\root\cimv2")
   If Err.Number <> 0 Then 'if you get an error trying to connect then move to the next computer
       On Error GoTo 0
       objRecordsetAD.MoveNext
   End If
   objRecordset.AddNew

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)

Post #: 8

RE: AD to Access - 1/18/2007 7:25:47 AM

ebgreen

Posts: 5246
Score: 31
Joined: 7/12/2005
Status: online

I would add this just to be on the safe side:

strComputers = objRecordsetAD("Name")
On Error Resume Next
Set objWMIService = GetObject("winmgmts:\\" & strComputer& "\root\cimv2")
If Err.Number <> 0 Then 'if you get an error trying to connect then move to the next computer
On Error GoTo 0
objRecordsetAD.MoveNext
End If
On Error Goto 0
objRecordset.AddNew

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to dm_4ever)

Post #: 9

RE: AD to Access - 1/18/2007 7:41:39 AM

twilliamsen

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline

Still isn't working properly. Maybe I should go back to a text file, then delete the file after words.

(in reply to dm_4ever)

Post #: 10

RE: AD to Access - 1/18/2007 7:53:12 AM

twilliamsen

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline

quote:

ORIGINAL: ebgreen

I would add this just to be on the safe side:

strComputers = objRecordsetAD("Name")
On Error Resume Next
Set objWMIService = GetObject("winmgmts:\\" & strComputer& "\root\cimv2")
If Err.Number <> 0 Then 'if you get an error trying to connect then move to the next computer
On Error GoTo 0
objRecordsetAD.MoveNext
End If
On Error Goto 0
objRecordset.AddNew

With this I get an error of Object Required: 'objWMIService'

Line 49
char: 1

Which is this line

Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem")

(in reply to ebgreen)

Post #: 11

RE: AD to Access - 1/18/2007 7:58:00 AM

ebgreen

Posts: 5246
Score: 31
Joined: 7/12/2005
Status: online

Where do you instantiate the objWMIService object?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)

Post #: 12

RE: AD to Access - 1/18/2007 8:00:28 AM

twilliamsen

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline

right here

strComputer = objRecordsetAD("Name")
On Error Resume Next
Set objWMIService = GetObject("winmgmts:\\" & strComputer& "\root\cimv2") <------ initiates here

If Err.Number <> 0 Then 'if you get an error trying to connect then move to the next computer

On Error GoTo 0
objRecordsetAD.MoveNext

End If
On Error Goto 0
objRecordset.AddNew

Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem")
For Each objItem in colItems
objRecordset("ComputerName") = objItem.Name
objRecordset("Manufacturer") = objItem.Manufacturer
objRecordset("Model") = objItem.Model
objRecordset("Installed Ram") = objItem.TotalPhysicalMemory
objRecordset("CPU Count") = objItem.NumberofProcessors
Next

(in reply to ebgreen)

Post #: 13

RE: AD to Access - 1/18/2007 8:04:38 AM

ebgreen

Posts: 5246
Score: 31
Joined: 7/12/2005
Status: online

So if you cannot connect to strComputer, then you won't be able to instantiate the object. Your logic needs to reflect that.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)

Post #: 14

RE: AD to Access - 1/18/2007 8:06:44 AM

twilliamsen

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline

To test it should I run a test on it to see if I can dump the AD computers into a text file and compare the text file to the AD and then see if I can connect to that PC?

(in reply to ebgreen)

Post #: 15

RE: AD to Access - 1/18/2007 10:56:21 AM

dm_4ever

Posts: 2722
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

How about this? I just put the piece where you query WMI and insert into a DB into a function. Should make testing an individual machine easier.

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")

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

Set objRecordSetAD = objCommand.Execute
objRecordSetAD.MoveFirst

Do Until objRecordSetAD.EOF
    GetPCInfo objRecordsetAD("Name")
   objRecordsetAD.MoveNext
Loop

Function GetPCInfo(strComputer)
   ' ==================================================================================================
   ' Grabs Computer Name, Manufacturer, Model, and Installed Ram from the ComputerSystem WMI Service
   '===================================================================================================
   On Error Resume Next
   Set objWMIService = GetObject("winmgmts:\\" & strComputer& "\root\cimv2")
   If Err.Number <> 0 Then
       On Error GoTo 0
       Exit Function
   End If
   objRecordset.AddNew 
   Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem") 
   For Each objItem in colItems 
       objRecordset("ComputerName") = objItem.Name
       objRecordset("Manufacturer") = objItem.Manufacturer
       objRecordset("Model") = objItem.Model
       objRecordset("Installed Ram") = objItem.TotalPhysicalMemory
       objRecordset("CPU Count") = objItem.NumberofProcessors    
   Next
   
   ' =========================================================================
   ' Queries the Processor WMI Service to grab the Processor Name
   ' =========================================================================
   
   Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Processor")
   For Each objItem in ColItems
       objRecordset("CPU Name") = objItem.Name
   Next
   
   '====================================================================================================
   ' Queries LogicalDisk WMI Service to retrieve Hard Drive Size and Free Space
   '====================================================================================================
   
   Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_LogicalDisk")
   For Each objItem In ColItems
       objRecordset("Hard Drive Size") = objItem.Size
       objRecordset("Hard Drive Free Space") = objItem.FreeSpace    
   Next
   
   '====================================================================================================
   ' Queries the BIOS WMI Service to retrieve the BIOS Version 
   '====================================================================================================
   
   Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_BIOS")
   For Each objItem in colItems
       objRecordset("BIOS Version") = objItem.Name
   Next
   
   '=====================================================================================================
   ' Queries the PhysicalMemory WMI Service to retrieve the Memory Type
   '=====================================================================================================
   
   Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_PhysicalMemory")
   For Each objItem in ColItems
       objRecordset("RAM Speed") = objItem.Speed
       'objRecordset("RAM Capacity") = objItem.Capacity pulls capacity of ram chip, not motherboard capacity
   Next
   
   Set ColItems = objWMIService.ExecQuery("SELECT * FROM Win32_PhysicalMemoryArray")
   For Each objItem In colItems
       objRecordSet("RAM Capacity") = objItem.MaxCapacity
   Next
   
   ' =========================================================================================
   ' Queries NetworkAdapterConfiguration WMI Service to get the IP Address 
   ' Only if TCP/IP is running on target Computer
   ' =========================================================================================
   
   Set IPConfigSet = objWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")
   For Each IPConfig in IPConfigSet
       If Not IsNull(IPConfig.IPAddress) Then 
           For i=LBound(IPConfig.IPAddress) to UBound(IPConfig.IPAddress)
               IPAddress = IPConfig.IPAddress(i)
               objRecordset("IP Address") = IPAddress
               objRecordset("MAC Address") = IPConfig.MACAddress
           Next
       End If
   Next
   objRecordset.Update
'=================================================================================
' Closes Recordset and Connection
'=================================================================================
   Set colItems = Nothing
   Set objWMIService = Nothing
End Function

objRecordset.Close
objConnection.Close
objRecordsetAD.Close

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)

Post #: 16

RE: AD to Access - 1/19/2007 1:35:36 AM

twilliamsen

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline

Very interesting. I must of burned myself out, why didn't I think of that? Let me give it a whirl and see what happens! I will let you know the result!

(in reply to dm_4ever)

Post #: 17

RE: AD to Access - 1/19/2007 2:24:01 AM

ebgreen

Posts: 5246
Score: 31
Joined: 7/12/2005
Status: online

I would still suggest putting an On Error Goto 0 outside the If-End If block.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)

Post #: 18

RE: AD to Access - 1/19/2007 2:38:40 AM

dm_4ever

Posts: 2722
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

Good point

...especially while trying to find and work out any kinks.

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to ebgreen)