Error Checking in AD Query

TheMSsForum.com: The Microsoft Software Forum

Hello
I've been trying to query AD to return the truncated domain name from a
list of users in a text file, based on the 'name' of the user. I'm very
new to scripting so at this stage I just piece together other's hard
work and try to make it happen! I seem to be able to return the domain
name o.k., but I'm missing around 40 names in a list of over 700,
consequently I can't match up each username with a domain name, so the
result is next to useless. I suspect that some of the usernames may be
invalid, but without an error checking routine I'm can't be sure. Does
anyone have a way of error checking the results of an AD query? I've
posted my script here, apologies if it's a bit scrambled, I've had
several attempts. Any help appreciated.

Const ADS_SCOPE_SUBTREE = 2
Const ForWriting = 2
Const ForReading = 1

LogFile = "C:\query\fso_initial.txt"
FinalFile = "C:\query\fso_final.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(LogFile, ForReading)

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

' *** Open input text file and read through values

strLine = objFile.ReadAll
objFile.Close

arrDN = Split(strLine, vbCrLf)
For x = 0 To UBound(arrDN)
strLine = Trim(arrDN(x))
Next

' *** End Text File Read


Set objFile = objFSO.OpenTextFile(FinalFile, ForWriting)
For Each strItem In arrDN

' *** AD Query

objCommand.CommandText = _
"SELECT distinguishedName FROM 'GC://dc=SOMEDOMAIN,dc=net' WHERE
objectCategory='user' " & _
"AND name = '" & strItem & "'"

On Error Resume Next
Set objRecordSet = objCommand.Execute

Do Until objRecordSet.EOF

strDN = objRecordSet.Fields("distinguishedName").Value
objUser = Right(objRecordSet.Fields("distinguishedName").Value,
29)

' *** Write back to Text File

objFile.Writeline objUser
objRecordSet.MoveNext
Loop
Next
objFile.Close
Top

RE: Error Checking in AD Query by MatchStick

MatchStick
Tue Nov 07 03:32:01 CST 2006

You could use something like this to test if any records were returned:

If objRecordSet.EOF Then
objFile.Writeline "The user was not found in the directory."
Else
strDN = objRecordSet.Fields("distinguishedName")
objFile.Writeline strDN
End If

"zendokai" wrote:

> Hello
> I've been trying to query AD to return the truncated domain name from a
> list of users in a text file, based on the 'name' of the user. I'm very
> new to scripting so at this stage I just piece together other's hard
> work and try to make it happen! I seem to be able to return the domain
> name o.k., but I'm missing around 40 names in a list of over 700,
> consequently I can't match up each username with a domain name, so the
> result is next to useless. I suspect that some of the usernames may be
> invalid, but without an error checking routine I'm can't be sure. Does
> anyone have a way of error checking the results of an AD query? I've
> posted my script here, apologies if it's a bit scrambled, I've had
> several attempts. Any help appreciated.
>
> Const ADS_SCOPE_SUBTREE = 2
> Const ForWriting = 2
> Const ForReading = 1
>
> LogFile = "C:\query\fso_initial.txt"
> FinalFile = "C:\query\fso_final.txt"
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objFile = objFSO.OpenTextFile(LogFile, ForReading)
>
> Set objConnection = CreateObject("ADODB.Connection")
> Set objCommand = CreateObject("ADODB.Command")
> objConnection.Provider = "ADsDSOObject"
> objConnection.Open "Active Directory Provider"
> Set objCommand.ActiveConnection = objConnection
>
> objCommand.Properties("Page Size") = 1000
> objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
>
> ' *** Open input text file and read through values
>
> strLine = objFile.ReadAll
> objFile.Close
>
> arrDN = Split(strLine, vbCrLf)
> For x = 0 To UBound(arrDN)
> strLine = Trim(arrDN(x))
> Next
>
> ' *** End Text File Read
>
>
> Set objFile = objFSO.OpenTextFile(FinalFile, ForWriting)
> For Each strItem In arrDN
>
> ' *** AD Query
>
> objCommand.CommandText = _
> "SELECT distinguishedName FROM 'GC://dc=SOMEDOMAIN,dc=net' WHERE
> objectCategory='user' " & _
> "AND name = '" & strItem & "'"
>
> On Error Resume Next
> Set objRecordSet = objCommand.Execute
>
> Do Until objRecordSet.EOF
>
> strDN = objRecordSet.Fields("distinguishedName").Value
> objUser = Right(objRecordSet.Fields("distinguishedName").Value,
> 29)
>
> ' *** Write back to Text File
>
> objFile.Writeline objUser
> objRecordSet.MoveNext
> Loop
> Next
> objFile.Close
>
>
Top

