How to decode content (IMG or file) from Cisco ECE EGML_EMAIL_ATTACHMENT table.

[AntonFiliptsov5806]

In EGML_EMAIL_ATTACHMENT table i can see list of attachments from ECE and all attachments are encoded.
It says ENCODING_TYPE -> Base64. But it's not a regular Base64. When i try to decode with Base64 i am getting a broken file.
Does anyone knows how to decode them and get a file?

This is an example of one of them in DB.

[kadek.sena]

Hi, do you by any mean get the solution for this?

[Konstantin Vaksin]

Hello,

You can always grab attachment via API.
You can find details here https://pubhub.devnetcloud.com/media/enterprise-chat-and-email/docs/downloads/previous-documentation/interaction-api-developer-guide/ece_11_6_es4_interaction_api_guide.pdf

Kostia

[piyush aghera]

Hi,

I looked at the API document but couldnt figure out how to get actual attachment file using only APIs.  Could you please elaborate more and give an example.

I guess attachment files are stored in binary format in SQL DB.

Thanks.

[jritmanis]

Actually the information that the encoding is Base 64 is misleading. The attachment contents are saved into DB simply as Hex values.
To convert the DB saved string back to file contents, for example in PHP - remove the first "0x" and then use hex2bin() function on that string.

[piyush aghera]

Hi.. have you tried this yourself ?  Because as per definition of hex2bin() php function, it converts a string of hexadecimal values to ASCII characters.

I tried using it and what I get is just a garbage value string rather than a file.

[jritmanis]

Yes have tried this and it works.
Actually today I was also looking for the solution because the API's does not really provide me with the features I need. So found this post and tried some "reverse engineering" to understand how this works, and after few trial-by-error's found a solution that seems to be working. 

Just tried few more examples and it works - at the end is a function that works even better then the built in hex2bin() .

Example (see attached screenshot in addition to the steps):

1. The Hex sting starts with: 0x4F6767530002000000000000000000000000000000002A82068201134F707573486561..

2. Then I removed the 0x and processed it via my simple php script

3. The result came out as Ogg file

4. It can be played via some media players and MediaInfo also recognizes it

What is interesting - for few attachements I received error from php hex2bin function: Warning: hex2bin(): Hexadecimal input string must have an even length...
Not sure why. Maybe there are some limitations...
When replacing the hex2bin() function with manually written one it seems to be working on all my tested examples:
function hexToStr($hex){
 $string='';
 for ($i=0; $i < strlen($hex)-1; $i+=2){
     $string .= chr(hexdec($hex[$i].$hex[$i+1]));
   }
  return $string;
}

P.S. running ECE version 12.6. Maybe other versions are encoding the attachments differently. 

[bill.king1]

Not sure if it helps you, but two defects you may be interested in.
One is re: an enhancement request to more easily view attachments. I'm imaging it is more re: the agent side of things, but perhaps could also pertain to what you're looking to do.
The second is about how sometimes attachments are encoding problematically, so not sure if that's the intermittent scenario you're running into? There are a few with this same behavior.

https://bst.cloudapps.cisco.com/bugsearch/bug/CSCvr23587
https://bst.cloudapps.cisco.com/bugsearch/bug/CSCvu57148

[piyush aghera]

Thanks for this information.

After fighting with php and code for couple of hours (not a good programmer.. lol), i finally started to get output file generated. Text files worked fine, but image files are giving error that the file is either damaged or corrupted.  File has accurate size as it should have, but picture won't open.  Have you tried with pictures - jpg or png - and did it work ?

I also got warning as you had, and after few search on the internet; i found that adding 0 to the string makes that error go away and file is generated.  I tried your function, but couldn't get it to work (again, not a good programmer), so if you could tell me more about that function, i.e., how to give input, how to generate output file, then i can give it another try.

[jritmanis]

Yes, can confirm, that this works also for image files.

Currently my test code in php is:

<?php

$input = file_get_contents('infile.txt');
file_put_contents('out.jpg', hexToStr($input));

function hexToStr($hex){
    $string='';
    for ($i=0; $i < strlen($hex)-1; $i+=2){
        $string .= chr(hexdec($hex[$i].$hex[$i+1]));
    }
    return $string;
}

 One thing to notice - if you are using MS SQL Studio, to get manually data from attachment table - it has a limit of something of 65.5k symbols, that can be returned for non-XML data results. But this limit is configurable under Tools->Options->Query results -> SQL Server -> Results to Grid -> "Non XML data" up to around 2M symbols. 
Maybe in your tests you did copy only partially the encoded file contents as images tend to be quite large in size.

[piyush aghera]

Thank you!!

But still no luck for me.. my test file is of 7 Kb, and the symbols in the SQL field is 13919. so i think data is not truncated in the resultset.  however i still get the same error that the file is either damaged or corrupted. Could it be because of ECE version ?? I am on ECE 11.6 ES12.

[jritmanis]

Would be strange, that different encodings would be used for different types of files.

I would suggest to try to find the truth by "reverse engineering" - try to send in a known image file, and try to encode it also manually to hex, for example via the php bin2hex() function. Then compare the results - what is saved in ECE DB and what is the result from manual conversion. Then also try to convert both back to original format and see if the file contents match. Maybe some symbols or some metainformation is added to the contents field in DB for image files ... just guessing. 

[piyush aghera]

Thank you.. those are some really good inputs. I was already working on it and here are my findings.

First of all, I cannot convert any of the file types from ECE 11.6 ES12, be it jpg, png, pdf or txt. All of them give same error, no matter what file size it is.

So I sent a test email with a very small pdf, 28 kb. It got ingested into ECE and attachment content saved in ECE table.  I took that and inserted into PHP code, which generated PDF of 22 kb and didnot open.  Next I took that same PDF and reversed PHP code to create hex from the file using bin2hex() function.  I got a text file with hex code which was totally different than what was stored in ECE table.  That threw me off right there.  And as expected, when I inserted that hex code to original PHP code for hex2bin, it gave me a 28 kb pdf file which worked just fine.

So I think it boils down to how data is stored in ECE for different version is different. For example, screen shot in original question shows value of "encoding_type" as "Base 64", whereas my DB shows "null".  I am not sure what version is used in original question.  I will try to dig deep into this part, dont know if enough information is documented because there is very little information available for ECE DB schemas.

Regards,

Piyush Aghera

BLOG

[kadek.sena]

Hi, i made this one using python, so far it works well for chat attachments like images and videos, but it still doesn't work with images or videos for email attachments, but i try some pdfs and looks like it sometimes works. It's best for chat attachments tho.

You can try this out here : https://github.com/helenaferdy/HEX2BIN.git

I'll update it if i somehow find a way to make it work for all the files.

[piyush aghera]

Thanks.  what is the version of ECE you have ?  What value do you see under "encoding_type" ?

Thanks,
Piyush A

BLOG