Hello, I want just (into OTRS Admin > SQL Box) to have a SQL query to export in CSV or Excel all tickets with, ticket by ticket all « Articles » (by No.). Best, S

Le 14 déc. 2016 à 10:13, Susan Dittmar a écrit :

Hi Stéphane!

Am 14.12.2016 um 09:13 schrieb Stéphane Pouyllau:

...

I think is a « classic » question but I want to export all items of all tickets to CSV file. What’s the SQL query ? Is there a guideline or an OTRS SQL queries « almanac » ?

I vaguely remember CSV export capabilities of SQL, though I'm not sure whether those commands are SQL standard or brand-specific extensions. Before I go on a hunt for those, some questions.

What database backend (which database server software) do you use?

Do you need to group information by ticket?

What do you want to archieve? Do you want to have some kind of human-readable backup? In that case, is CSV necessary or would another human-readable format work for you?

Susan --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/mailman/listinfo/otrs

-- Stéphane POUYLLAU Ingénieur de recherche CNRS Directeur technique d'Huma-Num http://www.huma-num.fr http://humanum.hypotheses.org Twitter : http://twitter.com/spouyllau Blog perso : http://blog.stephanepouyllau.org

Hello, We use MySQL. Best, S.

Le 16 déc. 2016 à 14:13, Susan Dittmar a écrit :

Hi Stéphane!

Am 16.12.2016 um 13:01 schrieb Stéphane Pouyllau:

...

I want just (into OTRS Admin > SQL Box) to have a SQL query to export in CSV or Excel all tickets with, ticket by ticket all « Articles » (by No.).

So you want to use the OTRS interface to your database. OK. But that still does not tell me what database you are using; OTRS supports several of those. As far as I know export capabilities are different for different database servers.

I'm far from being a database expert. Up to now I only encountered CSV exports into files. Thus possibly your restriction to the OTRS SQL box makes the whole thing impossible.

Sorting the export on a per-ticket basis isn't easy when restricted to one single SQL query btw.

Susan --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/mailman/listinfo/otrs

-- Stéphane POUYLLAU Ingénieur de recherche CNRS Directeur technique d'Huma-Num http://www.huma-num.fr http://humanum.hypotheses.org Twitter : http://twitter.com/spouyllau Blog perso : http://blog.stephanepouyllau.org

Thanks Susan. Best, S.

Le 18 déc. 2016 à 11:21, Susan Dittmar a écrit :

Hi Stéphane,

as far as I know MySQL can export CSV tables, but only on a per-table-basis (which is fine, as CSV cannot handle multiple tables). So first you need a list of all tables. The SQL commands to get that is:

use otrs; show tables;

In case of OTRS 5s this list is:

+------------------------------+ | Tables_in_otrs | +------------------------------+ | acl | | acl_sync | | article | | article_attachment | | article_flag | | article_plain | | article_search | | article_sender_type | | article_type | | auto_response | | auto_response_type | | cloud_service_config | | customer_company | | customer_preferences | | customer_user | | dynamic_field | | dynamic_field_value | | follow_up_possible | | generic_agent_jobs | | gi_debugger_entry | | gi_debugger_entry_content | | gi_object_lock_state | | gi_webservice_config | | gi_webservice_config_history | | group_customer_user | | group_role | | group_user | | groups | | link_object | | link_relation | | link_state | | link_type | | mail_account | | notification_event | | notification_event_item | | notification_event_message | | package_repository | | personal_queues | | personal_services | | pm_activity | | pm_activity_dialog | | pm_entity_sync | | pm_process | | pm_transition | | pm_transition_action | | postmaster_filter | | process_id | | queue | | queue_auto_response | | queue_preferences | | queue_standard_template | | role_user | | roles | | salutation | | scheduler_future_task | | scheduler_recurrent_task | | scheduler_task | | search_profile | | service | | service_customer_user | | service_preferences | | service_sla | | sessions | | signature | | sla | | sla_preferences | | smime_signer_cert_relations | | standard_attachment | | standard_template | | standard_template_attachment | | system_address | | system_data | | system_maintenance | | ticket | | ticket_flag | | ticket_history | | ticket_history_type | | ticket_index | | ticket_lock_index | | ticket_lock_type | | ticket_loop_protection | | ticket_priority | | ticket_state | | ticket_state_type | | ticket_type | | ticket_watcher | | time_accounting | | user_preferences | | users | | valid | | virtual_fs | | virtual_fs_db | | virtual_fs_preferences | | web_upload_cache | | xml_storage | +------------------------------+

Most of those tables you will only need to export when you change general settings.

To export one table as CSV you can either use MySQL directly with something like

SELECT * FROM put_in_table_name_here INTO OUTFILE 'put_in_full_path_to_file_here' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';

Or you can use OTRS's SQL box with

Query: SELECT * FROM put_in_table_name_here Limit: something bigger than expected number of rows format: CSV

In both cases the SELECT statement can be more complex and can contain columns from several tables. Syntax is something like that:

SELECT list_of_columns_separated_by_comma FROM list_of_tables_separated_by_comma WHERE list_of_restrictions_as_boolean_expression

In this case you need to decide which columns you need and how to combine those tables (which means how to decide which row(s) of the first table to combine with which row(s) of the second table). I wouldn't want to try to re-combine the complete ticket structure. There's good reason why it has been split into several tables by the developers. It is possible though.

Now it's up to you to decide what exactly you need and to put it together.

Susan

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/mailman/listinfo/otrs

-- Stéphane POUYLLAU Ingénieur de recherche CNRS Directeur technique d'Huma-Num http://www.huma-num.fr http://humanum.hypotheses.org Twitter : http://twitter.com/spouyllau Blog perso : http://blog.stephanepouyllau.org