Update to the Problem: we have a solution

So a few months ago there was a problem Where I continue to admire the Problem without a complete Solution. This has been solved for a few months, but sh!t happens. Am now finally getting around to providing the a solution, one that works for me, but may not work for you. This is pieced together haphazardly, mainly so I have note of it and well so if you’re looking for help on doing something similar you have a much better starting point than I did.

After a hot minute with Python-LDAP I determined it was a beast I was not interested in taming at the moment because well I had another option, mind you at the time we thought this ‘other’ option was going to be easier. I don’t know if it was or not, but it took some serious neuron firing to do.

At one point I dove deep into VBA scripting, where I figuratively lost loads of hair and age 20 years. The script scraped hundreds of emails for a text string (unique ID), to parse out into an excel file (staying within the suite of Microsoft Office deemed significantly easier than going out) and then ran line by line on LDAP to print the output of the request attributes and then convert to csv and email to colleague to do a mail merge.

**confession** I actually signed up for a Stack Overflow account because of this!

One Solution but not THE solution:

VBScript and a Bash script that ran LDAP Queries, Python conversions and used Mutt to send an email attachment.
I’ve changed values to neutral values, you will need to update them to match what you need.
Here is my repo that provides the files you’ll need if you decide to take the VBScript route. https://github.com/whitni/VBScriptandLDAP

Once you set this up in VBA, you can set it as a macro, but I had it set as a rule at first and then switched to doing something different right after I had this solution in place…so didn’t bother.

The Solution I settled on:

I returned to a single bash script that queried LDAP matching on certain attributes, primary change was the decision to send emails out based on start dates and not when we received an email notification from HR. This will soon be turned into a cron job and I can dust my hands of it BUT walking away with significantly more knowledge of LDAP, AD, VBA and all that…

#!/bin/bash
#A simple script

## date format ##
today=$(date +”%F”)
startdate=$(date +”%Y%m”)

##backup path ##
BAK=”/path/that/you/saved/file/newhires_$today”

# LDAP Search query
ldapsearch -W -h your.AD.server.com -x -D ldapusername@email.com -b “dc=$1,dc=$2,dc=$3” -s sub “attributes you need to match or not match on” attributes you want > $BAK

#convert LDIF to csv
python LDIFtoCSV/LDIFtoCSV.py $BAK > /path/that/you/saved/file/newhires_$today.csv

#send email with file attached
mutt -a /path/that/you/saved/file/newhires_$today.csv -s “New Hire Emails” -c persontoCC@email.com — persontoemail@email.com

Resources used:
VBScript Library: https://msdn.microsoft.com/en-us/library/aa227499(v=vs.60).aspx
RegEx parsing: http://www.slipstick.com/developer/regex-parse-message-text/
LDAP Man page: [Linux_terminal] > man ldap
LDIFtoCSV conversion tool: https://github.com/tachang/ldiftocsv
Stack Overflow — my question: http://stackoverflow.com/questions/36752876/why-does-copying-a-string-from-outlook-to-excel-open-a-new-instance-of-excel-for/36754211#36754211

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s