Solved

Query the email address of each user in an OU and add the Email address of the user to the Notes box.

Posted on 2008-10-26
38
336 Views
Last Modified: 2012-05-05
Hi,

Query the email address of each user in an OU and add the Email address of the user to the Notes box.
Like
Email Address : Sharath@plc.com

Can a script query the Users in active directory and do this.

Regards
Sharath
0
Comment
Question by:bsharath
  • 22
  • 11
  • 5
38 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 22807266
Hi, Sharath.

Yes, it's possible, but I'll need to know which notes box you are talking about.  
0
 
LVL 11

Author Comment

by:bsharath
ID: 22807483
In Active directory each user has a Notes box.
Username > Properties > Phones. Notes
In outlook when we see a users properties we can see the entered data in there
0
 
LVL 11

Author Comment

by:bsharath
ID: 22807490
Attached image
ScreenShot003.bmp
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22807524
Ok, definitely doable.  I'm curious though, why do you want the address in the notes?  It's already visible.
0
 
LVL 11

Author Comment

by:bsharath
ID: 22807531
But not selectable. when i do some quick entries to my excel. I need to get the email addreses. But there is no place in the GAL that i get them being able to select.

Is there a way to select?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22811446
Going through all of those clicks to get that info seems cumbersome.  Would you like a simpler way of doing this?  How about entering the person's name, account name, or something like that and having their email address copied directly to the clipboard so you can just paste it into whatever you're doing?
0
 
LVL 11

Author Comment

by:bsharath
ID: 22811474
Ok then can this be done as i have both the Ntlogin and Email address in a txt file or excel.
If in txt file like
NTlogin;Email address
Or an excel
Colum A                 Colum B
Ntlogin                  Email address
Can a script query each Ntlogin and paste the email address to the Notes box as this.

Email address : Sharath@plc.com
0
 
LVL 11

Author Comment

by:bsharath
ID: 22811515
Can this same script be used to update the email addresse in colum "O" to the notes as " Email Address : mail id"
http://www.experts-exchange.com/Software/Server_Software/File_Servers/Active_Directory/Q_23738740.html
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22811621
I'm confused.  I asked why you'd want the address in the Notes field and you replied, "I need to get the email addreses. But there is no place in the GAL that i get them being able to select."

Based on that satetment, I concluded that you must want them in the Notes field so you can copy and paste them into Excel (or whatever).  By my count it takes at least four mouse clicks to get to the Notes field in Outlook.  So, I proposed a means of doing this that would save all those clicks and perform half the work, copying the address to the clipboard, for you and your response is that you still want to copy the address to the Notes field.  I must not be understanding the goal properly.  Why would you want the address in the Notes field if you can get it copied straight to the clipboard without ever having to go to the Notes field and do that manually?  
0
 
LVL 11

Author Comment

