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.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 9 years and 1 month old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
Maries
Posts: 17
Last visit: Mon Feb 09, 2015 12:00 pm

Help with VB Script - Get the Managers sAmAccountName?

Post by Maries »

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
Last visit: Thu Oct 22, 2015 1:10 pm

Help with VB Script - Get the Managers sAmAccountName?

Post by SAPIEN Support Forums »

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 ***
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

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

Post by jvierra »

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
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

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

Post by jvierra »

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
Last visit: Mon Feb 09, 2015 12:00 pm

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

Post by Maries »

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?
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

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

Post by jvierra »

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
Last visit: Mon Feb 09, 2015 12:00 pm

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

Post by Maries »

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!
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

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

Post by jvierra »

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
Last visit: Mon Feb 09, 2015 12:00 pm

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

Post by Maries »

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?
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

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

Post by jvierra »

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?
This topic is 9 years and 1 month old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked