topic Re: Importing user picture ressource from AD using OLE DB Provider for Microsoft Directory Services in QlikView

Importing user picture ressource from AD using OLE DB Provider for Microsoft Directory Services

MarcoWedel — Sun, 09 Mar 2014 02:25:16 GMT

Hi everyone,

I recently tried to import user pictures from Microsoft Active Directory to personalize a QV application (logged on users should see their AD picture).

While doing so, I encountered a problem importing and converting the thumbnailPhoto active directory attribute.

ThumbnailPhoto in this case contains a user picture (JPG) coded as an octet string.

Unlike the expected string length of some kB I only got a string length of 511bytes leading to a corrupt jpg file as most of the information was missing.

Nevertheless I tried to import this picture stub and decided to work on the string length issue later on.

As I could not load the picture ressource directly from the string, I previously saved it to disk.

The conversion and saving was done using vbs.

Here are the load script and vbs function:

OLEDB CONNECT32 TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Data Source=<ADserverName>;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

tabADPhoto:

SQL select

SN,

givenName,

sAMAccountName,

thumbnailPhoto

from 'LDAP://<domainname>.com'

where

sAMAccountName = '<useraccount>';

Left Join

LOAD

sAMAccountName,

len(thumbnailPhoto) as LenThumbnailPhoto

Resident tabADPhoto;

tabTemp:

LOAD

sAMAccountName,

OctetStringToFile(thumbnailPhoto, 'U:\Qlikview\'&sAMAccountName&'.jpg') as ResultOctetStringToFile

Resident tabADPhoto;

Bundle Info LOAD

sAMAccountName,

'U:\Qlikview\'&sAMAccountName&'.jpg'

Resident tabADPhoto;

------------------------------------------------------------------------

Function OctetStringToFile(OctetString, FilePath)

Const adTypeText = 2

Const adSaveCreateOverWrite = 2

LenData = Len(OctetString)\2 + Len(OctetString) Mod 2

set fso = createobject("scripting.filesystemobject")

set stream = createobject("adodb.stream")

stream.type = adTypeText

stream.charset = "windows-1252"

stream.open

For i = 0 to LenData-1

stream.writetext chr(CByte("&h" & Mid(OctetString, i*2+1, 2)))

stream.savetofile FilePath, adSaveCreateOverWrite

stream.close

OctetStringToFile = "processed"

End Function

Any ideas as to why the thumbnailPhoto attribute octet string is truncated or whether there is a more straight forward approach for this picture ressource import?

thanks in advance

Marco

Re: Importing user picture ressource from AD using OLE DB Provider for Microsoft Directory Services

MarcoWedel — Sun, 16 Mar 2014 22:37:10 GMT

Anybody?

Re: Importing user picture ressource from AD using OLE DB Provider for Microsoft Directory Services

johnca — Thu, 22 Jan 2015 23:34:33 GMT

Hi Marco,

I'd like to do the same. I thought it may be similar to the limit of number of users you can get at one time, but looking at the pictures I did get they're all 4kb or 0kb (Blank). Some are full pictures though, so it must depend on the image size to begin with. Here's a comparison...note the resolution and sizes.

Here's mine...

Size on disk: 4.00 KB (4,096 bytes)

And here's another...

Size on disk: 4.00 KB (4,096 bytes)

Re: Importing user picture ressource from AD using OLE DB Provider for Microsoft Directory Services

MarcoWedel — Thu, 22 Jan 2015 23:51:41 GMT

Hi,

thanks for the reply.

I did not pursue this topic any further, but I do think it's a limitation of the AD / OLE DB provider for Microsoft Directory Services.

Unfortunately I have no clue as to how this limitation can be circumvented.

Maybe by loading parts of the data in a loop and concatenate afterwards.

There are posts that describe loading other AD account information in a loop because of those limitations.

If someone found a solution, I would be interested in learning from it.

regards

Marco

Re: Importing user picture ressource from AD using OLE DB Provider for Microsoft Directory Services

johnca — Fri, 23 Jan 2015 15:42:24 GMT

Yes, I believe that limitation is 999 or 1000 records, per request. I had to loop through each last name letter to get everyone. Yours is the first attempt I've seen to get the thumbnails, and although imperfect as it is its a good start.

--john