[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Vbscript Active Directory Query

Posted on 2010-08-29
6
Medium Priority
?
849 Views
Last Modified: 2012-05-10
There was a discussion by the real programmers at my job, which I am not, where they talked about how iteration should be avoided at all costs. Well I'm trying to query a specific OU to return the count of Windows XP Service Pack 3 computers. I for the life of me cannot think how this can be done without iteration. I mean you have to bind to each computer to get the operating system right? Am I missing something? Can this be done?
0
Comment
Question by:notta3d
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 1600 total points
ID: 33555946
Hi, first off....iteration is a necessary part of much automated programming....I don't see any reason why it should be avoided....it often makes tasks much easier!

In any case, this script will do the job for you.....by iterating through each computer object in the OU you specify.  The up side of this, the AD contains the attributes, so you don't actually need to bind to the computer as such....just run through the returned recordset.

Regards,

Rob.
If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /k cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If

strOU = "OU=TestOU,OU=MainOU,"
If Trim(strOU) = "" Then
	strOU = ""
Else
	If Right(strOU, 1) <> "," Then strOU = strOU & ","
End If

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

Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = "Select name, operatingSystem, operatingSystemServicePack from 'LDAP://" & strOU & strDNSDomain & "' where objectClass='computer'"  
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
Set objRecordSet = objCommand.Execute
While Not objRecordSet.EOF
	If LCase(objRecordSet.Fields("operatingSystem").Value) = LCase("Windows XP Professional") And LCase(objRecordSet.Fields("operatingSystemServicePack").Value) = LCase("Service Pack 3") Then WScript.Echo objRecordSet.Fields("name").Value
	objRecordSet.MoveNext
Wend
objRecordSet.Close

Open in new window

0
 
LVL 17

Assisted Solution

by:Tony Massa
Tony Massa earned 400 total points
ID: 33557926
Rob is correct..there isn't any reason why you shouldn't be able to query every object in AD, but he's also provided a search scope that will reduce the query time.  You should also use something like ADFIND (Joeware.net) to learn to effectively use LDAP filters to further reduce the effect of your queries:

Querying indexed attributes will improve query performance.  You make any attribute "indexed" by modifying the schema:
http://technet.microsoft.com/en-us/library/cc737526%28WS.10%29.aspx

Here's an ADFIND query to find indexed attributes in AD: (See Code Snippet)

To perform Robs query using ADFIND:
ADFIND -h DC1.yourdomain.com -b "OU=TestOU,OU=MainOU,dc=yourdomain,dc=com" -f "(&(objectCategory=Computer)(operatingSystem=Windows XP Professional)(operatingSystemServicePack=Service Pack 3))" -list name


ADFIND -h DC1.yourdomain.com -b "CN=Schema,CN=Configuration,DC=yourdomain,DC=com" -f "(&(objectCategory=attributeSchema)(searchFlags:1.2.840.113556.1.4.803:=1))" -list LDAPDisplayName

Open in new window

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 33563273
Good point tmassa....I should have included
where objectClass='computer' and operatingSystem='Windows XP Professional' AND operatingSystemServicePack='Service Pack 3'

and left out the If statement to check those properties.

Regards,

Rob.
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
LVL 1

Author Closing Comment

by:notta3d
ID: 33564212
Thanks guys. This was excellent. I was looping through and actually binding to each computer object in the OU to retrieve that property. It was taking longer than I liked. Now it's returning the count for about 1000 machines in like 2 seconds. Now I just need to write that info into an Access table and I'm done. The ADFIND looks very useful tmass, but also very cryptic. I'm going to try it out.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 33564343
No problem.  Thanks for the grade.

Regards,

Rob.
0
 
LVL 17

Expert Comment

by:Tony Massa
ID: 33565914
Once you learn the syntax, the filters apply to any LDAP tool.  Microsoft's ADUC had the "saved search" utility that you can use to learn how the filters work.

As far as ADFIND goes, it should be mandatory learning for  anyone working with AD.  
0

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Question has a verified solution.

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

Always backup Domain, SYSVOL etc.using processes according to Microsoft Best Practices. This is meant as a disaster recovery process for small environments that did not implement backup processes and did not run a secondary domain controller that ne…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

656 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