Query the Active Directory

In this lesson we will look at the script parts that make up the working solution.

Published

Goal

Goal is to show how to detect the different stages when querying.

Steps

Complete script

Visual Basic

option explicit

'Declarations

' Machine or domain name 
const DC_machine    = "enx-ota.local"

' filter to find the correct groups
const GROUP_FILTER  = "G-UG-*"

' base distinguishedname where the groups are to be searched using the filter above
const GROUP_BASE_OU = "OU=Groups" 

' page size: how many members should be retrieved in one page
const pageSize = 1500

' ------------------------------------------------------------------------------------------------------------------------
' DO NOT MAKE CHANGES BELOW THIS LINE
' ------------------------------------------------------------------------------------------------------------------------

' setup some globals
dim LDAP_PREFIX,GROUP_LDAP_FILTER
dim fsObj, filetxt 

' LDAP and file constants
Const ForReading = 1, ForWriting = 2, ForAppending = 8 
Const ADS_SCOPE_SUBTREE = 2

' construct some globals based on constants
LDAP_PREFIX = "LDAP://" & DC_machine & "/"
GROUP_LDAP_FILTER = "(&(objectClass=group)(cn=" & GROUP_FILTER & "))" 
 
' ------------------------------------------------------------------------------------------------------------------------
'  START OF MAIN CODE PART 1
' ------------------------------------------------------------------------------------------------------------------------

' open output file and write header
set fsObj=CreateObject("Scripting.FileSystemObject")
Set filetxt = fsObj.OpenTextFile("results.txt" , ForWriting,true, -1)  ' -1 = UNICODE

filetxt.writeline("sAMAccountName|memberDN|OrganisatieEenheid")

' ------------------------------------------------------------------------------------------------------------------------
' Function : list the member-attribute
' ------------------------------------------------------------------------------------------------------------------------
function ListMembers(strADSPath)
  dim fldlist,sql,objCmd,objConnection,objRecordSet, filter, strSAMAccountName

  ' setup ADO connection to Active Directory
  Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Provider = "ADsDSOObject"
  objConnection.Open "Active Directory Provider"
  
  Set objCmd = CreateObject("ADODB.Command")
  Set objCmd.ActiveConnection = objConnection
  
  dim finished
  dim startAtNumber, requestedItemCount

  finished = false
  startAtNumber = 0
  requestedItemCount = "*" 
  
  while not finished
    wscript.echo "***** range requested : " & startAtNumber & " - "  & requestedItemCount
	
	  ' determine field list. you can edit this line
	  fldlist= "adspath,sAMAccountName,distinguishedname,description" 
	  
	  ' do not modify this line. it will setup range parameter
	  ' !!notice: range parameters must be at the END of the field list !!
	  if requestedItemCount <> "*" then 
	    fldlist = fldlist & ",member;range=" & startAtNumber & "-" & startAtNumber + requestedItemCount-1
	  else
	    fldlist = fldlist & ",member;range=" & startAtNumber & "-*"
	  end if

	  ' setup the ADO query : <adspath>;filter;fieldlist;searchbase 
	  ' because we use the ADSPath (full Distinguishedname), the search base must be 'base'
	  sql = "<"  & strADSPath &  ">;(objectClass=*);" & fldlist & ";base"
	  objCmd.CommandText = sql
	  
	  wscript.echo "- fldlist : "  & fldlist

	  ' setup other ADO parameters
	  objCmd.Properties("Page Size") = 100 ' this is a different page size than the range parameter!
	  objCmd.Properties("Timeout") = 30
	  objCmd.Properties("Cache Results") = False
	  
	  ' execute the ADO query to Active Directory
	  Set objRecordSet = objCmd.Execute
	  
	  ' goto first record
	  objRecordSet.MoveFirst

	  ' we need to count how many records are retrieved for later use 
	  dim numItems 
	  numItems = 0

	  ' cycle found group-records
	  if not (objRecordSet.EOF) then
		dim objDescription, strDescription, descr

		strSAMAccountName = objRecordSet.fields("sAMAccountName" ).value
		wscript.echo "strSAMAccountName : " & strSAMAccountName

		' description is multi value; get last value
		objDescription = objRecordSet.fields("description" ).value
		for each strDescription in objDescription
		  descr = strDescription
		next
		
		dim memberFieldName, fld

		' find the field 'member' . Because we use RANGE-parameter, the field is returned with the range in the fieldname. 
		' e.g. "member;range=0-500" . so check all the fields until we find the member field.
		for each fld in objRecordSet.fields
		  if left(fld.name,7)="member;" then
			memberFieldName = fld.name
			exit for
		  end if 
		next 
		
		' now check the type of 'member' field.
		' depending on the number of values it is either NULL (no values) or variant(an array of values)
		dim strValueTypename
		strValueTypename = TypeName(objRecordSet.fields(memberFieldName).value)
		
		wscript.echo "Result : "  & strValueTypename
		
		if strValueTypename = "Variant()" then 
			' there are values reported. start listing them
			dim strVal,arrValues
			arrValues = objRecordSet.fields(memberFieldName).value
			
			for each strVal in arrValues
			  dim objMember
			  set objMember = GetObject("LDAP://" & strVal)
			  
			  filetxt.writeline( QuoteString(strSAMAccountName) & "|" & QuoteString(strVal) & "|" & QuoteString(descr)) & "|" & QuoteString(objmember.sAMAccountName)& "|" & QuoteString(objmember.description) & "|" & QuoteString(objmember.employeeId)
			  numItems = numItems + 1
			next 
			
			wscript.echo "Retrieved : " & numItems
			
			' if the number of items is less than requested, we're at the end
			finished = (numItems < pageSize)
			
			' if not finished then increase startAtNumber 
			startAtNumber      = startAtNumber + numItems
			requestedItemCount = pageSize
		elseif (strValueTypename = "Null") and (requestedItemCount <>"*" ) and (startAtNumber = 0) then 
		 'we requested not to retrieve everything (e.g. range=500-*) and it is the first call (startAtNumber=0) 
		 'no results are found, so there are no members
		 finished = true
		elseif (strValueTypename = "Null") and (requestedItemCount <>"*" ) then 
		   ' no values are returned if NULL is reported as the type. 
		   ' BUT: if the requested item count is not "*" (give the rest) then we need to call again to get the last chunk
		   startAtNumber = startAtNumber + numItems
		   requestedItemCount = "*"
		elseif (strValueTypename = "Null") and (requestedItemCount="*") and (startAtNumber = 0) then
		   'the first call would deliver more items than the AD will yield based on the limits. so start paging
		   requestedItemCount = pageSize
		else
		  finished = true
		end if
	  else  
	    finished = true
	  end if
  wend
  ListMembers = numItems
