How to download attachment from database

Moderator: crythias

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

How to download attachment from database

Post by sumitsahaymca » 16 Feb 2018, 13:33

Hi All,

I want to download the attachment from otrs database.

Regards
Sumit Sahay
sumitsahay.mca@gmail.com..

seberget2
OTRS expert
Posts: 71
Joined: 17 May 2013, 09:30
OTRS Version?: 6.0.4
Real Name: Stein Erik Berget

Re: How to download attachment from database

Post by seberget2 » 16 Feb 2018, 18:18

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.4 on Ubuntu with MySQL DB, and various plug-ins and a hack or two :-D

sumitsahaymca
OTRS newbie
Posts: 8
Joined: 06 Feb 2018, 10:46
OTRS Version?: OTRS5

Re: How to download attachment from database

Post by sumitsahaymca » 19 Feb 2018, 06:37

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
Moderator
Posts: 14195
Joined: 26 Jan 2007, 14:50
OTRS Version?: Git Master
Contact:

Re: How to download attachment from database

Post by jojo » 19 Feb 2018, 07:21

Why do you want to download the attachments? What do you want to do with the content?
"Production": OTRS Business Solution™ 6, STORM powered by OTRS
"Testing": OTRS git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com :: Share your ideas

sumitsahaymca
OTRS newbie
Posts: 8
Joined: 06 Feb 2018, 10:46
OTRS Version?: OTRS5

Re: How to download attachment from database

Post by sumitsahaymca » 19 Feb 2018, 07:53

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
OTRS expert
Posts: 71
Joined: 17 May 2013, 09:30
OTRS Version?: 6.0.4
Real Name: Stein Erik Berget

Re: How to download attachment from database

Post by seberget2 » 19 Feb 2018, 15:07

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.4 on Ubuntu with MySQL DB, and various plug-ins and a hack or two :-D

root
Moderator
Posts: 1162
Joined: 18 Dec 2007, 12:23
OTRS Version?: 4/5/6
Real Name: Roy Kaldung
Company: Znuny Inc.
Contact:

Re: How to download attachment from database

Post by root » 19 Feb 2018, 22:03

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
OTRS 4/5/6 CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

You need professional services? Check out http://znuny.com/

sumitsahaymca
OTRS newbie
Posts: 8
Joined: 06 Feb 2018, 10:46
OTRS Version?: OTRS5

Re: How to download attachment from database

Post by sumitsahaymca » 20 Feb 2018, 06:57

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
OTRS newbie
Posts: 8
Joined: 06 Feb 2018, 10:46
OTRS Version?: OTRS5

Re: How to download attachment from database

Post by sumitsahaymca » 20 Feb 2018, 07:11

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
Moderator
Posts: 1162
Joined: 18 Dec 2007, 12:23
OTRS Version?: 4/5/6
Real Name: Roy Kaldung
Company: Znuny Inc.
Contact:

Re: How to download attachment from database

Post by root » 20 Feb 2018, 07:21

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
OTRS 4/5/6 CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

You need professional services? Check out http://znuny.com/

sumitsahaymca
OTRS newbie
Posts: 8
Joined: 06 Feb 2018, 10:46
OTRS Version?: OTRS5

Re: How to download attachment from database

Post by sumitsahaymca » 20 Feb 2018, 07:25

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
Moderator
Posts: 1162
Joined: 18 Dec 2007, 12:23
OTRS Version?: 4/5/6
Real Name: Roy Kaldung
Company: Znuny Inc.
Contact:

Re: How to download attachment from database

Post by root » 20 Feb 2018, 08:09

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
OTRS 4/5/6 CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

You need professional services? Check out http://znuny.com/

Post Reply