Support Forums

Help with VB Script - Get the Managers sAmAccountName?

Anything VBScript-related, including Windows Script Host, WMI, ADSI, and more.
Forum rules
Do not post any licensing information in this forum.
User avatar
Maries
Posts: 17
Joined: Wed Jan 21, 2015 10:23 am

Help with VB Script - Get the Managers sAmAccountName?

Postby Maries » Wed Jan 21, 2015 10:27 am

Howdy

I am nearly done with this script and can't get the managers sAmAccountName. This works to gather many attributes for users and I can get the DN of the manager, but not the samaccountname, can anyone help me with this?

Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & "localdomain" & ">"
strFilter = "(&(objectCategory=person)(objectClass=user)(company=*))"
strAttributes = "sAMAccountName,sn,GivenName,distinguishedName,"
strAttributes = strAttributes & "Department"
strAttributes = strAttributes & "manager"
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
Set adoRecordset = CreateObject("ADODB.Recordset")
Set adoRecordset.ActiveConnection = adoConnection
adoRecordset.Properties("Page Size") = 1000
adoRecordset.Source = strQuery
adoRecordset.Open
Do Until adoRecordset.EOF
SQLRecordset.AddNew
SQLRecordset.Fields("sAMAccountName") = adoRecordset.Fields("sAMAccountName")
SQLRecordset.Fields("department") = adoRecordset.Fields("department")
sDesc = ""
If IsNull(adoRecordset.Fields("description")) = False Then
For Each item In adoRecordset.Fields("Description").Value
sDesc = sDesc & Trim(item)
Next
SQLRecordset.Fields("description") = sDesc
Else
SQLRecordset.Fields("description") = Null
End If
SQLRecordset.Fields("displayName") = adoRecordset.Fields("displayName")
User avatar
SAPIEN Support Forums
Posts: 945
Joined: Wed Dec 03, 2014 2:26 pm

Help with VB Script - Get the Managers sAmAccountName?

Postby SAPIEN Support Forums » Wed Jan 21, 2015 10:27 am

This is an automated post. A real person will respond soon.

Thank you for posting, maries.

Here are some hints to help you get an accurate and complete answer to your question.

Ask in the best forum:
If you asked in the wrong forum, just copy your question to the right forum.

Anticipate follow-up questions!

Did you remember to include the following?
    1. Product, version and build
    2. 32 or 64 bit product
    3. Operating system, e.g. Windows 7 64 bit.
    4. Attach a screenshot, if applicable
    5. Attach logs, crash reports, etc., in a ZIP file
If not, please take a moment to edit your original post or reply to this one.

*** Make sure you do not post any licensing information ***
User avatar
jvierra
Posts: 10044
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Help with VB Script - Get the Managers sAmAccountName?

Postby jvierra » Wed Jan 21, 2015 12:05 pm

To get you pointed in a better direction start with this.
VBScript Code
Double-click the code block to select all.
Set objRootDSE = GetObject("LDAP://RootDSE")
domainDN = objRootDSE.Get("defaultNamingContext")

strBase = "<LDAP://" & domainDN & ">"

strFilter = "(&(objectCategory=person)(objectClass=user))"
strAttributes = "sAMAccountName,displayName,sn,GivenName,distinguishedName,Department,manager,description"
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"

Set conn = CreateObject("ADODB.Connection")
conn.Provider = "ADsDSOObject"
conn.Open "Active Directory Provider"
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.Properties("Page Size") = 500
cmd.CommandText = strQuery
Set rs = cmd.Execute()

'On Error Resume Next
While Not rs.EOF
WScript.Echo "++++++++++++++++++++++++++"
for each f in rs.Fields
WScript.StdOut.Write f.Name
On Error Resume Next
WScript.StdOut.Writeline ":" & vbTab & f.Value
On Error GoTo 0
Next
rs.MoveNext
Wend
User avatar
jvierra
Posts: 10044
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Help with VB Script - Get the Managers sAmAccountName?

Postby jvierra » Wed Jan 21, 2015 12:28 pm

Here is a version that dumps everything:

