Re: LDAP query information

Hi Richard,
Can you just help me with this last query given below?

Thanks,

"Vishn" wrote:

Hi Richard,
BINGO! Its working fine now. Thanks to your effort. You are the best!
I just redirected the output to a Csv file & the output was displayed
with all the informations except for the machine role which may need some
tweaking but thats fine.
The output displayed is cluttered & is seperated by the delimiter ";" . Can
the script be modified so that the output is displayed as seperate fields in
Excel for seperate attributes of the "computer" object?
Also, the 'SAM account name' string ends with the "$" symbol like
'ausx2k3bl$'.Is there a way where the $ symbol could be removed?
Lastly, I would like to learn scripting. I saw your website & saw the list
of prerequisites. But is the knowledge of VBScript essential before going
through the scripting books you have mentioned? Please do guide me so that it
will make work easier for our IT dept by automating things!

You have given a fantastic service anyone could have hoped for! I would like
to send an appreciation mail for your work apart from rating your post! Can
you give me an email address of your reporting contact/manager(maybe with
Microsoft) where in I can send them.

Thanks for your efforts & time.

I really appreciate your help in solving this issue.




"Richard Mueller" wrote:

It turns out that the description attribute is also multi-valued, so it must
handled in the same way as canonicalName, as an array (even though there is
never more than one value). All of my computer objects had no description,
so this error did not show up for me. Replace the line:

strDesc = adoRecordset.Fields("description").Value & ""

with the following:
=============
Dim arrDesc

arrDesc = = adoRecordset.Fields("description").Value & ""
If (TypeName(arrDesc) = "Variant()") Then
strDesc = arrDesc(0)
Else
strDesc = ""
End If
=============
because I use "Option Explicit" I must declare the new variable, arrDesc, in
a "Dim" statement. You can place this new Dim statement at the beginning
with the others.

The //nologo parameter I referred to just suppresses logo information when a
script is run. You don't need to use it, it makes no difference in the
execution of the script. I use it because I like to redirect the output of
scripts to a text file, and I don't want the logo information to clutter up
the output file.

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

"Vishn" <Vishn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FC8290F3-CECC-427C-A688-37611185C544@xxxxxxxxxxxxxxxx
Hi Richard,
Can you please provide me info on what to do with the below query?

Appreciate your help!


"Vishn" wrote:

I ran the script & got one more type mismatch error:

C:\Richardscript>cscript //nologo queryAD.vbs >output.txt
C:\Richardscript\queryAD.vbs(52, 6) Microsoft VBScript runtime error:
Type
misma
tch

C:\Richardscript>

Error mismatch on the 52nd line which is
strDesc = adoRecordset.Fields("description").Value & ""
The 51st line was
strDN = adoRecordset.Fields("distinguishedName").Value
So, I suppose the DistinguisedName attribute worked fine(No error msgs)
Do I have to put the If -- End if condition for this one too? If so, what
about the declaration part. If I get the same error msg on the other
lines,
Do I have to do the same conditional statements. Apologise for my
ignorance.
What does the //nologo switch do? Will it make a difference in the
execution of the vbscript?

Thanks for your help!

"Richard Mueller" wrote:

Hi,

The error message indicates the line number in the script, in this case
57.
Most likely there is a missing value, which results in a Null, which
causes
the type mismatch. The way I fix this is to append a blank string, "",
to
the values retrieved. For example:
================
Do Until adoRecordset.EOF
strNTName = adoRecordset.Fields("sAMAccountName").Value
arrCanonical = adoRecordset.Fields("canonicalName").Value
If (TypeName(arrCanonical) = "Variant()") Then
strCanonical = arrCanonical(0)
Else
strCanonical = ""
End If
strDN = adoRecordset.Fields("distinguishedName").Value
strDesc = adoRecordset.Fields("description").Value & ""
strRole = adoRecordset.Fields("machineRole").Value & ""
strManaged = adoRecordset.Fields("managedBy").Value & ""
strLocation = adoRecordset.Fields("location").Value & ""
strOS = adoRecordset.Fields("operatingSystem").Value & ""
strOSVer = adoRecordset.Fields("operatingSystemVersion").Value &
""
strSP = adoRecordset.Fields("operatingSystemServicePack").Value &
""
strCreated = adoRecordset.Fields("whenCreated").Value & ""
strChanged = adoRecordset.Fields("whenChanged").Value & ""
Wscript.Echo strNTName & ";" & strCanonical & ";" & strDN _
& ";" & strDesc & ";" & strRole & ";" & strManaged _
& ";" & strLocation & ";" & strOS & ";" & strOSVer _
& ";" & strSP & ";" & strCreated & ";" & strChanged
adoRecordset.MoveNext
Loop
============
The sAMAccountName is required so cannot be missing. The canonicalName
attribute is bit strange. It is multi-valued, although I think there is
always only one value. It is retrieved by ADO as an array. Just to be
safe,
I check if it is a variant array above. I hope this helps.

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

"Vishn" <Vishn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F4CE4254-3A67-4533-9F54-618E9858DB65@xxxxxxxxxxxxxxxx
Hi Richard,
Can you please help me with the below error message I received
when I
executed the VBscipt which you provided?