by:bsharath
ID: 22811667
Sorry to get you confused.
There are 2 reasons.
This is required for many employers say managers who fill in there excel with all the employer details would love to have it in Notes so they can go there and get the mail id copied.I cannot give everyone a script to do this... :-(
Second is i have Hta's that query the notes and get the details and even scripts in bulk query fetck the data in the notes into excel when i query by user name/Location/machine Name thats in the Notes already.

So thats why i though if it can be updated the way other details get updated in the above link i posted that would solve my issue.

0
 
LVL 76

Expert Comment

by:David Lee
ID: 22811894
Ok, I understand.  This should do it.  Copy and paste the code into Notepad.  Edit the LDAP string, then save the file with a .vbs extension.  Double-click to run it.  
Dim adoCon, adoRS, objUser

Set adoCon = CreateObject("ADODB.Connection")

adoCon.Provider = "ADsDSOObject"

adoCon.Open "ADSI"

Set adoRS = adoCon.Execute("SELECT ADsPath,mail,samAccountName 

'Edit the LDAP string on the following line to point to the OU you want to run against'

FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")

If Not adoRS.EOF Then

    Do While Not adors.EOF

        Set objUser = GetObject(adoRS.Fields("ADsPath"))

        objUser.Put "Notes", "Email address: " & adoRs.Fields("mail")

        objUser.SetInfo

        adoRS.MoveNext

    Loop

End If

adors.Close

Set adoRS = Nothing

adoCon.Close

Set adoCon = Nothing

WScript.Echo "All done!"

Open in new window

0
 
LVL 11

Author Comment

by:bsharath
ID: 22812034
Do i need to give it a file that has NTlogin & Email address or will this take it automatically.

Will i have a header like
Email address : and the mail ID
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22812087
Ok, I'm confused. again.  The question was "Query the email address of each user in an OU and add the Email address of the user to the Notes box."  Everything is done in AD.  The code reads all the users in that OU in AD, gets their mail address. then copies the email address in the form

    Email address: someone@company.com

to the Notes field in AD.  Where does a file come into play?
0
 
LVL 11

Author Comment

by:bsharath
ID: 22812112
Thats exactly what i wanted ...
Thank U....
Shall check and get back...
0
 
LVL 11

Author Comment

by:bsharath
ID: 22820555
How can i get this from
servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com
Any place on the DC this is mentioned...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22820654
I can't answer the question because I don't know anything about your Active Directory structure.  All I can say is that you need to edit that string to point to the OU you want the script to run against.  Assuming the following values

Company Name: PLC
Server Name: Alpha
Target OU: Users

the string would be

    alpha.plc.com/ou=Users,dc=plc,dc=com
0
 
LVL 11

Author Comment

by:bsharath
ID: 22820756
I get this

---------------------------
Windows Script Host
---------------------------
Script:      C:\Place email id's.vbs
Line:      5
Char:      64
Error:      Unterminated string constant
Code:      800A0409
Source:       Microsoft VBScript compilation error

---------------------------
OK  
---------------------------
0
 
LVL 11

Author Comment

by:bsharath
ID: 22820757
I get this

---------------------------
Windows Script Host
---------------------------
Script:      C:\Place email id's.vbs
Line:      5
Char:      64
Error:      Unterminated string constant
Code:      800A0409
Source:       Microsoft VBScript compilation error

---------------------------
OK  
---------------------------
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22820800
Oops.  My fault.  I inserted the comment line in the wrong place.  Move the comment on line #6 up a line.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 11

Author Comment

by:bsharath
ID: 22820892
As this
Still get the error...
Dim adoCon, adoRS, objUser

Set adoCon = CreateObject("ADODB.Connection")

adoCon.Provider = "ADsDSOObject"

adoCon.Open "ADSI"

'Edit the LDAP string on the following line to point to the OU you want to run against'
 

Set adoRS = adoCon.Execute("SELECT ADsPath,mail,samAccountName 
 

FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")

If Not adoRS.EOF Then

    Do While Not adors.EOF

        Set objUser = GetObject(adoRS.Fields("ADsPath"))

        objUser.Put "Notes", "Email address: " & adoRs.Fields("mail")

        objUser.SetInfo

        adoRS.MoveNext

    Loop

End If

adors.Close

Set adoRS = Nothing

adoCon.Close

Set adoCon = Nothing

WScript.Echo "All done!"

Open in new window

0
 
LVL 76

Expert Comment

by:David Lee
ID: 22820941
Line 8 shouldn't be blank.  Delete it.  Lines 7 and 9 are really one line that wraps to two lines.
0
 
LVL 11

Author Comment

by:bsharath
ID: 22832476
Sorry still get the error.
Can you post in the full code...Please...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22832771
Here you go.
Dim adoCon, adoRS, objUser

Set adoCon = CreateObject("ADODB.Connection")

adoCon.Provider = "ADsDSOObject"

adoCon.Open "ADSI"

'Edit the LDAP string on the following line to point to the OU you want to run against'

Set adoRS = adoCon.Execute("SELECT ADsPath,mail,samAccountName 

FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")

If Not adoRS.EOF Then

    Do While Not adors.EOF

        Set objUser = GetObject(adoRS.Fields("ADsPath"))

        objUser.Put "Notes", "Email address: " & adoRs.Fields("mail")

        objUser.SetInfo

        adoRS.MoveNext

    Loop

End If

adors.Close

Set adoRS = Nothing

adoCon.Close

Set adoCon = Nothing

WScript.Echo "All done!"

Open in new window

0
 
LVL 11

Author Comment

by:bsharath
ID: 22837718
I get this now...

---------------------------
Windows Script Host
---------------------------
Script:      C:\Place email id's.vbs
Line:      11
Char:      9
Error:      The requested operation did not satisfy one or more constraints associated with the class of the object.
Code:      80072014
Source:       (null)

---------------------------
OK  
---------------------------
0
 
LVL 11

Author Comment

by:bsharath
ID: 22837719
I get this now...

---------------------------
Windows Script Host
---------------------------
Script:      C:\Place email id's.vbs
Line:      11
Char:      9
Error:      The requested operation did not satisfy one or more constraints associated with the class of the object.
Code:      80072014
Source:       (null)

---------------------------
OK  
---------------------------
0
 
LVL 14

Expert Comment

by:rejoinder
ID: 22844775
I have modified the script to point to the "info" field which in Outlook looks like the "Notes" field.  This version will bypass users with no email address and will preserve the existing data that is currently in the "info" box.
'http://www.experts-exchange.com/Programming/Languages/Q_23848162.html
 

Dim adoCon, adoRS, objUser

Set adoCon = CreateObject("ADODB.Connection")

adoCon.Provider = "ADsDSOObject"

adoCon.Open "ADSI"

'Edit the LDAP string on the following line to point to the OU you want to run against'

Set adoRS = adoCon.Execute("SELECT ADsPath,info,mail,samAccountName FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")

If Not adoRS.EOF Then

    Do While Not adors.EOF

        if IsNull(adoRs.Fields("mail")) or adoRs.Fields("mail") = "" then

            'Do nothing - the user has no email address

        else

            strAddNewLine = ""

            if IsNull(adoRs.Fields("info")) or adoRs.Fields("info") = "" then

                strAddNewLine = vbCRLF

            end if

            Set objUser = GetObject(adoRS.Fields("ADsPath"))

            objUser.Put "Info", adoRs.Fields("info") & strAddNewLine & "Email address: " & adoRs.Fields("mail")

            objUser.SetInfo

        end if

        adoRS.MoveNext

    Loop

End If

adors.Close

Set adoRS = Nothing

adoCon.Close

Set adoCon = Nothing

WScript.Echo "All done!"

Open in new window

0
 
LVL 76

Expert Comment

by:David Lee
ID: 22845969
Good catch, rejoinder.
0
 
LVL 11

Author Comment

by:bsharath
ID: 22847292
Thank you Rejoinder works perfect....
But it gets mixes with the other data in the Notes.
Can it be placed to the last available row.

As i have 3 rows of data in the Notes already.
Can this be the 4th Row...
0
 
LVL 11

Author Comment

by:bsharath
ID: 22847293
Thank you Rejoinder works perfect....
But it gets mixes with the other data in the Notes.
Can it be placed to the last available row.

As i have 3 rows of data in the Notes already.
Can this be the 4th Row...
0
 
LVL 14

Expert Comment

by:rejoinder
ID: 22847686
Hmmm, I am not sure how to fix that.  When I look at the code it should append the email text after what is already there.  I had no idea that it would end up in the middle or beginning.  I am not sure what is doing that.
0
 
LVL 11

Author Comment

by:bsharath
ID: 22847694
Ok the notes has data as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah

Now what is happening is the email address is getting into as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah Email address : Blah

Actually it has to be as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah
Email address : Blah
0
 
LVL 11

Author Comment

by:bsharath
ID: 22847695
Ok the notes has data as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah

Now what is happening is the email address is getting into as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah Email address : Blah

Actually it has to be as this

Machine Name : Blah blah blah
Seat No : Blah blah
Serial No : Blah
Email address : Blah
0
 
LVL 14

Expert Comment

by:rejoinder
ID: 22847707
I had a case of backwards logic.  This will look better.
'http://www.experts-exchange.com/Programming/Languages/Q_23848162.html

 

Dim adoCon, adoRS, objUser

Set adoCon = CreateObject("ADODB.Connection")

adoCon.Provider = "ADsDSOObject"

adoCon.Open "ADSI"

'Edit the LDAP string on the following line to point to the OU you want to run against'

Set adoRS = adoCon.Execute("SELECT ADsPath,info,mail,samAccountName FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")

If Not adoRS.EOF Then

    Do While Not adors.EOF

        if IsNull(adoRs.Fields("mail")) or adoRs.Fields("mail") = "" then

            'Do nothing - the user has no email address

        else

            strAddNewLine = vbCRLF

            if IsNull(adoRs.Fields("info")) or adoRs.Fields("info") = "" then

                strAddNewLine = ""

            end if

            Set objUser = GetObject(adoRS.Fields("ADsPath"))

            objUser.Put "Info", adoRs.Fields("info") & strAddNewLine & "Email address: " & adoRs.Fields("mail")

            objUser.SetInfo

        end if

        adoRS.MoveNext

    Loop

End If

adors.Close

Set adoRS = Nothing

adoCon.Close

Set adoCon = Nothing

WScript.Echo "All done!"

Open in new window

0
 
LVL 11

Author Comment

by:bsharath
ID: 22847774
Thank U worked fine... But now need to remove the other one which got placed... :-(

Now i have 2 Email addresses...
Can the script clear anything in notes and place just this email address.
So again i can run the other scripts to place the serial no,machine name etc....

0
 
LVL 11

Author Comment

by:bsharath
ID: 22847775
Thank U worked fine... But now need to remove the other one which got placed... :-(

Now i have 2 Email addresses...
Can the script clear anything in notes and place just this email address.
So again i can run the other scripts to place the serial no,machine name etc....

0
 
LVL 14

Accepted Solution

by:
rejoinder earned 500 total points
ID: 22847932
I am so sorry about that mistake.
This version will now clear up the mistake from earlier within the notes field and will put the email address on the last line, perserving what is already there.

As a precaution, you can move a few users into a seperate OU and test the script against them.
'http://www.experts-exchange.com/Programming/Languages/Q_23848162.html

 

Dim adoCon, adoRS, objUser

Set adoCon = CreateObject("ADODB.Connection")

adoCon.Provider = "ADsDSOObject"

adoCon.Open "ADSI"

'Edit the LDAP string on the following line to point to the OU you want to run against'

Set adoRS = adoCon.Execute("SELECT ADsPath,info,mail,samAccountName FROM 'LDAP://servername.company.com/ou=MySubOU,ou=MyMainOU,dc=company,dc=com' WHERE objectClass='user' AND objectCategory='Person'")

If Not adoRS.EOF Then

    Do While Not adors.EOF

        if IsNull(adoRs.Fields("mail")) or adoRs.Fields("mail") = "" then

            'Do nothing - the user has no email address

        else

            strValue = ""

            arrData  = split(adoRs.Fields("info"),vbCRLF)

            for n = lBound(arrData) to uBound(arrData)

                intPosition = InStr(arrData(n),"Email address:")

                if InStr(arrData(n),"Email address:") > 2 then

                    strTemp = mid(arrData(n),1,intPosition-1)

                    arrData(n) = strTemp

                end if

                strSearchText = "Email address: " & adoRs.Fields("mail")

                if arrData(n) <> "" AND arrData(n) <> strSearchText then

                    strValue = strValue & arrData(n) & vbCRLF

                end if

            next

            strValue = strValue & strSearchText

            Set objUser = GetObject(adoRS.Fields("ADsPath"))

            objUser.Put "Info", strValue

            objUser.SetInfo

        end if

        adoRS.MoveNext

    Loop

End If

adors.Close

Set adoRS = Nothing

adoCon.Close

Set adoCon = Nothing

WScript.Echo "All done!"

Open in new window

0
 
LVL 11

Author Comment

by:bsharath
ID: 22847940
Thank you both for such great help...
Worked perfect by removing the duplicates and adding just one... :-))
0
 
LVL 14

Expert Comment

by:rejoinder
ID: 22847951
Again, I am sorry about the mistake and I am glad it worked out in the end.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now