exporting contacts from AD

[nbowles]

I am trying to export contact records from AD with the following script. I'm not much of a scripter and I am getting errors on Line:36, Char:4, Error:Expected statement, Code:800A0400, Source: MS VBScript compilation error. The whole script is pasted below, but line 36 is

& " from '" & LDAPQUERY & "' where objectClass= 'user' order by sn " _

Any help is greatly appreciated.

*******
Script*
*******

Listing 1: ExportContacts.vbs

'User parameters.
'You must change FILENAME and LDAPQUERY constants according to your
'requirements before using this script.

'Filename for exporting data from AD.
const FILENAME= "d:\contacts.csv"

'LDAP query to AD, where
'DC_server is the DC server name and
'domainname is your domain name.
const LDAPQUERY= "LDAP://DC_server/DC=domainname,DC=ru"


Dim con, com, rs, fso, f

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fspenTextFile(FILENAME, 2, True) ' ForReading = 1, ForWriting = 2, ForAppending = 8

'Create the .csv file header for
'Outlook 2000 and Outlook Express.
f.Write "Last Name,First Name,Email address" & VbCrLf

'Connect to AD.
Set con = CreateObject("ADODB.Connection")
Set com = CreateObject("ADODB.Command")
con.Provider = "ADsDSOObject"
con.Open "Active Directory Provider"
Set com.ActiveConnection = con

'This query selects only unhidden users and contacts.
com.CommandText = "select givenname, sn,objectCategory,objectClass,"_
& "mail,msExchHideFromAddressLists" _

& " from '" & LDAPQUERY & "' where objectClass= 'user' order by sn " _
& " or objectClass= 'contact' order by sn"

com.Properties("Page Size") = 1000

'Run query to AD.

Set rs = com.Execute

rs.MoveFirst
While Not rs.EOF
Tmail = rs.Fields("mail")
Tsn = rs.Fields("sn")
TgivenName = rs.Fields("givenName")

'Write to the .csv filethe records
'that don't have empty <Last Name>,
'<First Name>, or <Email Address> fields.

If ((Tmail <> "" And Tsn <> "" And _
TgivenName <> "") And IsNull(rs.Fields("msExchHideFromAddressLists"))) Then

f.Write Tsn & "," & TgivenName & "," & Tmail & VbCrLf

End If
rs.MoveNext
Wend

rs.Close
f.Close

wscript.quit

 

[w33mhz]

have you tried using this:

com.CommandText = "select givenname, sn,objectCategory,objectClass,mail,msExchHideFromAddressLists from '" & LDAPQUERY & "' where objectClass= 'user' order by sn or objectClass= 'contact' order by sn"

Just make it one line. I did find there was another error as well at the :

Set rs = com.Execute

I am not sure on the syntax

 

[w33mhz]

I am not sure on the syntax or your com.CommandText

 

[markdmac]

Put your LDPAQUERY text within <>

The following example will query ad for all users:

'==========================================================================
'
' NAME: ListUsers.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL: [URL unfurl="true"]http://www.thespidersparlor.com[/URL]
' DATE  : 12/4/2007
' COPYRIGHT (c) 2007 All Rights Reserved
'
' COMMENT: 
'
'    THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
'    ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
'    THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
'    PARTICULAR PURPOSE.
'
'    IN NO EVENT SHALL THE SPIDER'S PARLOR AND/OR ITS RESPECTIVE SUPPLIERS 
'    BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY
'    DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
'    WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
'    ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE
'    OF THIS CODE OR INFORMATION.
'
'==========================================================================
On Error Resume Next

Dim qQuery, objConnection, objCommand, objRecordSet, obj
Dim oRootDSE, strDomain

Set oRootDSE = GetObject("LDAP://rootDSE")
strDomain = oRootDSE.get("defaultNamingContext")

