How can we view/extract our HipChat data aes file?

[Christopher Emerson]

I exported our HipChat data into some *.aes file using your Data Export - but how can I open, view or use this data? Can we convert to a Excel or Word doc or something?

[davy · Accepted solution]

Hi Chris,

The exported archive first needs to be decrypted.
This can be done with the command (on the Hipchat server or on a linux/mac system)

openssl aes-256-cbc -d -in path_to_encrypted_file -out path_to_decrypted_file -pass pass:your_password

Afterwards the archive can be extracted
The data files are in json format, which if needed can be converted to csv.

(source: https://confluence.atlassian.com/hc/exporting-and-importing-your-hipchat-data-688882302.html)

Best Regards,

[Christopher Emerson]

I see that section of the article (below) but can you please give me an example of what I'd type in Hipchat exactly? is the in path a HTTP path and the out path a C:\ path? An example would make this clear.

[davy]

Hi Chirs,

When I mentioned that the decrypt command can be run on the Hipchat server, I was referring to the terminal interface of an Hipchat server instance (which runs Linux)

Below is an example how to decrypt the aes file
(note: this is on a terminal interface on Linux)

Let say you have the .aes file, hipchat-2018-08-24_09-07-06.tar.gz.aes, in the directory /home/admin

[/home/admin> ls -al
-rw-r--r-- 1 admin admin 12448 Aug 24 09:12 hipchat-2018-08-24_09-07-06.tar.gz.aes

we can extract the .aes file with the command

[/home/admin> openssl aes-256-cbc -d -in /home/admin/hipchat-2018-08-24_09-07-06.tar.gz.aes -out /home/admin/hipchat-2018-08-24_09-07-06.tar.gz -pass pass:hipchat

The decrypt will give us the unencrypted file hipchat-2018-08-24_09-07-06.tar.gz

[/home/admin> ls -al
-rw-r--r-- 1 admin admin 12419 Aug 24 09:17 hipchat-2018-08-24_09-07-06.tar.gz
-rw-r--r-- 1 admin admin 12448 Aug 24 09:12 hipchat-2018-08-24_09-07-06.tar.gz.aes

The file hipchat-2018-08-24_09-07-06.tar.gz can be extracted with the command

[/home/admin> tar xfv hipchat-2018-08-24_09-07-06.tar.gz

Is you use windows, you will need to install a separate application to decrypt the encrypted .aes file (as far as I know windows is not able to decrypt aes files on itself)
On https://www.aescrypt.com/download/ a few applications are mentioned that are available on windows, unfortunately I'm not familiar with them.

Hope this helps,

Best Regards,

[Christopher Emerson]

First one I tried AESCrypt throws this error when I try to open/decrypt:

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

AES Crypt Error

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

Invalid input file C:\Users\Administrator\Downloads\hipchat-252690-2018-08-24_09-52-09.tar.gz.aes

Invalid signature

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

OK  

Will try another one

[Christopher Emerson]

I tried the 64bit, 32bit and console versions of AEScrypt for Windows but none of them worked. I eventually got the file extracted via the openssl commands on a mac machine. Have to say that was incredibly tedious/complicated for something like this (and I'm a fairly experienced computer user/developer). I find it surprising that this is what you guys are putting Hipchat users through. 

Anyway thanks for the help - you can close this ticket.

[davy]

Hi Chris,

Glad you where able to decrypt the archive.

Sorry to hear you had such trouble with the archive.
The Hipchat export archive was only intended to be used on the Hipchat platform, which uses Linux
Unfortunately we never put a process in place to decrypt the archive on windows.

Best Regards,

[Sandeep Srinivasa]

im having trouble decrypting it on my fedora 28 laptop using the exact same command. i tried it 3 times with different exports. 

the error i get is 

bad decrypt
139670421636928:error:06065064:digital envelope routines:EVP_DecryptFinal_ex:bad decrypt:crypto/evp/evp_enc.c:569:

I then figured out that you guys were using an old and outdated version of openssl to encrypt that was using md5. 

openssl 1.1 onwards using sha256 as default. after i added the explicit switch to use md5, it worked. i would recommend updating the documentation.

openssl aes-256-cbc -d -in hipchat-124688-2018-09-19_18-28-04.tar.gz.aes -out hipchat-124688-2018-09-19_18-28-04.tar.gz -pass pass:somepass -md md5

[Simone Riccardi]

you saved my day... I did try several time following hipchat documentations and fortunately community is one step ahead. Thanks @Sandeep Srinivasa!

BTW I migrated to rocket.chat that's an amazing place to go, instead of being forced to Slack ;)

[David Jones]

I got it to work on windows 

1. Download openssl for windows https://slproweb.com/products/Win32OpenSSL.html

2. Open an admin command prompt and navigate to the bin folder 

3. openssl aes-256-cbc -d -in path\inputfile.aes -out path\outputfile.gz -pass pass:yourpassword -md md5

4. Use 7zip or your favorite windows archive tool to unpack into a tar and then unpack the tar into a folder and viola you have the whole archive on your windows machine