Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2013
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Query Outlook GAL from Excel using the 'Alias' field?

    Evening all,

    I have come to inherit a piece of work which - at the moment - entails a massive amount of manual referencing. When one of our servers hits a capacity threshold, it spits out an alert and a Treesize file is generated, detailing the largest files. The only identifier to who owns these files, is the staff number. The current - and extremely manual and time-consuming - process od establishing the owner by name, is to take this staff number and perform either an Advanced search in the GAL, or pulling up the Corporate Directory. In both instances, the staff number (m******) is the alias.

    Ideally, I want to get rid of this manual nonsense. In its place, I'd like to have a workbook which took the alias (m******) and automatically referenced the GAL, in order to pull: the First Name, the Last Name and the associated Email address. This would then enable me to connect these fields to a mailmerge, therein making notification a pretty much fully automated process. If I could reach this point, I could also request that server reports are sent to me more frequently, and I'd be able to notify people a lot earlier.

    The server report is Excel based: each row represents one file, and the column fields are:

    Name | Path | Size | Last Changed | Last Accessed | File Type | Owner

    It's the 'Owner' column which contains the m****** alias which I'd like to query against the GAL.

    I have done a LOT of research into this, but with mixed success. I managed to find a piece of VBA which did the reverse: if I knew the First Name and Last Name, then it referenced across to the GAL and returned the Alias (m******) But, I need it the other way around! Incidentally, that piece of code - although it worked - took about 2mins to query one name. It is querying a large organisations GAL though.

    I hope that makes sense? I really would be grateful for any views or advice on how to get this to work - it's doing my head in!

    If it would help, I can also post the code which works (albeit in reverse)

    Thanks in advance...

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    I'm assuming the "alias" is the sAMAccountName. This is a simple LDAP query which will return the email address via the field name "mail".
    There are plenty of examples of LDAP queries on the web.

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Aug 2013
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi Paul,

    Thank you so much for the tip - first class; I now have code which does EXACTLY what I want!

    I googled around LDAP and came across this: http://www.remkoweijnen.nl/blog/2007...ry-from-excel/

    I recognised that this function was generic enough that I could merely change the fields around, to return anything I wanted. So, I changed the target field to "sAMAccountName", and am now calling: "mail" (for their email), "givenName" (for their first name), "sn" (for their last name), and "company" (for their organisational division)

    The code is super efficient, and is called by an Excel function 'GetAdsprop'. Thoroughly recommended!

    Thanks again,

    td

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts

  5. #5
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Humm - I tried that code and it didn't like "cn". I just did a cut and paste (replacing semi colons with commas)

    Alan

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Cut and paste often doesn't cut it. You need to replace the quote characters with Shift 2 quote characters, and hyphens and single quotes etc, etc.
    BTW, "cn" means search for a CN = "whatever you entered in the cell". If it's a sAMAccountName use "sAMAccountName"....

    cheers, Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •