How to download attachment from database

Moderator: crythias

Post Reply
sumitsahaymca
Znuny newbie
Posts: 8
Joined: 06 Feb 2018, 10:46
Znuny Version: OTRS5

How to download attachment from database

Post by sumitsahaymca »

Hi All,

I want to download the attachment from otrs database.

Regards
Sumit Sahay
sumitsahay.mca@gmail.com..
seberget2
Znuny newbie
Posts: 74
Joined: 17 May 2013, 09:30
Znuny Version: 6.0.30
Real Name: Stein Erik Berget

Re: How to download attachment from database

Post by seberget2 »

sumitsahaymca wrote:I want to download the attachment from otrs database.
You would like to download how? With the Perl API? Doing a SQL call directly to the DB? With a REST call or something else? We need a little bit more context to help you into the right direction.

Have a happy day!
OTRS 6.0.12 on Ubuntu with MySQL DB, and various plug-ins and a hack or two :-D
sumitsahaymca
Znuny newbie
Posts: 8
Joined: 06 Feb 2018, 10:46
Znuny Version: OTRS5

Re: How to download attachment from database

Post by sumitsahaymca »

seberget2 wrote:
sumitsahaymca wrote:I want to download the attachment from otrs database.
You would like to download how? With the Perl API? Doing a SQL call directly to the DB? With a REST call or something else? We need a little bit more context to help you into the right direction.

Have a happy day!
Thanks for your reply.
I want to download attachment from DB by doing SQL Queries only.
Is it possible to download the document without any perl script.

Looking forward for your reply.

Thanks & Regards
Sumit Sahay
sumitsahay.mca@gmail.com.
jojo
Znuny guru
Posts: 15019
Joined: 26 Jan 2007, 14:50
Znuny Version: Git Master
Contact:

Re: How to download attachment from database

Post by jojo »

Why do you want to download the attachments? What do you want to do with the content?
"Production": OTRS™ 8, OTRS™ 7, STORM powered by OTRS
"Testing": ((OTRS Community Edition)) and git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
sumitsahaymca
Znuny newbie
Posts: 8
Joined: 06 Feb 2018, 10:46
Znuny Version: OTRS5

Re: How to download attachment from database

Post by sumitsahaymca »

jojo wrote:Why do you want to download the attachments? What do you want to do with the content?
Thanks for your reply...

I am doing integration of otrs with ansible, our task is to execute the ansible playbook from otrs and will do the changes in remote system, we are able to login to otrs db through ansible playbook and the same time able to query article and all details of the ticket, now want to download the attachment from otrs db by querying only in db so that I 'll use that attachment file in ansible.

Note: all the queries are in my playbook.

Looking forward for your reply...

Thanks & Regards
Sumit Sahay
seberget2
Znuny newbie
Posts: 74
Joined: 17 May 2013, 09:30
Znuny Version: 6.0.30
Real Name: Stein Erik Berget

Re: How to download attachment from database

Post by seberget2 »

sumitsahaymca wrote:I am doing integration of otrs with ansible, our task is to execute the ansible playbook from otrs and will do the changes in remote system, we are able to login to otrs db through ansible playbook and the same time able to query article and all details of the ticket, now want to download the attachment from otrs db by querying only in db so that I 'll use that attachment file in ansible.
The table you would like to look at is called article_attachment. However. There are two ways OTRS stores attachements. The default setup stores everything as a blob in the DB (in the table mentioned). The other as files on the file system. So here you must look at how your OTRS does it.

Please do not update the OTRS database from ansible or any other third-party tool if you do not know 110 % what you are doing. Read only access to OTRS should be fine.

Messing about with more or less direct access to the OTRS DB is like playing with guns, it helps to know which end is the dangerous one. It is OKish to shot yourself in the foot, blowing your head off not so much... You have been warned!


Have a happy day!
OTRS 6.0.12 on Ubuntu with MySQL DB, and various plug-ins and a hack or two :-D
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: How to download attachment from database

Post by root »

Hi,

I recommend accessing the ticket data, including articles and attachments, via GenericInterface. By using this (REST or SOAP) you don't have to take care which backend is used.

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
sumitsahaymca
Znuny newbie
Posts: 8
Joined: 06 Feb 2018, 10:46
Znuny Version: OTRS5

Re: How to download attachment from database

Post by sumitsahaymca »

seberget2 wrote:
sumitsahaymca wrote:I am doing integration of otrs with ansible, our task is to execute the ansible playbook from otrs and will do the changes in remote system, we are able to login to otrs db through ansible playbook and the same time able to query article and all details of the ticket, now want to download the attachment from otrs db by querying only in db so that I 'll use that attachment file in ansible.
The table you would like to look at is called article_attachment. However. There are two ways OTRS stores attachements. The default setup stores everything as a blob in the DB (in the table mentioned). The other as files on the file system. So here you must look at how your OTRS does it.

Please do not update the OTRS database from ansible or any other third-party tool if you do not know 110 % what you are doing. Read only access to OTRS should be fine.

Messing about with more or less direct access to the OTRS DB is like playing with guns, it helps to know which end is the dangerous one. It is OKish to shot yourself in the foot, blowing your head off not so much... You have been warned!


Have a happy day!
Thanks for your reply.

We are using Generic Agent for triggering the Ansible Playbook, I 'll give you one example of Ticket Command that we are using here below for better understanding,
echo ARG[0]>& /tmp/otrs_ticketnumber.csv : `ssh user_name@Host_Addressh ansible-playbook -i /path/of_file/hosts otrs_ansible_integration.yml >& /tmp/ping.log`

The playbook only contain mysql queries only for retrieving the ticket details as per the ticket id.
If we change the Storage configuration for attachment from DB to FS then we are getting the file in file system inside article directory, in that case we can not get the file name in DB as per my understanding, and we have to pass the hard coded file name in our Ansible Playbook.
And if we use the default functionality of storage attachment then we will get the file name in db as per the ticket_id and article_id.
That is why I want to download the file from db by making the sql queries only as per ticket_id and article_id and all the sql queries inside playbook.
In article_attachment table we are getting the attachment name and attachment content, so is there any sql queries to download the attachment file from db only by making queries.

Let me know your concern.

Looking forward for your reply.

Thanks & Regards
Sumit Sahay
sumitsahaymca
Znuny newbie
Posts: 8
Joined: 06 Feb 2018, 10:46
Znuny Version: OTRS5

Re: How to download attachment from database

Post by sumitsahaymca »

root wrote:Hi,

I recommend accessing the ticket data, including articles and attachments, via GenericInterface. By using this (REST or SOAP) you don't have to take care which backend is used.

- Roy
Thanks for your reply.
We are using Generic Agent only for running our ansible playbook. In playbook we are using only mysql queries to retrieve the ticket data we are getting everything of particular ticket except the attachment.
Is there any way to skip using REST or SOAP?

Looking forward for your reply.

Thanks & Regards
Sumit Sahay
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: How to download attachment from database

Post by root »

Hi,
sumitsahaymca wrote: Is there any way to skip using REST or SOAP?
Yes, you can access the data by using the Perl API.

-Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
sumitsahaymca
Znuny newbie
Posts: 8
Joined: 06 Feb 2018, 10:46
Znuny Version: OTRS5

Re: How to download attachment from database

Post by sumitsahaymca »

root wrote:Hi,
sumitsahaymca wrote: Is there any way to skip using REST or SOAP?
Yes, you can access the data by using the Perl API.

-Roy
Hi Roy,

http://doc.otrs.com/doc/api/otrs/5.0/Pe ... nt.pm.html
Am I correct?
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: How to download attachment from database

Post by root »

Hi,
Definitely wrong. See ArticleAttachmentIndex() of a ticket object. Anyway, I recommend using REST: a simple TicketGet request with AllArticles returns the complete tickets with all articles and attachments. Accessing the JSON structure is possible with jq in a script context.

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
Post Reply