PHP LDAP: Get All Active Directory Groups and other useful things….

As the suite of applications and other useful tools I’m developing for our organization expand,   I’ve found it increasingly necessary to integrate with our Active Directory.

With the assistance of our AD guru, Adam Witt,  and Professor Google,  I’ve finally managed to pull the information I need,  without breaking anything.

My goal is to maintain a database table of AD groups in order to control access to certain applications.

If you’re sitting there thinking that I’m sort of sort of idiot because I’m not pulling the AD groups as needed,  I explain all that at the end of this blog post….please read it before pointing out what is blindingly obvious.

I used the PHP LDAP library for this project.  Here is a link to the entry on the PHP site.   Please note the prerequisites.

This is how to connect to the LDAP server


$ldap = ldap_connect('867.530.09.00','389');  //its not a real IP....its a musical easter egg!
if (!$ldap) {
die('No LDAP');
exit;
}

That’s the easy part.

In order to successfully pull information from your Active Directory,  it is essential that you know how its laid out.

Note:  I specified to KNOW how its laid out,  not how you THINK its laid out.

(in other words:  learn from my experience!)

Using the Microsoft AD Browser helped me tie everything together,  and figure out exactly what I had to use as filters, and search strings.

Before I get to the big chunk of code that does things,  I’m going to cover each of the gotchas that I had to work past.

LDAP Operations Errors

After much flailing about I found that there were two things causing these.

Remember to set Options

ldap_set_option($ldap,LDAP_OPT_REFERRALS,0);
ldap_set_option($ldap,LDAP_OPT_PROTOCOL_VERSION,3);

If your AD administrator likes things like security,  you will have to use the ldap_bind command to register with the AD server!

Here’s the code to tell the AD server the credentials of your PHP script


ldap_bind($ldap,'xxxxxxx','thisisnotreallyapassword');

So now you can connect to your AD server,  AND your AD server knows what rights and privileges your PHP script has.

The next step is to tell the PHP script where to look for whatever it is you want to look for.

Here’s the command:


$result = ldap_search($ldap,$base_dn,$filter,$attributes);

The parameters can be a bit tricky,  and this is where the understanding your AD layout is crucial.

The $ldap parameter is your connection that you set up earlier.

The $base_dn is the Distinguished Name at the top of the part of the AD structure you want to search.

Note that the base_dn is NOT limited to DN’s,  you can add other attributes.

In this example,  I’m going to want to search within the OU (Organizational Unit) of STAFF, within the DCs (Domain Controller)  of MAIN,  SITE, CA.

Loading the $base_dn variable will look like this:

$base_dn = "OU=STAFF,DC=MAIN,DC=SITE,DC=CA";

I will confess that I haven’t spent a lot of time playing with filters and attributes.   I’ve seen some examples of fairly complex filters,  so if you’re looking for something like that,  you can always hit up Google.   If you find good, working examples, I’d invite you to post them in the comments here.

For my example,  we’re going to look for all “OU”, “CN”, and “DC” attributes within any child of our base that is an OU.

$filter = "(OU=*)";

$attr = array("OU","CN","DC");

$result = ldap_search($ldap,$base_dn,$filter,$attr);

$rescount = ldap_count_entries($ldap,$result);

$data = ldap_get_entries($ldap,$result);

echo '<pre>';

foreach($data as $row) {

print_r($row);

}

That’s a pretty basic example.  I don’t have any sample data to show you as that would require either sending you our actual AD structure (which I think would violate some terms of my employment contract),  or require me to go and build an AD server.

I will leave you with this bit of code that I used to examine and validate the information that I was getting.

I wanted to ensure that I was only working with groups that had users,  so, in my validation stage I used this bit of code:


foreach ($info as $k=>$v) { //$v['dn'] becomes my base domain as I want to search its children
$sres = ldap_search($ldap,$v['dn'],"(CN=*)",$attrib);

if (ldap_count_entries($ldap, $sres) > 0 ) {

echo $v['dn'].' has '.ldap_count_entries($ldap, $sres).' users<br>';

}

echo '<hr>';
}

Hopefully it helps.  If you’ve any questions or improvements,  please feel free to post them.

