Querying Active Directory for computers

Querying Active Directory for computers

We have physical and Virtual machines, I need to retrieve a report with the following fields:

Windows OS= I need just windows servers 2003 or 2008
Active Directory account of the server= enabled
Type of windows server= Physical machine or Virtual machine

I have vsphere client console and Active Directory console, but a lot of computers to go through manually and check all that info.

Any help will be very much appreciated.

Thanks
jskfanAsked:
Who is Participating?
 
RobSampsonCommented:
See if this helps.  Error 462 typically means that permission is denied, there are DNS issues, or WMI is blocked or not working on the remote machine.  Error 70 means permission is denied.  You may need to run the script from an elevated command prompt, using a domain admin account.

Regards,

Rob.

strLog = "C:\Temp\SystemOS.csv"
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const ADS_SCOPE_SUBTREE = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
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"
objCommand.ActiveConnection = objConnection

Set objLog = objFSO.CreateTextFile(strLog, True)
objLog.WriteLine """Hostname"",""Operating System"",""Status"",""Type"""

strOS = ""
strFilter = "(&(objectCategory=computer)(objectClass=computer))"

strQuery = "<LDAP://" & strDomain & ">;" & strFilter & ";distinguishedName,operatingsystem,samaccountname;subtree"
objCommand.CommandText = strQuery

Set objRecordSet = objCommand.Execute

Do Until objRecordSet.EOF
	strComputerName = Replace(objRecordSet.Fields("samaccountname").Value, "$", "")
	strOS = objRecordSet.Fields("operatingsystem").Value
	If InStr(1, strOS, "Server", vbTextCompare) > 0 Then
		Set objServer = GetObject("LDAP://" & objRecordSet.Fields("distinguishedName").Value)
		If objServer.AccountDisabled = False Then
			strStatus = "Enabled"
		Else
			strStatus = "Disabled"
		End If
		If Ping(strComputerName) = True Then
			On Error Resume Next
			Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputerName & "\root\cimv2")
			If Err.Number = 0 Then
				strQuery = "SELECT Model FROM Win32_ComputerSystem"
				Set colItems = objWMIService.ExecQuery(strQuery, "WQL", 48)
				For Each objItem In colItems
					strModel = objItem.Model
				Next
				If InStr(1, strModel, "Virtual", vbTextCompare) > 0 Then
					strType = "Virtual"
				Else
					strType = "Physical"
				End If
			Else
				If Err.Number = 70 Then
					strError = "Permission denied"
				ElseIf Err.Number = 462 Then
					strError = "The remote server machine does not exist or is unavailable"
				Else
					strError = Err.Description
				End If
				strModel = "<ERROR " & Err.Number & ": " & strError & ">"
				strType = "<ERROR " & Err.Number & ": " & strError & ">"
			End If
			Err.Clear
			On Error GoTo 0
		Else
			strType = "<OFFLINE>"
		End If
		objLog.WriteLine """" & strComputerName & """,""" & strOS & """,""" & strStatus & """,""" & strType & """"
	End If
	objRecordSet.Movenext
Loop

Set objCommand = Nothing
Set objConnection = Nothing
objLog.Close

WScript.Echo "Finished"

Function Ping(strComputer)
	Set cPingResults = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _
		"." & "/root/cimv2"). ExecQuery("SELECT * FROM Win32_PingStatus " & _
		"WHERE Address = '" + strComputer + "'")
	 
	For Each oPingResult In cPingResults
		If IsNull(oPingResult.StatusCode) or oPingResult.StatusCode<>0 Then
			Ping = False
		Else
			Ping = True
		End If
	Next
End Function

Open in new window

0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
How does Active Directory know the difference between a physical and virtual computer ?

Unless you have a naming schema, IP Address range....it's just a machine name.

You will need to use a combination, of scripts and Inventory Assest Management.

RV Tools will give you OS Name and Computer Name from vSphere Inventory

http://www.robware.net/
0
 
Jim P.Commented:
Active Directory account of the server= enabled

That can be done via command line:
dsquery computer | dsget computer -dn -samid -disabled

Open in new window


Windows OS= I need just windows servers 2003 or 2008

That is probably buried in some arcane chunk of the AD, but the VM/Physical is not going to be there as  hanccocka pointed out. I have never used vsphere, but we use Spiceworks and it has reports that tell OS Version as well as the hardware it is running on or that it is a VM.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ChrisCommented:
you could combine a powershell AD query with a WMI query to get computer account and then get the Physical/Virtual bit from the computer

