Joe's idea is much faster than a re-write, but you could change this line
objRecordSet.Fields("description").Value
And make it work. The description field is a multi-valued attribute. As
such, you would need to hold the data in an array and then loop through it.
Something like arrDescription = objRecordSet.Fields("description")
And then loop through it. (note you could add the .Value to the end to keep
with your coding, but it should work either way). A for..each loop would
likely do it.
I haven't tested that, but that's normally how I handle multi-valued
attributes like that.
Al
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of joe
Sent: Monday, November 22, 2004 12:17 PM
To: [EMAIL PROTECTED]
Subject: RE: [ActiveDir] Slightly OT: AD Scripting question - ADO query and
"description" field
Hmm can't really answer your questions well as I don't much like ADO and try
to avoid it but I have a couple of things that may help. Below find a perl
and vbscript example that I wrote up for something else. Something I noticed
when reading your query was a filter that was objectclass=computer. Unless
you have indexed objectclass (and probably even still) it is more efficient
to use objectcategory=computer.
Note the following stats dumps:
[Mon 11/22/2004 11:54:57.37]
G:\>adfind -gc -b -f objectclass=computer -stats+only
AdFind V01.24.00cpp Joe Richards ([EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> )
September 2004
Using server: 2k3dc01.joe.com
Directory: Windows Server 2003
Statistics
=================================
Elapsed Time: 411 (ms)
Returned 26 entries of 9818 visited - (0.26%)
Used Filter:
(objectClass=computer)
Used Indices:
DNT_index:8757:N
Analysis
---------------------------------
Hit Rate of 0.26% is Inefficient
No dedicated indices used for search, this is inefficient.
Indices used:
Index Name : DNT_index
Record Count: 8757 (estimate)
Index Type : Normal Attribute Index
Filter Breakdown:
(objectClass=computer)
[Mon 11/22/2004 11:55:42.46]
G:\>adfind -gc -b -f objectcategory=computer -stats+only
AdFind V01.24.00cpp Joe Richards ([EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> )
September 2004
Using server: 2k3dc01.joe.com
Directory: Windows Server 2003
Statistics
=================================
Elapsed Time: 20 (ms)
Returned 26 entries of 26 visited - (100.00%)
Used Filter:
(objectCategory=CN=Computer,CN=Schema,CN=Configuration,DC=joe,DC=com)
Used Indices:
idx_objectCategory:26:N
Analysis
---------------------------------
Hit Rate of 100.00% is Efficient
Indices used:
Index Name : idx_objectCategory
Record Count: 26 (estimate)
Index Type : Normal Attribute Index
Filter Breakdown:
(objectCategory=CN=Computer,CN=Schema,CN=Configuration,DC=joe,DC=com)
As promised here are the scripts....
Vbscript
strBase = "dc=joe,dc=com"
strFilter = "(&(objectcategory=person)(objectclass=user))"
strAttrs = "distinguishedName,displayName,memberOf"
strScope = "subtree"
set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
set objComm = CreateObject("ADODB.Command")
objComm.ActiveConnection = objConn
objComm.Properties("Page Size") = 1000
objComm.CommandText = "<LDAP://"; & strBase & ">;" & strFilter & ";" _
& strAttrs & ";" & strScope
set objRS = objComm.Execute
objRS.MoveFirst
on error resume next
while Not objRS.EOF
wscript.Echo "DN: " & objRS.Fields(0).Value
wscript.echo "Display Name: " & objRS.Fields(1).Value
wscript.echo "Group memberships"
for each group in objrs.fields(2).value
wscript.echo " > " & group
next
wscript.echo
objRS.MoveNext
wend
on error goto 0
Perl
use Win32::OLE;
use Win32::OLE::Enum;
use Win32::OLE 'in';
my $strBase = "dc=joe,dc=com";
my $strFilter = "(&(objectcategory=person)(objectclass=user))";
my $strAttrs = "distinguishedName,displayName,memberOf";
my $strScope = "subtree";
my $objConn = Win32::OLE->CreateObject("ADODB.Connection");
$objConn->{Provider} = "ADsDSOObject";
$objConn->Open("Active Directory Provider");
my $objComm = Win32::OLE->CreateObject("ADODB.Command");
$objComm->{ActiveConnection} = $objConn;
$objComm->{Properties}{"Page Size"} = 1000;
$objComm->{CommandText} =
"<LDAP://$strBase>;$strFilter;$strAttrs;$strScope";
my $objRS = $objComm->Execute();
$objRS->MoveFirst;
while (!$objRS->EOF())
{
print "DN: ".$objRS->Fields(0)->Value."\n";
print "Display Name: ".$objRS->Fields(1)->Value."\n";
foreach $group (in $objRS->Fields(2)->Value)
{
print " > $group\n";
}
print "\n";
$objRS->MoveNext();
}
________________________________
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Fuller, Stuart
Sent: Monday, November 22, 2004 11:46 AM
To: [EMAIL PROTECTED]
Subject: [ActiveDir] Slightly OT: AD Scripting question - ADO query and
"description" field
To the scripting gurus:
This one is kind of driving me nuts so any clarification on why this happens
would be greatly appreciated.
I recently created a script for one of our agency OU admins that queried the
AD for their workstations and returned name, distinguished name,
description, and some operating system details. The guts of the script are
shown below. What I found is that "description" is what I think is a
multi-variate field and the line "strDescrip =
objRecordSet.Fields("description").Value" barks at me. WSH returns "Type
mismatch code 800A000D" error.
I got around this by shimming in a call back to the original object and
adding in a return of ADSpath to the ADO query. I set the description
string via a GetObject call and I don't get any errors - "strDescrip =
GetObject(strADSPath).description".
My questions to the scripting gurus in the group are:
1. When doing an ADO query, how to you handle things that return arrays or
multi-variate attributes?
2. Is there something within the "objRecordSet.Fields..." bit that you can
turn on to force a single value or pick a value from an returned
multi-variate or array??
3. Why does an return from an ADO query be any different than a "GetObject"
return? Or in other words, why should description bark in an ADO query but
be fine in a normal GetObject?
Thanks,
Stuart Fuller
Sometimes cheesy scripting person
State of Montana
===============ADO query script ============== Const ADS_SCOPE_SUBTREE = 2
Set objConnection = CreateObject("ADODB.Connection") Set objCommand =
CreateObject("ADODB.Command") objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCOmmand.ActiveConnection = objConnection objCommand.CommandText = _
"Select Name, distinguishedName, description, operatingSystem,
operatingSystemServicePack, operatingSystemVersion from
'LDAP://ou=SomeOU,dc=ChildDomain,dc=RootDomain,dc=Root' " _ & "where
objectClass='computer'"
objCommand.Properties("Page Size") = 2000
objCommand.Properties("Timeout") = 60
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Cache Results") = False Set objRecordSet =
objCommand.Execute objRecordSet.MoveFirst Do Until objRecordSet.EOF strName
= objRecordSet.Fields("Name").Value strDescrip =
objRecordSet.Fields("description").Value
strOS = objRecordSet.Fields("operatingSystem").Value
strOSV = objRecordSet.Fields("operatingSystemVersion").Value
strOSSP = objRecordSet.Fields("operatingSystemServicePack").Value
strLocation = objRecordSet.Fields("distinguishedName").Value
fileTxt.WriteLine(strName & "," & strDescrip & "," & strOS & "," & strOSV &
"," & strOSSP & "," & strLocation) objRecordSet.MoveNext Loop wscript.echo
"DONE"
==========Bad fix to make it work=============
Do Until objRecordSet.EOF
strName = objRecordSet.Fields("Name").Value ==> strADSPath =
objRecordSet.Fields("ADSPath").Value
==>' Go get multi-valued description attribute from object using ADSpath ==>
strDescrip = GetObject(strADSPath).description strOS =
objRecordSet.Fields("operatingSystem").Value
strOSV = objRecordSet.Fields("operatingSystemVersion").Value
strOSSP = objRecordSet.Fields("operatingSystemServicePack").Value
strLocation = objRecordSet.Fields("distinguishedName").Value
fileTxt.WriteLine(strName & "," & strDescrip & "," & strOS & "," & strOSV &
"," & strOSSP & "," & strLocation) objRecordSet.MoveNext Loop
List info : http://www.activedir.org/mail_list.htm
List FAQ : http://www.activedir.org/list_faq.htm
List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/