end function 'ListMembers 

' ------------------------------------------------------------------------------------------------------------------------
' Function : Put quotes around a string
' ------------------------------------------------------------------------------------------------------------------------
function QuoteString(str) 
  QuoteString = chr(34) & str & chr(34)
end function

' ------------------------------------------------------------------------------------------------------------------------
' Main code
' ------------------------------------------------------------------------------------------------------------------------

' Declare variables
Dim objOU, objUser, objRootDSE
Dim strContainer, strLastUser, strDNSDomain, intCounter, objRecordSet
Dim objConnection,objCmd,fldlist,strTarget, sql

' find rootDSE
Set objRootDSE = GetObject(LDAP_PREFIX & "RootDSE")
strDNSDomain = objRootDSE.Get("DefaultNamingContext")

' Construct a basic LDAP scope (domain, base DN)
strTarget = LDAP_PREFIX & GROUP_BASE_OU & "," & strDNSDomain
fldlist= "distinguishedname,adspath"

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCmd = CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConnection
sql = "<" & strTarget & ">;" & GROUP_LDAP_FILTER & ";" & fldlist & ";subtree"
objCmd.CommandText = sql

objCmd.Properties("Page Size") = 100
objCmd.Properties("Timeout") = 30
objCmd.Properties("Cache Results") = False

Set objRecordSet = objCmd.Execute
objRecordSet.MoveFirst

if not ( objRecordSet.EOF   ) then
  while not objRecordSet.EOF
    ' list the members for this group. first try arbitrary number of records. 
	ListMembers objRecordSet.fields("adspath").value
	objRecordSet.movenext
  wend
end if


Summary

-


Tags


Copyright 2013 Martin Molema