Now…for those of you who can’t think of any reason why I wouldn’t just pull AD groups from the server as users connected….

Yes…I know that I could just pull the AD groups on the fly (I’m not stupid),     I want to ensure that I can maintain security functionality in the event that the AD server isn’t available (or doesn’t respond fast enough),   and,  I want the ability to audit access,  meaning that I would want to have a record of a user’s AD groups at the time they logged in.

Just because I know some of you are thinking about how stupid I am to not just record the groups in some sort of audit table,  I’m going to explain why I’m doing that way.

The short answer is Database Normalization.   

Database Normalization refers to the practice of designing a database to avoid, as much as possible, storing redundant data.   Meaning that if you have a piece of information,  such as an address,  that would be associated to another entity (such as a person),  that you record that piece of information once,  and then associate it to the parent entities through a joining table.

I know some people have a hard time wrapping their heads around that,  but believe me,  its an actual thing.

In this specific case,  recording a user’s groups (as each would have several) each time they connect would create a row for each group,  for each user,  each time they logged in.

When I was testing the first part of this project,   I connected twice and created 16 rows…..8 rows twice.

Imagine how fast that audit table would grow with 100’s of users connecting multiple times a day.

So,  when tracking data like this for historical audit purposes,  I keep a table of all AD groups.   Each group,  in addition to having its name stored in the table,   it will also have a unique identifier field that would serve as this table’s primary key.

With this solution,  instead of having 16 rows with the name of the group,  I would have 16 rows of at least two columns.

Column 1 would contain the unique identifier of my row in the user table,   and Column 2 would contain the unique identifier of the row of the groups table containing the individual group.

This solution takes up much less space,  and,  with proper indexing,   makes database operations much faster and more efficient.

There is also the added bonus that other database programmers won’t make fun of me.

Advertisements

Mirth Integration: Iterate through specific HL7 segments

Many of us in the healthcare integration community use the Mirth Integration Engine for HL7 and other integration needs.

In the past I’ve been active on the Mirth Community Forums as Bostad,  both asking and answering questions.  I haven’t been that active lately as my duties don’t leave me much time.

I do sometime receive direct requests,  and it was one of those that prompted me to start posting some of my Mirth programming tips here.

If you’re curious about healthcare integration and/or HL7 messaging, you can read about it here.

When required to iterate through repeating segments of an HL7 message,  here’s my method:


var i = 0;   //iteration variable
for each (seg in msg..ROL) {   //iterates through each ROL segment of an incoming HL7 message

i++;

if (seg['ROL.3']['ROL.3.1'].toString() != 'FAMILY DOCTOR')  {

   channelMap.put('ROL ' + i,'Not Family Doc');

}
}

In the code above,  you’ll note that the variable seg holds the current iteration of the target segment.

You’ll also note that the code above doesn’t do anything useful…I don’t want to flood you with a bunch of lines of code that aren’t helpful.

To reference specific fields within the segment,  unlike when referencing msg or tmp objects,  you do not use the initial segment name as an index!


//referencing msg and tmp