Appreciate your help!


"Vishn" wrote:


It gave me the below error message when I ran the script.I suppose
its
some
issue with the Type declaration.
--------------------------------------------------
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

O:\>c:

C:\>cd compaccts

C:\compaccts>cscript compaccts.vbs

C:\compaccts>cscript //nologo compaccts.vbs >report.txt
C:\compaccts\compaccts.vbs(57, 5) Microsoft VBScript runtime error:
Type
mismatc
h

C:\compaccts>
------------------------------------------------------------------------
This is how I had saved the script
Option Explicit

Dim objRootDSE, strDNSDomain, adoCommand, adoConnection
Dim strBase, strFilter, strAttributes, strQuery, adoRecordset
Dim strNTName, lngUSN, arrCanonical, strCanonical
Dim strDN, strDesc, strRole, strManaged, strOS, strOSVer
Dim strSP, strLocation, strCreated, strChanged

' Determine DNS domain name.
Set objRootDSE = GetObject("LDAP://RootDSE";)
strDNSDomain = objRootDSE.Get("defaultNamingContext")

' Use ADO to search Active Directory.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection

' Search entire domain.
strBase = "<LDAP://"; & strDNSDomain & ">"

' Filter on computer object.
strFilter = "(objectCategory=computer)"

' Comma delimited list of attributes.
strAttributes = "sAMAccountName,canonicalName,distinguishedName," _
& "description,machineRole,managedBy,operatingSystem," _
& "location,operatingSystemVersion,operatingSystemServicePack,"
_
& "whenCreated,whenChanged"

' Construct LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes &
";subtree"

' Execute the query.
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False
Set adoRecordset = adoCommand.Execute

' Enumerate the recordset and display values.
Do Until adoRecordset.EOF
strNTName = adoRecordset.Fields("sAMAccountName").Value
arrCanonical = adoRecordset.Fields("canonicalName").Value
strCanonical = arrCanonical(0)
strDN = adoRecordset.Fields("distinguishedName").Value
strDesc = adoRecordset.Fields("description").Value
strRole = adoRecordset.Fields("machineRole").Value
strManaged = adoRecordset.Fields("managedBy").Value
strLocation = adoRecordset.Fields("location").Value
strOS = adoRecordset.Fields("operatingSystem").Value
strOSVer = adoRecordset.Fields("operatingSystemVersion").Value
strSP = adoRecordset.Fields("operatingSystemServicePack").Value
strCreated = adoRecordset.Fields("whenCreated").Value
strChanged = adoRecordset.Fields("whenChanged").Value
Wscript.Echo strNTName & ";" & strCanonical & ";" & strDN _
& ";" & strDesc & ";" & strRole & ";" & strManaged _
& ";" & strLocation & ";" & strOS & ";" & strOSVer _
& ";" & strSP & ";" & strCreated & ";" & strChanged
adoRecordset.MoveNext
Loop
adoRecordset.Close
adoConnection.Close

' Clean up.
Set objRootDSE = Nothing
Set adoCommand = Nothing
Set adoConnection = Nothing
Set adoRecordset = Nothing


Thanks for your help!

"Richard Mueller" wrote:

Hi,

Yes, the script uses ADO to query AD directly, which is very
efficient - no
need to bind to each computer object. If the program is saved in a
file
called Computers.vbs, run it at a command prompt with the
following
command
to redirect the output to a text file:

cscript //nologo Computers.vbs > report.txt

If Computers.vbs is not in the current directory, include the path
to
the
file. The "//nologo" parameter suppresses WSH info. You can add or
remove
attributes, change the order the values are output, use a
different
delimiter, whatever.

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

"Vishn" <Vishn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AE498841-CBE0-4969-AE04-AE2EBE005387@xxxxxxxxxxxxxxxx
Hi,
WOW! That was quite fast. You are the best!!.
I ran the script, it executed succesfully in cmd prompt.
Does
it
query the AD directly? How can we redirect the AD results to a
text
file(create a text file in the same directory as the vbs file &
then),I
mean
.

Relevant Pages

  • Re: LDAP query information
    ... a "Dim" statement. ... execution of the script. ... ' Filter on computer object. ... ' Construct LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)
  • Re: LDAP query information
    ... Dim strBase, strFilter, strAttributes, strQuery, adoRecordset ... Set adoConnection = CreateObject ... ' Construct LDAP syntax query. ... Yes, the script uses ADO to query AD directly, which is very efficient - no ...
    (microsoft.public.windows.server.scripting)
  • Re: LDAP query information
    ... execution of the vbscript? ... The error message indicates the line number in the script, ... Dim strBase, strFilter, strAttributes, strQuery, adoRecordset ... ' Construct LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)
  • Re: LDAP query information
    ... The error message indicates the line number in the script, ... Dim strBase, strFilter, strAttributes, strQuery, adoRecordset ... Set adoConnection = CreateObject ... ' Construct LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)
  • Re: LDAP query information
    ... a "Dim" statement. ... script is run. ... Microsoft MVP Scripting and ADSI ... to redirect the output to a text file: ...
    (microsoft.public.windows.server.scripting)