Extract User Data To a CSV File (Inc Terminal Services Attributes)

Posted on by David

Extract user data from Active Directory as a CSV file. Handling for terminal services attributes is included.

Notes

This script expands on the Extract User Data to a CSV script by adding support for terminal services attributes.  Terminal services attributes are actually stored in the userparameters attribute in Active Directory.  Reading from this value directly is not supported.  The IADsTSUserEx interface can be used to read terminal services attributes, but it does require that we bind to each user account when running the search.  You will notice that the search runs a lot slower when terminal services attributes are included - If they are not required, you might want to remove them from the list of attributes in the setup section. Note: This script makes use of the IADsTSUserEx interface.  You will need to install the Windows 2000/2003 Administration Tools Pack to allow the script to work.

Extract User Data from Active Directory to CSV with term services
Option Explicit

dim FileName, multivaluedsep,strAttributes
dim strFilter, strRoot, strScope
dim cmd, rs,cn
Dim objRoot, objFSO,objCSV
Dim comma, q, i, mvsep, strAttribute, strValue,strSearchAttributes
dim objUser

' ********************* Setup *********************

' The filename of the csv file produced by this script
FileName ="userexport.csv"
' Seperator used for multi-valued attributes
multivaluedsep = ";"
' comma seperated list of attributes to export
strAttributes = "sAMAccountName,givenName,initials,sn,displayName,description,physicalDeliveryOfficeName," & _
		"telephoneNumber,mail,wWWHomePage,cn,terminalservicesprofilepath,terminalserviceshomedrive,terminalserviceshomedirectory,allowlogon"

' Default filter for all user accounts (ammend if required)
strFilter = "(&(objectCategory=person)(objectClass=user))"
' scope of search (default is subtree - search all child OUs)
strScope = "subtree"
' search root. e.g. ou=MyUsers,dc=wisesoft,dc=co,dc=uk
' leave blank to search from domain root
strRoot = ""

' *************************************************

q = """"
comma = "" ' first column does not require a preceding comma
i = 0 
Set objFSO = createobject("Scripting.FileSystemObject")
Set objCSV = objFSO.createtextfile(FileName)

' Create CSV header row and get attributes to use in search
For Each strAttribute In SPLIT(strAttributes,",")
	Select Case LCASE(strAttribute)
		Case "terminalservicesprofilepath","terminalserviceshomedrive","terminalserviceshomedirectory","allowlogon","manager_samaccountname"
			' Terminal services attributes are stored in the userparameters attribute and can be read individually
			' via the IADsTSUserEx interface. This requires us to bind to each user account returned by the search (slow)
			' Add the "adspath" attribute to allow us to bind to the user account where terminal services attributes are 
			' specified
			If INSTR(1,strSearchAttributes,"adspath",1) = 0 Then ' Check if we don't already have adspath attribute
				IF strSearchAttributes <> "" Then
					strSearchAttributes = strSearchAttributes & ","
				End If
				strSearchAttributes = strSearchAttributes & "adspath"
			End If
		Case Else
			' Append attribute to the search attributes
			If strSearchAttributes <> "" Then
				strSearchAttributes = strSearchAttributes & ","
			End If
			strSearchAttributes = strSearchAttributes & strAttribute
	END Select
	' Write CSV File Header
	objcsv.write(comma & q & strAttribute & q)
	comma = "," ' all columns apart from the first column require a preceding comma
	i = i + 1
Next

set cmd = createobject("ADODB.Command")
set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")

cn.open "Provider=ADsDSOObject;"
cmd.activeconnection = cn

' If root = "", use the default naming context (current domain)
if strRoot = "" then
	set objRoot = getobject("LDAP://RootDSE")
	strRoot = objRoot.get("defaultNamingContext") 
end if

cmd.commandtext = "<LDAP://" & strRoot & ">;" & strFilter & ";" & _
		  strSearchAttributes & ";" & strScope

'**** Bypass 1000 record limitation ****
cmd.properties("page size")=1000

set rs = cmd.execute

' for each item returned by the Active Directory query
while rs.eof <> true and rs.bof <> True
	Set objUser = Nothing ' Used only for terminal services attributes
	comma="" ' first column does not require a preceding comma
	objcsv.writeline ' Start a new line
	' For each column in the result set
	for each strAttribute in SPLIT(strAttributes,",")
		select case strAttribute
			case "terminalservicesprofilepath"
				' Bind to user account if required (only bind once per user if more than 1 
				' terminal services attribute is specified)
				If objUser Is Nothing Then
					Set objUser = GETOBJECT(rs("adspath"))
				End If
				objCSV.Write(comma & q & replace(objUser.TerminalServicesProfilePath,q,q & q) & q)
			case "terminalserviceshomedrive"
				' Bind to user account if required (only bind once per user if more than 1 
				' terminal services attribute is specified)
				IF objUSer IS NOTHING Then
					SET objUser = GETOBJECT(rs("adspath"))
				End If
				objCSV.Write(comma & q & objUser.TerminalServicesHomeDrive & q)
			case "terminalserviceshomedirectory"
				' Bind to user account if required (only bind once per user if more than 1 
				' terminal services attribute is specified)
				IF objUSer IS NOTHING Then
					SET objUser = GETOBJECT(rs("adspath"))
				End If
				objCSV.Write(comma & q & replace(objUser.TerminalServicesHomeDirectory,q,q & q) & q)
			case "allowlogon"
				' Bind to user account if required (only bind once per user if more than 1 
				' terminal services attribute is specified)
				IF objUSer IS NOTHING Then
					SET objUser = GETOBJECT(rs("adspath"))
				End If
				objCSV.Write(comma & q & objUser.AllowLogon & q)
			case "manager_samaccountname"
				' Bind to user account if required (only bind once per user if more than 1 
				' terminal services attribute is specified)
				IF objUSer IS NOTHING Then
					SET objUser = GETOBJECT(rs("adspath"))
				End If
				dim objManager, strManagerDN, strManager
				strManager=""
				on error resume next
				strManagerDN = objUser.Get("manager")
				on error goto 0
				if strManagerDN <> "" then
					set objManager = GETOBJECT("LDAP://" & strManagerDN)
					strManager = objManager.sAMAccountName
				end if
				objCSV.Write(comma & q & strManager & q)
			case else
				select case typename(rs(strAttribute).value)
					case "Null" ' handle null value
						objcsv.write(comma & q & q)
					case "Variant()" ' multi-valued attribute
						' Multi-valued attributes will be seperated by value specified in
						' "multivaluedsep" variable
						mvsep = "" 'No seperator required for first value
						objcsv.write(comma & q)
						for each strValue in rs(strAttribute).Value
							' Write value
							' single double quotes " are replaced by double double quotes ""
							objcsv.write(mvsep & replace(strValue,q,q & q))
							mvsep = multivaluedsep ' seperator used when more than one value returned
						next
						objcsv.write(q)
					case else
						' Write value
						' single double quotes " are replaced by double double quotes ""
						objcsv.write(comma & q & replace(rs(strAttribute).value,q,q & q) & q)
				end select
		end select
		
		comma = "," ' all columns apart from the first column require a preceding comma
	next
	rs.movenext
wend

' Close csv file and ADO connection
cn.close
objCSV.Close

wscript.echo "Finished"
Posted in VBScript, Active Directory