Re: Error Checking in AD Query by zendokai

zendokai
Tue Nov 07 04:48:47 CST 2006

Thanks MatchStick, that works a treat! Helps if you know what you're
talking about. I'll certainly be using that in the future.

Cheers


MatchStick wrote:
> You could use something like this to test if any records were returned:
>
> If objRecordSet.EOF Then
> objFile.Writeline "The user was not found in the directory."
> Else
> strDN = objRecordSet.Fields("distinguishedName")
> objFile.Writeline strDN
> End If
>
> "zendokai" wrote:
>
> > Hello
> > I've been trying to query AD to return the truncated domain name from a
> > list of users in a text file, based on the 'name' of the user. I'm very
> > new to scripting so at this stage I just piece together other's hard
> > work and try to make it happen! I seem to be able to return the domain
> > name o.k., but I'm missing around 40 names in a list of over 700,
> > consequently I can't match up each username with a domain name, so the
> > result is next to useless. I suspect that some of the usernames may be
> > invalid, but without an error checking routine I'm can't be sure. Does
> > anyone have a way of error checking the results of an AD query? I've
> > posted my script here, apologies if it's a bit scrambled, I've had
> > several attempts. Any help appreciated.
> >
> > Const ADS_SCOPE_SUBTREE = 2
> > Const ForWriting = 2
> > Const ForReading = 1
> >
> > LogFile = "C:\query\fso_initial.txt"
> > FinalFile = "C:\query\fso_final.txt"
> > Set objFSO = CreateObject("Scripting.FileSystemObject")
> > Set objFile = objFSO.OpenTextFile(LogFile, ForReading)
> >
> > Set objConnection = CreateObject("ADODB.Connection")
> > Set objCommand = CreateObject("ADODB.Command")
> > objConnection.Provider = "ADsDSOObject"
> > objConnection.Open "Active Directory Provider"
> > Set objCommand.ActiveConnection = objConnection
> >
> > objCommand.Properties("Page Size") = 1000
> > objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
> >
> > ' *** Open input text file and read through values
> >
> > strLine = objFile.ReadAll
> > objFile.Close
> >
> > arrDN = Split(strLine, vbCrLf)
> > For x = 0 To UBound(arrDN)
> > strLine = Trim(arrDN(x))
> > Next
> >
> > ' *** End Text File Read
> >
> >
> > Set objFile = objFSO.OpenTextFile(FinalFile, ForWriting)
> > For Each strItem In arrDN
> >
> > ' *** AD Query
> >
> > objCommand.CommandText = _
> > "SELECT distinguishedName FROM 'GC://dc=SOMEDOMAIN,dc=net' WHERE
> > objectCategory='user' " & _
> > "AND name = '" & strItem & "'"
> >
> > On Error Resume Next
> > Set objRecordSet = objCommand.Execute
> >
> > Do Until objRecordSet.EOF
> >
> > strDN = objRecordSet.Fields("distinguishedName").Value
> > objUser = Right(objRecordSet.Fields("distinguishedName").Value,
> > 29)
> >
> > ' *** Write back to Text File
> >
> > objFile.Writeline objUser
> > objRecordSet.MoveNext
> > Loop
> > Next
> > objFile.Close
> >
> >

Top