[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Querying Active Directory for computers

Posted on 2013-10-25
21
Medium Priority
?
413 Views
Last Modified: 2013-11-08
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
0
Comment
Question by:jskfan
  • 9
  • 7
  • 2
  • +3
21 Comments
 
LVL 124

Assisted Solution

by:Andrew Hancock (VMware vExpert / EE MVE^2)
Andrew Hancock (VMware vExpert / EE MVE^2) earned 200 total points
ID: 39602606
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
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 200 total points
ID: 39602797
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
 
LVL 18

Assisted Solution

by:irweazelwallis
irweazelwallis earned 200 total points
ID: 39603144
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 51

Assisted Solution

by:Netman66
Netman66 earned 200 total points
ID: 39603164
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
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 1200 total points
ID: 39603703
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
 

Author Comment

by:jskfan
ID: 39628963
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
 

Author Comment

by:jskfan
ID: 39628968
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
 
LVL 124
ID: 39628987
You may want to change the string from Virtual to VMware
0
 

Author Comment

by:jskfan
ID: 39628993
changed it to VMware it did not work
0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 1200 total points
ID: 39629076
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
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 1200 total points
ID: 39629081
It should still work with
                        strType = "Virtual"
0
 

Author Comment

by:jskfan
ID: 39631466
AD
the error is pointing to this line:
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputerName & "\root\cimv2")
0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 1200 total points
ID: 39631910
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
 

Author Comment

by:jskfan
ID: 39632040
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
 
LVL 65

Accepted Solution

by:
RobSampson earned 1200 total points
ID: 39632110
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
 

Author Comment

by:jskfan
ID: 39632282
it is doing the same thing ..it retrieves only 243 computers..
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39632291
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
 

Author Comment

by:jskfan
ID: 39632643
it is not about errors….it does not retrieve all computers in AD.
there are 1564 computers, it retrieves only 243.
0
 

Author Comment

by:jskfan
ID: 39632647
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
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 1200 total points
ID: 39632654
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
 

Author Closing Comment

by:jskfan
ID: 39634256
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When rebooting a vCenters 6.0 and try to connect using vSphere Client we get this issue "Invalid URL: The hostname could not parsed." When we get this error we need to do some changes in the vCenter advanced settings to fix the issue.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question