Re: Books/References new to scripting in AD/2003

Thanks again Richard. I have gone through your site and info a bit. I think
I have the "work in progress" version of this using the LDAP that you showed
earlier. I posted the SQL which was from a script that I found online
somewhere got me started.

I think I need to step back and really look at our current AD set up and
groupings to make sure they all make sense going forward. I might just have
to do some "manual" filtering of the data if I can't get it worked out in
the code.

Think I need to head to the bookstore to get that book and start toying
some....


"Richard Mueller [MVP]" <rlmueller-nospam@xxxxxxxxxxxxxxxxxxxx> wrote in
message news:OPkokprtIHA.4260@xxxxxxxxxxxxxxxxxxxxxxx
The filter I gave earlier (for enabled users) is for LDAP syntax (4
clauses separated by semicolons). You use SQL syntax below, which is more
familiar to many people. However, I don't believe there is any way to test
bits of flag attributes like userAccountControl using SQL syntax. Even if
you use SQL syntax, the provider converts it to LDAP syntax since that is
all AD understands.

The telephoneNumber attribute is string syntax (technically called
DirectoryString). Any value is allowed, but needs to be enclosed in quotes
in a query. The correct SQL syntax would be:

" WHERE objectCategory='person' AND objectClass='user' AND telephoneNumber
= '*'"

where * is the wildcard character. To restrict the query to an OU, modify
the base of the query. For example:

objCommand.CommandText = _
"SELECT givenName, SN, description, telephoneNumber FROM " _
& "'LDAP://ou=Sales,ou=West,dc=mops-ohio,dc=local' WHERE " _
& "objectCategory='person' AND objectClass='user' AND
telephonenumber='*'"

The equivalent LDAP syntax query would be (one line):

objCommand.CommandTest =
"<LDAP://ou=Sales,ou=West,dc=mops-ohio,dc=local>;(&(objectCategory=person)(objectClass=user));givenName,sn,description,telephoneNumber;subtree"

Only LDAP syntax can check userAccountControl to see if the disabled bit
is set or not. For example (again one line):

objCommand.CommandTest =
"<LDAP://ou=Sales,ou=West,dc=mops-ohio,dc=local>;(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2));givenName,sn,description,telephoneNumber;subtree"

There is no way to search several OU's or to exclude OU's from the search,
except to use a separate query for each OU. You can specify a base of the
search and then specify that the query includes all children of the base
(subtree). Or you can specify that only the base OU is to be searched and
not any children by specifying oneLevel instead of subtree. An alternative
is to query the entire domain and then when the resulting recordset is
enumerated parsing for the OU and only outputing when the parent OU of the
record meets your conditions.

I don't use (objectCategory=user) mostly because there is no such
objectCategory. The ADSI provider must convert this to
(objectCategory=person). In any case, it returns both user and contact
object, which may be what you want. If you want only user objects use the
filter:

(&(objectCategory=person)(objectClass=user))

If you want both user and contact objects use the filter:

(objectCategory=person)

The differences between SQL and LDAP syntax are explained in my earlier
link. For example in LDAP syntax the constant "user" is not quoted, but in
SQL syntax such constants are in single quotes.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

"Timothy Parker" <timparker@xxxxxxxxxxxx> wrote in message
news:482c5385$0$31751$4c368faf@xxxxxxxxxxxxxxxxx
Thanks Richard. I will check out that book and the links.

Here is the part of the script where it is pulling the info from AD.

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") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT givenName, SN, description, telephoneNumber FROM " _
& "'LDAP://dc=mops-ohio,dc=local' WHERE " _
& "objectCategory='user' and telephonenumber > 1"
Set objRecordSet = objCommand.Execute

I will read up on your site to change things around as needed. I just
want/need something that for now I can create and pretty up to be printed
out and left in our mail room. Eventually I will have this online for
users to view and print if they want based on sorting criteria.

Thanks.

Tim


"Richard Mueller [MVP]" <rlmueller-nospam@xxxxxxxxxxxxxxxxxxxx> wrote in
message news:uwDzBsptIHA.2292@xxxxxxxxxxxxxxxxxxxxxxx
Timothy Parker wrote:

Hello, I am new to the scripting world in an AD environment. I have
done some stuff relating to web servers/e-commerce. I am now in charge
of a 50+ user network and all its components. I am working on
cleaning/updating and organizing AD a bit and am looking to be able to
pull some reports out of this data for the office users (i.e.:
telephone directory, etc) I am looking for recommendations for good
books/whitepapers, websites, etc to help get me up to speed.

I have looked at the Scripting repository and have started to toy a bit
with some stuff there (script to pull AD users into an excel
Spread*** for one) but can't seem to figure out how to get only
active (non-disabled users).

Thanks for any help and guidance you can offer.

Besides the Technet Script Center, the best resource is "Microsoft
Windows 2000 Scripting Guide - Automating System Administration". I like
the hard copy text, but it is also available online at:

http://www.microsoft.com/technet/scriptcenter/guide/sagsas_overview.mspx?mfr=true

For tips on using ADO to search AD in scripts see this link:

http://www.rlmueller.net/ADOSearchTips.htm

For example, to filter on all non-disabled users:

strFilter = "(&(objectCategory=person)(objectClass=user)" _

& "(!userAccountControl:1.2.840.113556.1.4.803:=2))"



For documentation on Active Directory attributes see this link:



http://www.rlmueller.net/UserAttributes.htm


--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--








.