Unfortunately, there isn't an out of the box way for users to add these photos directly to Active Directory (as far as I'm aware!) so some trickery needs to happen to get this all to work.
This blog post will explain how we extract user profile pictures from our Interact Intranet environment and then upload them to Active Directory.
Step 1: SQL query to identify users and their pictures
Microsoft recommends square profile pictures with dimensions of 96x96. Interact conveniently already thumbnails pictures into preset sizes. After testing, we found that their 4th size, 75px width, has sufficient clarity when used in Active Directory, which allows us to export pictures without the need for programatic image manipulation. Note: If you do want to investigate image manipulation you might start with a library like ImageMagick.select PERSON.NTUsername, ASSET_INSTANCE.BinaryData
from PERSON
INNER JOIN ASSET_INSTANCE
on PERSON.AssetID=ASSET_INSTANCE.AssetID
Where PERSON.NTUsername != 'ARCHIVED'
and PERSON.AssetID is not NULL
and AssetSize='4'
Step 2: Export pictures to a file path
Below is the code that is triggered though a nightly job to extract the binary data from the database and convert it into files at the designed path.Credit for this code, and pulling this project together, comes from coding ninja extraordinaire Matt Chiste from Integryst who was able to complete the development in less time than it took me to write the requirements!
using System;Reference: http://support.microsoft.com/kb/317016
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
namespace PicExporter
{
class Program
{
static string connString = "Data Source=<redacted>;Initial Catalog=<redacted>;Persist Security Info=True;User ID=<redacted>;Password=<redacted>";
static string cmdString = "select PERSON.NTUsername, ASSET_INSTANCE.BinaryData from PERSON INNER JOIN ASSET_INSTANCE on PERSON.AssetID=ASSET_INSTANCE.AssetID Where PERSON.NTUsername != 'ARCHIVED' and PERSON.AssetID is not NULL and AssetSize='4'";
static string folder = "pics/";
static string FILENAME_EXTENSION = ".jpg";
static int FILENAME_INDEX = 0;
static int BINARY_INDEX = 1;
static string PROP_FILE = "picexporter.properties";
static void Main(string[] args)
{
Console.Out.WriteLine("START: " + DateTime.Now.ToString());
// vars
string filename;
loadVars();
// set up the connection
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
SqlDataReader rdr=null;
try
{
// run the command
conn.Open();
cmd.Connection = conn;
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdString;
rdr = cmd.ExecuteReader();
// iterate the users
while (rdr.Read())
{
// write to the log
Console.Out.Write("Extracting user: " + rdr[FILENAME_INDEX].ToString());
// get the byte array for the image
Byte[] b = new Byte[(rdr.GetBytes(BINARY_INDEX, 0, null, 0, int.MaxValue))];
rdr.GetBytes(BINARY_INDEX, 0, b, 0, b.Length);
// write rest of line to the log
Console.Out.Write(" (" + b.Length + "bytes)");
// check existing file
filename = folder + rdr[FILENAME_INDEX].ToString() + FILENAME_EXTENSION;
if (File.Exists(filename))
Console.Out.Write(" [UPDATE]" + Environment.NewLine);
else
Console.Out.Write(" [NEW]" + Environment.NewLine);
// user names prefixed with the domain will have a \ in them. Need to make sure the full folder path is created
if (!Directory.Exists(Path.GetDirectoryName(filename)))
{
Console.WriteLine("Creating Folder: " + Path.GetDirectoryName(filename));
Directory.CreateDirectory(Path.GetDirectoryName(filename));
}
// write the file
FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write);
fs.Write(b, 0, b.Length);
fs.Close();
}
}
catch (Exception ex)
{
Console.Out.WriteLine("EXCEPTION: " + ex.Message);
Console.Error.WriteLine("EXCEPTION: " + ex.Message);
}
finally {
// close the connections
if (rdr != null)
rdr.Close();
if (conn != null)
conn.Close();
}
Console.Out.WriteLine("FINISH: " + DateTime.Now.ToString());
}
static void loadVars()
{
string propFile = Directory.GetCurrentDirectory() + "\\" + PROP_FILE;
string line, name, value;
StreamReader file = null;
// Read the file line by line
try
{
file = new System.IO.StreamReader(propFile);
while ((line = file.ReadLine()) != null)
{
try
{
name = (line.Substring(0, line.IndexOf('=')));
value = (line.Substring(line.IndexOf('=')+1));
if (name == "connString")
connString = value;
else if (name == "cmdString")
cmdString = value;
else if (name == "folder")
{
folder = value;
if (!Directory.Exists(folder))
{
Console.WriteLine("Creating Folder: " + folder);
Directory.CreateDirectory(folder);
}
}
// else
// Console.WriteLine("ignoring property: name: " + name + ", val: " + value);
}
catch (Exception)
{
// ignore line without "="
// Console.WriteLine("ignoring line: " + line);
}
}
}
catch (Exception ex)
{
Console.Out.WriteLine("ERROR READING " + propFile + ": " + ex.Message);
Console.Error.WriteLine("ERROR READING " + propFile + ": " + ex.Message);
}
finally
{
if (file != null)
file.Close();
}
}
}
}
Step 3: Import pictures to Active Directory
The following powershell script runs through a scheduled job. The job searches the folder we have extracted photos to for newly modified photos and then calls the script, which matches the filename of the photo to the network User Name and then uses native Active Directory APIs to add the image to the user’s profile.param($Identity,$Path);
# Import a .jpg into Active Directory for use as the Exchange/Outlook GAL Photo
# For best resultsL <10kb files, 96x96 dimensions
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin
if (!$Identity)
{
throw "Identity Missing";
}
if (!$Path)
{
throw "Path Missing";
}
if (!(Get-Command Get-User))
{
throw "Exchange Management Shell not loaded";
}
$User = Get-User $Identity -ErrorAction SilentlyContinue
if (!$User)
{
throw "User $($Identity) not found";
}
if (!(Test-Path -Path $Path))
{
throw "File $($Path) not found";
}
$FileData = [Byte[]]$(Get-Content -Path $Path -Encoding Byte -ReadCount 0);
if($FileData.Count -gt 10240)
{
throw "File size must be less than 10K";
}
$adsiUser = [ADSI]"LDAP://$($User.OriginatingServer)/$($User.DistinguishedName)";
$adsiUser.Put("thumbnailPhoto",$FileData);
$adsiUser.SetInfo()
Geoff,
ReplyDeleteAt first I got excited about Interact/AD photo integration, but then realized you were importing into AD. I was looking for the opposite - to have Interact pull out the AD pictures. Apparently it's on their to-do list, but no indication of a release date yet.
To simplify the import and association of roughly 550 user photos into our new intranet, I named the pictures using FirstnameSurname.jpg, for each staff member. Then, I bulk uploaded the photos into the People media gallery.
So, now I had the pictures ready to go, but was dreaging the task of linking them with 500 people. We didn't want to let staff upload their own pics on launch, so we needed to do this first.
Digging into the database, I seems like the pictures are handled by 2-3 "asset" tables, and the people are in the "Person" table. It turns out that only one field is needed to link a person with their picture, since the site will automatically determine the appropriate size when displaying the people directory.
So, this query worked to match up the vast majority of the users:
UPDATE person
SET person.assetID = asset.assetID
FROM ASSET
WHERE
person.Active=1
AND person.Archived=0
AND Asset.TypeID=5
AND asset.Title=(person.Firstname + Person.Surname)
It's a simple script, and will not link those users with more complex last names (ie. extra spaces, or apostrophes). In my case, it matched 500 pictures. 25 more needed a manual intervention, and another 15-ish didn't have pictures in the collection uploaded.
The time spent writing the script was far shorter than the time we would have spent matching images to people.
I hope this will help someone else along the way.
Hey Peter, where do your users add their photos into AD? We had been importing them via a batch job which picked up the images off a folder on the server but Interact gave us a better interface to let users do it all themself.
ReplyDeleteThanks for the comments!
We don't allow our users to upload their own photos - it is controlled by Human Resources. Our I.T. have set them up to use Ithicos tools, although I'm certain which module off the top of my head. http://www.ithicos.com/
ReplyDelete