VBScript Code
Double-click the code block to select all.
Set objRootDSE = GetObject("LDAP://RootDSE")
domainDN = objRootDSE.Get("defaultNamingContext")

strBase = "<LDAP://" & domainDN & ">"

strFilter = "(&(objectCategory=person)(objectClass=user))"
strAttributes = "sAMAccountName,displayName,sn,GivenName,distinguishedName,Department,manager,description"
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"

Set conn = CreateObject("ADODB.Connection")
conn.Provider = "ADsDSOObject"
conn.Open "Active Directory Provider"
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.Properties("Page Size") = 500
cmd.CommandText = strQuery
Set rs = cmd.Execute()

for each f in rs.Fields
header = header & f.Name & "|"
Next

WScript.Echo header

While Not rs.EOF

for each f in rs.Fields
if f.Type = 12 Then

if IsNull(f.Value) Then
WScript.StdOut.Write f,Value & "|"
Else
WScript.StdOut.Write Join(f.Value) & "|"
End if
Else
WScript.StdOut.Write f.Value & "|"
End If
Next

WScript.StdOut.Write vbCrLf
rs.MoveNext

Wend
User avatar
Maries
Posts: 17
Joined: Wed Jan 21, 2015 10:23 am

Re: Help with VB Script - Get the Managers sAmAccountName?

Postby Maries » Wed Jan 21, 2015 9:16 pm

Thank You Immensely!

Can I trouble you for one more question? The script I posted is run as a MS SQL job, and the commands for SQL are in the script. How do I add the part that just pulls the manager info as you posted in your first reply, or if I want to just use the code you posted to pull all the info, how do I then get the SQL part to work?
User avatar
jvierra
Posts: 10044
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Help with VB Script - Get the Managers sAmAccountName?

Postby jvierra » Wed Jan 21, 2015 9:34 pm

I have no idea what you are trying to ask.

Perhaps you want to post in a SQLServer forum.

I just showed how to correctly quert AD with VBScript. SQLServer has its own
way of linking to AD. It wouls not use VBScript.

The manager attribute of a user object is a distinguished name.
User avatar
Maries
Posts: 17
Joined: Wed Jan 21, 2015 10:23 am

Re: Help with VB Script - Get the Managers sAmAccountName?

Postby Maries » Wed Jan 21, 2015 10:03 pm

The script I posted runs as a SQL job, so my understanding is that it's just vb and then the commands are in there that work with SQL to add the data to the database. I literally copied and pasted the script from SQL to the post.

So if I were to just add the manager part to what I posted, what would I have to do, change the script entirely to just use the script you provided or can I leave the SQL items in and just add the manager part?

Thank you very much!
User avatar
jvierra
Posts: 10044
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Help with VB Script - Get the Managers sAmAccountName?

Postby jvierra » Thu Jan 22, 2015 12:47 am

To get the managers accoount we would just use GetObject

VBScript Code
Double-click the code block to select all.
Set mgr = GetObject("LDAP://" & managerDN )
msgbox mgr.SamAccountName


That is all you need to do.

If you are taking about using the script control under SSIS then ther are many better methods in modern SQLServer to integrate with AD.
User avatar
Maries
Posts: 17
Joined: Wed Jan 21, 2015 10:23 am

Re: Help with VB Script - Get the Managers sAmAccountName?

Postby Maries » Thu Jan 22, 2015 1:07 am

Thx! Yes, that's all I need, and I know this can be done much easier and better, but the SQL group won't change at the moment.

To add this line in, I tried belis the get query, but it fails, where do I add it?
User avatar
jvierra
Posts: 10044
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Help with VB Script - Get the Managers sAmAccountName?

Postby jvierra » Thu Jan 22, 2015 1:26 am

Siorry I haveno idea what you are trying to do so how would I know where you should add it.

YOu posted a script that was so badly assembled that it could never have worked. It also seemed to be missing mmajor pieces. I posted an example of how to write a functional query. Othere than that what you have posted leaves no clue as to what you are trying to do.

Are you trying to execute this as a CMD task as a file or as a script task under SSIS? What is the purpose? What is being added? Why?

Return to “VBScript”

Who is online

Users browsing this forum: No registered users and 8 guests