Thursday, October 23, 2008

VBS to query AD

Problem: I need to get an excel spreadsheet with a list of computer names and their descriptions.

Answer:
~Use VBS script to query AD and output all fields for computer objects to .txt file with ; between items
~Open .txt file with excel type software and have it sort using the ; as delimiters.

Voila!

continued...


VBS AD query script

~Copy the text below into your favorite text editor and save as adquery.vbs file
~Run from command prompt: cscript adquery.vbs > output.txt
~You can remove some of the output fields if you don't need all of the fields in your outpu file.
This section controls the output:
wscript.echo strNTName & ";" & strCanonical & ";" & strDN _
& ";" & strDesc & ";" & strRole & ";" & strManaged _
& ";" & strLocation & ";" & strOS & ";" & strOSVer _
& ";" & strSP & ";" & strCreated & ";" & strChanged
adoRecordset.MoveNext

* note that the _ is used as a 'line break' character. It's only needed at the end of a line if the command needs to continue onto the next line down. Remove as necessary.
------
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 = ""

' 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
Dim arrDesc
arrDesc = adoRecordset.Fields("description").Value
If (TypeName(arrDesc) = "Variant()") Then
strDesc = arrDesc(0)
Else
strDesc = ""
End If
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

0 Comments:

Get the latest 'geek' post via RSS...

Subscribe below for email updates...

Enter your email address:

Delivered by FeedBurner

/* Extra Links ----------------------------------------------- */
tangential
tangential