var strM = msg['ROL']['ROL.3']['ROL.3.1'].toString();
var strT = tmp['ROL']['ROL.3]['ROL.3.1'].toString();

//referencing seg (from within implied loop)

var strS = seg['ROL.3]['ROL.3.1']..toString();

If anything is unclear,  or you have further questions, feel free to ask in comments.

MEDITECH and iPeople Echo: Providing near real time patient census data

Anyone whose worked with the Meditech Magic Hospital Information System knows that sometimes it can be a challenge to pull useful and timely statistical data from it.

Late last year, the Huron-Perth Healthcare Alliance implemented the iPeople Connect product which includes Echo an application that allows us to build our own SQL Server based data-repository,  specifying what data we want from our Meditech system,  and how frequently to update it.

What this does is allows us to provide our client base with a broad range of reports and reporting tools instead of the text based reports generated by Meditech’s NPR report writer.

One of the very first requests we received was to provide near real-time patient census data.

The project, dubbed Atlas,  asked for the following data elements:

  • Location
  • Bed Count
  • Patient Count
  • Empty Beds
  • Occupancy Rate
  • Isolation Patients
  • Pending DC dates prior to current date
  • Pending DC dates on current day
  • ALC patient counts

Anyone who has ever faced pulling data from Meditech knows that it be difficult figuring out where the data is stored.

With the help of iPeople Scout,  an application that allows us to search the Meditech Dictionaries,  and view joins,  we found that the best Meditech source table was ADM.ADMStatsInLocation.   This table gave us the bed census data for each location, as of the midnight run on any given date.

While all this seems straight forward,  there was a complication in how to define a bed.

I know what you’re thinking,  “a bed is something that you sleep in, or on…and its where hospitals keep patients!”.    You’d be correct,  except that our organization may have several entries for the same physical bed in the Meditech dictionary.    The reason for this is that some beds may be either acute,  or chronic.  It all depends on what type of patient gets admitted.

My preference is that data elements be identified programmatically.    After spending several hours running test queries,  trying to ensure that our virtual bed count matched our physical bed count I realized that this was impossible.   The reason is that those pesky humans might get in there and create a bed that wouldn’t fit the existing naming convention.

I opted instead to use an associative array within the PHP script that contained the official bed counts for each facility and location.

The next, and most difficult challenge to solve, was how to identify ALC patients.

For those unfamiliar with the term,  ALC means (Alternative Level of Care).   This term refers to patients occupying acute beds,  who are ready for discharge,  but do not have a place to go.   They still require care (long term facility,  chronic or palliative etc),  but there are no beds for them to go to.

When I first reviewed the specifications,  I figured this would be a flag,  perhaps a custom query somewhere within ADM.   I was somewhat mortified to learn that the only way to identify an ALC patient was through ALC orders.

This was complicated by the fact that a patient who has been in hospital for some time,  could be designated ALC several times through their stay.

Within the organization we have several ALC order types in our OE.ORD dictionary.

  • Identify:  indicates that the patient is an ALC patient
  • Change:  changes the order
  • Discontinue:  discontinues the order,  however the patient remains in that bed, and
  • Discharge:  the patient is discharged

It took several days to work this out,  and it was only with the assistance of our Nursing Informatics team,  as well as input from Bed Management that I managed to figure it out at all.

In order to identify,  and add a patient to the ALC tally,   while compiling the information,   I query OE.ORD for each in patient,   looking for any ALC orders for their current visit.

I pull the data sorted in descending order by order date and time.

This is the logic I use:

If the query gets no results,  then patient is not now,  nor has ever been an ALC patient.   I then set the ALC flag within the XML node for this patient to “no”.

If there are results,  I check to see if the last result is a discontinue or discharge.   If it is,  this indicates that the patient was an ALC patient.   I set the ALC attribute to “expired” for these patients.

The only option left at this point is that the patient is an alc patient.   I increment the appropriate counters,  and set the ALC flag to “yes”,  which is used for display and functionality as the user can pull up a patient’s entire ALC order history in this case.

Other data types,  such as Isolation and Pending Discharge are determined by checking custom query fields which are exported into the SQL Server database.

All this information is formatted into an XML document by a PHP script.   In order to save time on processing,  the script is run every 10 minutes by a cron job,  and the results written to a file on the web server.

When a user accesses the Atlas home page,   it is this file that is accessed,  saving the user a long wait each time they go looking for data.

There is functionality that checks the age of the data,  and,  if it is older than 10 minutes,  the user be flagged, and notified to call IT to report a problem.

The front end is a simple web page,  with JQuery/javascript programming that will take the XML document and dynamically create tables using Document Object Model traversal methods.

The user has the ability, via checkboxes, to limit which sites within the organization they want to see.

Upon page load,  each site is collapsed.   The user can view or hide site specific locations by clicking on the desired site.

The screenshot below shows the Stratford General Hospital locations expanded.

censusbigscreenshot

Other functionality includes the ability to view a list of patients in each location.   The list only uses account numbers,  reducing the risk of a privacy breach.

Within the individual patient list,  the user can view a history of ALC orders for patients that have them.

They can also view a list of patients for whom there is a pending discharge.

Managers and other stakeholders receive a static copy of this report via email every morning at 7am.

The iPeople DR,   combined with the ability to use modern programming languages has allowed us to improve patient flow, and service delivery to our patients.