i.e.
$complist  = get-adcomputer -filter * | fl Name
foreach ($computer in $complist { get-wmiobject  Win32_ComputerSystem | fl Model}

this bit of code needs a bit of work and testing but the basic principles would give you what you need

this link here gives you a whole lot more

http://www.powershellpro.com/powershell-tutorial-introduction/powershell-scripting-with-wmi/
0
 
Netman66Commented:
You can download and try a copy of Hyena from SystemTools.  It's free for 30 days.

http://www.systemtools.com/hyena/

It may give you the ability to get everything you need (and more).
0
 
RobSampsonCommented:
Hi, I haven't tested this at all, but in theory, it should check each server and see whether the Model property of the Win32_ComputerSystem class contains the word "Virtual".

It writes output to the file specified on this line:
strLog = "C:\Temp\SystemOS.csv"

Regards,

Rob.

strLog = "C:\Temp\SystemOS.csv"
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const ADS_SCOPE_SUBTREE = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
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"
objCommand.ActiveConnection = objConnection

Set objLog = objFSO.CreateTextFile(strLog, True)
objLog.WriteLine """Hostname"",""Operating System"",""Status"",""Type"""

strOS = ""
strFilter = "(&(objectCategory=computer)(objectClass=computer))"

strQuery = "<LDAP://" & strDomain & ">;" & strFilter & _ 
	";distinguishedName,operatingsystem,samaccountname;subtree"
objCommand.CommandText = strQuery

Set objRecordSet = objCommand.Execute

Do Until objRecordSet.EOF
	strComputerName = objRecordSet.Fields("samaccountname").Value
	strOS = objRecordSet.Fields("operatingsystem").Value
	If InStr(1, strOS, "Server", vbTextCompare) > 0 Then
		Set objServer = GetObject("LDAP://" & objRecordSet.Fields("distinguishedName").Value)
		strStatus = objServer.AccountDisabled
		If Ping(strComputerName) = True Then
			Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputerName & "\root\cimv2")
			strQuery = "SELECT Model FROM Win32_ComputerSystem"
			Set colItems = objWMIService.ExecQuery(strQuery, "WQL", 48)
			For Each objItem In colItems
				strModel = objItem.Model
			Next
			If InStr(1, strModel, "Virtual", vbTextCompare) > 0 Then
				strType = "Virtual"
			Else
				strType = "Physical"
			End If
		Else
			strType = "<OFFLINE>"
		End If
		objLog.WriteLine """" & strComputerName & """,""" & strOS & """,""" & strStatus & """,""" & strType & """"
	End If
	objRecordSet.Movenext
Loop

Set objCommand = Nothing
Set objConnection = Nothing
objLog.Close

WScript.Echo "Finished"

Function Ping(strComputer)
	Set cPingResults = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _
		"." & "/root/cimv2"). ExecQuery("SELECT * FROM Win32_PingStatus " & _
		"WHERE Address = '" + strComputer + "'")
	 
	For Each oPingResult In cPingResults
		If IsNull(oPingResult.StatusCode) or oPingResult.StatusCode<>0 Then
			Ping = False
		Else
			Ping = True
		End If
	Next
End Function

Open in new window

0
 
jskfanAuthor Commented:
Hostname	Operating System	                                                    Status	Type
           srvfDC02	Windows Server 2008 	R2 Standard	FALSE	<OFFLINE>
           srCTRX01	Windows Server 2003			FALSE	<OFFLINE>
           srvfDC04	Windows Server 2008 	R2 Standard	FALSE	<OFFLINE>
           srvfDC01	Windows Server 2008 	R2 Standard	FALSE	<OFFLINE>
          srmeaDC01	Windows Server 2008 	R2 Standard	FALSE	<OFFLINE>
           srvfDC03	Windows Server 2008 	R2 Standard	FALSE	<OFFLINE>

Open in new window

0
 
jskfanAuthor Commented:
The CSV output  is shown  as the example above.
I do not see where it should show if a computer is VM or not
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
You may want to change the string from Virtual to VMware
0
 
jskfanAuthor Commented:
changed it to VMware it did not work
0
 
RobSampsonCommented:
Change this line:
	strComputerName = objRecordSet.Fields("samaccountname").Value

Open in new window


to this
	strComputerName = Replace(objRecordSet.Fields("samaccountname").Value, "$", "")

Open in new window


Regards,

Rob.
0
 
RobSampsonCommented:
It should still work with
                        strType = "Virtual"
