RE: [ActiveDir] Slightly OT: AD Scripting question - ADO query and "description" field

[joe]

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]) 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]) 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