Solved

Vbscript Active Directory Query

Posted on 2010-08-29
6
840 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
  • 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
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…
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 from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

911 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

22 Experts available now in Live!

Get 1:1 Help Now