0
 
jskfanAuthor Commented:
AD
the error is pointing to this line:
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputerName & "\root\cimv2")
0
 
RobSampsonCommented:
OK, try this.  The output will show you which machines cannot be connected to via WMI.

Regards,

Rob.

strLog = "C:\Temp\SystemOS.csv"
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const ADS_SCOPE_SUBTREE = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
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"
objCommand.ActiveConnection = objConnection

Set objLog = objFSO.CreateTextFile(strLog, True)
objLog.WriteLine """Hostname"",""Operating System"",""Status"",""Type"""

strOS = ""
strFilter = "(&(objectCategory=computer)(objectClass=computer))"

strQuery = "<LDAP://" & strDomain & ">;" & strFilter & _ 
	";distinguishedName,operatingsystem,samaccountname;subtree"
objCommand.CommandText = strQuery

Set objRecordSet = objCommand.Execute

Do Until objRecordSet.EOF
	strComputerName = Replace(objRecordSet.Fields("samaccountname").Value, "$", "")
	strOS = objRecordSet.Fields("operatingsystem").Value
	If InStr(1, strOS, "Server", vbTextCompare) > 0 Then
		Set objServer = GetObject("LDAP://" & objRecordSet.Fields("distinguishedName").Value)
		strStatus = objServer.AccountDisabled
		If Ping(strComputerName) = True Then
			On Error Resume Next
			Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputerName & "\root\cimv2")
			If Err.Number = 0 Then
				strQuery = "SELECT Model FROM Win32_ComputerSystem"
				Set colItems = objWMIService.ExecQuery(strQuery, "WQL", 48)
				For Each objItem In colItems
					strModel = objItem.Model
				Next
				If InStr(1, strModel, "Virtual", vbTextCompare) > 0 Then
					strType = "Virtual"
				Else
					strType = "Physical"
				End If
			Else
				strModel = "<ERROR " & Err.Number & ">"
				strType = "<ERROR " & Err.Number & ">"
			End If
			Err.Clear
			On Error GoTo 0
		Else
			strType = "<OFFLINE>"
		End If
		objLog.WriteLine """" & strComputerName & """,""" & strOS & """,""" & strStatus & """,""" & strType & """"
	End If
	objRecordSet.Movenext
Loop

Set objCommand = Nothing
Set objConnection = Nothing
objLog.Close

WScript.Echo "Finished"

Function Ping(strComputer)
	Set cPingResults = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _
		"." & "/root/cimv2"). ExecQuery("SELECT * FROM Win32_PingStatus " & _
		"WHERE Address = '" + strComputer + "'")
	 
	For Each oPingResult In cPingResults
		If IsNull(oPingResult.StatusCode) or oPingResult.StatusCode<>0 Then
			Ping = False
		Else
			Ping = True
		End If
	Next
End Function

Open in new window

0
 
jskfanAuthor Commented:
It did not retrieve all computers in Active Directory, we have 1564 computers in AD, but the script retrieved just 243, I also don't understand the display of the last column (Type)

Hostname	Operating System	Status	Type

Windows Server 2003	                 FALSE	<OFFLINE>
Windows Server 2003	                 FALSE	Physical
Windows Server 2003	                 FALSE	<OFFLINE>
Windows Server® 2008 Standard	FALSE	<OFFLINE>
Windows Server 2008 R2 Standard	FALSE	<OFFLINE>Windows Server 2008 R2 Standard	FALSE	Virtual
Windows Server 2008 R2 Standard	FALSE	Virtual
Windows Server® 2008 Enterprise	FALSE	<ERROR 462>
Windows Server 2003	                 TRUE	<OFFLINE>
Windows Server 2008 R2 Standard	FALSE	Virtual

Open in new window

0
 
jskfanAuthor Commented:
it is doing the same thing ..it retrieves only 243 computers..
0
 
RobSampsonCommented:
What are the errors you see in the last column?  You may need to check your credentials or WMI health on the remote machines.
0
 
jskfanAuthor Commented:
it is not about errors….it does not retrieve all computers in AD.
there are 1564 computers, it retrieves only 243.
0
 
jskfanAuthor Commented:
Sorry…I may have said on my question the OS = windows 2003 and windows 2008.
can you change the script for all computers OS
0
 
RobSampsonCommented:
Right, it only queries computers with the word "server" in their operating system.  To retrieve all computers, comment out lines 28 and 66 from the code snippet above.

Rob.
0
 
jskfanAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.