Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

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)))

Next

stream.savetofile FilePath, adSaveCreateOverWrite

stream.close

OctetStringToFile = "processed"

End Function

QlikCommunity_Thread_UserPics AD_Pic1.JPG.jpg

QlikCommunity_Thread_UserPics AD_Pic2.JPG.jpg

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

Tags (1)
4 Replies

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

Anybody?

johncaqc
Valued Contributor

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

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...

johnca.jpgSize on disk: 4.00 KB (4,096 bytes)

And here's another...

c_nroman.jpgSize on disk: 4.00 KB (4,096 bytes)

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

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

johncaqc
Valued Contributor

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

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