' other categories = computer, user, printqueue, group
qQuery = "<LDAP://" & strDomain &">;" & _
		"(objectCategory=person)" & _
       ";name;subtree"

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Open "Provider=ADsDSOObject;"
objCommand.ActiveConnection = objConnection
objCommand.CommandText = qQuery
Set objRecordSet = objCommand.Execute

While Not objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields("name")
    
    objrecordset.MoveNext
Wend

objConnection.Close

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[nbowles]

I tried making it all one line as noted, but it then returns an error

Line: 1
Char: 1
Error: Type mismatch:'Listing'
Code: 800A000D
Source: Microsoft VBScript runtime error

 

[tsuji]

[1] Add a command object property.
[tt]
com.Properties("Page Size") = 1000
[blue]com.Properties("Sort On") = "sn"[/blue]
[/tt]
[2] Modify the query string.
[tt]
com.CommandText = "select givenname, sn,objectCategory,objectClass,"_
& "mail,msExchHideFromAddressLists" _
& " from '" & LDAPQUERY & "' where objectClass= 'user'" _
& " or objectClass= 'contact' order by sn"
[/tt]
And try again.

 

[tsuji]

[3] Also be explicit on the rs.fields value if it follows with extensive manipulation. That leaves less to chance.
[tt]
While Not rs.EOF
Tmail = rs.Fields("mail")[blue].value[/blue]
Tsn = rs.Fields("sn")[blue].value[/blue]
TgivenName = rs.Fields("givenName")[blue].value[/blue]
'etc etc
[/tt]

 

[tsuji]

Amendment
Incomplete editing in [2]. The corresponding line should be read as follow.
[tt]
com.CommandText = "select givenname, sn,objectCategory,objectClass,"_
& "mail,msExchHideFromAddressLists" _
& " from '" & LDAPQUERY & "' where objectClass= 'user'" _
& " or objectClass= 'contact'"
[/tt]

 

[nbowles]

Thanks for all of the replies. I have tried to make all of the changes you suggested tsuji, but I still get this error when I try to run it.

Line: 1
Char: 1
Error: Type mismatch:'Listing'
Code: 800A000D
Source: Microsoft VBScript runtime error

 

[tsuji]

You may not be showing all the elements for the forum's consideration. What is listing? (Line1, char 1) message probably results from so grossly erronous script? I don't know.

 

[nbowles]

The whole script was posted when I first submitted the request. As I stated earlier, I'm not much of scripter, just trying to make my life a bit easier though.

 

[EBGreen]

This line:

Listing 1: ExportContacts.vbs


Is not valid vbscript. If it is in the code that you are running get rid of it. Also if you use line continuation (_), the next line must be script not blank so this:

com.CommandText = "select givenname, sn,objectCategory,objectClass,"_
& "mail,msExchHideFromAddressLists" _

& " from '" & LDAPQUERY & "' where objectClass= 'user' order by sn " _
& " or objectClass= 'contact' order by sn"


Is not valid. It must be:

com.CommandText = "select givenname, sn,objectCategory,objectClass,"_
& "mail,msExchHideFromAddressLists" _
& " from '" & LDAPQUERY & "' where objectClass= 'user' order by sn " _
& " or objectClass= 'contact' order by sn"

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[tsuji]

Sure! If the script file itself include the line
>Listing 1: ExportContacts.vbs
as such, it is not correct! Take it out or comment it out by adding an apostroph at the beginning. But then I am very disappointed if we jump to do ldap query without knowing the most rudimentary vbs. Do people/companies do scripts without proper training/learning any more?!

 

[nbowles]

Thanks for the criticism!! I was under the impression that these forums were here to help. I was not hired to do scripting, but I am pushing forward in my career by accepting challenges however daunting and difficult they might be to expand my knowledge and potential worth in the future. It is really nice to know that you are perfectly trained in everything you do. Thanks for the advice.

 

[tsuji]

nbowles, I don't know you nothing. If you do not want to make reasonable effort, I take it personally that you are insulting the forum and trying to degrade this forum to a playground.

 

[nbowles]

tsuji thank you again for your insightful comments.