November 2009 - wiseDATAman

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.

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"

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

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

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

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

comma = "," ' all columns apart from the first column require a preceding comma

rs.movenext

wend

' Close csv file and ADO connection

cn.close

objCSV.Close

wscript.echo "Finished"

Monthly Archives: November 2009

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