Solved

Vbscript Active Directory Query

Posted on 2010-08-29
6
847 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 400 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 100 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes my battle tested process for setting up delegation. I use this process anywhere that I need to setup delegation. In the article I will show how it applies to Active Directory
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

688 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