Solved

Querying Active Directory for computers

Posted on 2013-10-25
21
397 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 118

Assisted Solution

by:Andrew Hancock (VMware vExpert / EE MVE)
Andrew Hancock (VMware vExpert / EE MVE) earned 50 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 50 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 50 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
 
LVL 51

Assisted Solution

by:Netman66
Netman66 earned 50 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 300 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 118
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 300 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
Are your corporate email signatures appalling?

Is it scary how unprofessional your email signatures look? Do users create their own terrible designs and give themselves stupid job titles? You can make this a lot easier for yourself by choosing an email signature management solution from Exclaimer today.

 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 300 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 300 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 300 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 300 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When we have a dead host and we lose all connections to the ESXi, and we need to find a way to move all VMs from that dead ESXi host.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
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…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now