Export data - export-v2.php
: document the Excel (XLSX) format
Purpose
iTop provides a generic web service to export whatever data based on an OQL query. This service can be used to extract data from iTop in order to integrate with third party tools. This web service can return data in several formats:
-
HTML
-
CSV
-
XML
-
Spreadsheet (plain HTML for importing into Excel or OpenOffice)
-
XLSX (Excel 2007, 2010, 2013…)
-
PDF
This service can be called locally on the iTop server via the command line interface, or remotely via an HTTP request.
For 100% backward compatibility with previous versions of iTop, you can use the old Export data - export.php page.
Usage
Command line interface
php /var/www/itop/webservices/export-v2.php --auth_user=login --auth_pwd=password --expression=query --format=csv --fields=<list of fields separated by a comma>
Remote web access
There are several ways to access the service remotely via a web connection. Here is the syntax to use with the wget command (if the Basic Authentication is enabled):
wget --http-user=login --http-password=password http://itopserver/webservices/export-v2.php?expression=query&format=csv&fields=<list of fields separated by a comma>
Arguments
Argument | Description | Defaut value |
---|---|---|
param_file | Parameters file - see Parameters file | - |
auth_user | User login - CLI mode only | - |
auth_pwd | User password - CLI mode only | - |
expression | Object Query Language Reference query | Mandatory if 'query' is missing |
query | Identifier of a query stored in the so-called query phrasebook. See Query Phrasebook | Mandatory if 'expression' is missing |
format | spreadsheet (recommended for
integration with MS-Excel web queries), html ,
xml , csv or xlsx |
html |
fields | coma separated list of attributes (e.g. “name,brand,model”). Extended attribute codes (e.g. caller_id->org_id->code) are supported but may significantly slow down the export (since they require additional database queries). Use with care! | All the attributes code of the class specified by <expression>. Those one are not localized |
with_archive | 1 : the export will include archived
objects. 0 or if argument omitted: it does not return
archived objects |
0 |
arg_xxx | Needed if the OQL query has parameters: the value of the parameter 'xxx' | none |
fields
parameter.Output
HTML
Spreadsheet
This format is dedicated to the integration into spreadsheets. In particular, it is the recommended method to use with Excel web queries. Refer to the article How to create an Excel report using iTop queries for more information about this Excel integration.
Format specific parameters
When using the Spreadsheet format, the following additional parameters are available:
Parameter name | Description | Default value |
---|---|---|
date_format | The format to use for exporting date and time fields (specified using PHP date & time specifiers). | Same as in the GUI |
formatted_text | Whether or not to export rich text fields with their HTML markup. 1 = export the markup, 0 = no markup. | 1 |
no_localize | If set to 0, then header and values are supposed to be localized in the language of the logged in user. Set to 1 to use internal attribute codes and values (enums) | 0 |
XML
fields
parameter
is ignored when exporting in XML format, since all fields of the
target class(es) are always retrieved. The number of fields can
vary from one object in the set to another when the query is
performed on a class of objects which has subclasses. The export
can also include the relations when called with the parameter
linksets=1
, as shown below.<?xml version="1.0" encoding="UTF-8"?> <Set> <Server alias="Server" id="1"> <name>Server1</name> <description></description> <org_id>3</org_id> <org_id_friendlyname>Demo</org_id_friendlyname> <organization_name>Demo</organization_name> <business_criticity>low</business_criticity> <move2production></move2production> <contacts_list> <Set> <lnkContactToFunctionalCI id="1"> <contact_id>3</contact_id> <contact_name>Monet</contact_name> <contact_id_friendlyname>Claude Monet </contact_id_friendlyname> <contact_id_finalclass_recall>Person </contact_id_finalclass_recall> </lnkContactToFunctionalCI> </Set> </contacts_list> <documents_list> <Set> </Set> </documents_list> <applicationsolution_list> <Set> </Set> </applicationsolution_list> <providercontracts_list> <Set> </Set> </providercontracts_list> <services_list> <Set> </Set> </services_list> <softwares_list> <Set> </Set> </softwares_list> <tickets_list> <Set> </Set> </tickets_list> <serialnumber></serialnumber> <location_id>1</location_id> <location_id_friendlyname>Bordeaux</location_id_friendlyname> <location_name>Bordeaux</location_name> <status>production</status> <brand_id>2</brand_id> <brand_id_friendlyname>HP</brand_id_friendlyname> <brand_name>HP</brand_name> <model_id>4</model_id> <model_id_friendlyname>DL380</model_id_friendlyname> <model_name>DL380</model_name> <asset_number></asset_number> <purchase_date></purchase_date> <end_of_warranty></end_of_warranty> <networkdevice_list> <Set> </Set> </networkdevice_list> <physicalinterface_list> <Set> <PhysicalInterface id="1"> <name>eth0</name> <ipaddress>192.168.10.12</ipaddress> <macaddress>FB:0A:AF:00:12:3F:54:FF</macaddress> <comment></comment> <ipgateway>192.168.10.1</ipgateway> <ipmask>255.255.255.0</ipmask> <speed>1000.00</speed> </PhysicalInterface> </Set> </physicalinterface_list> <rack_id>0</rack_id> <rack_id_friendlyname></rack_id_friendlyname> <rack_name></rack_name> <enclosure_id>0</enclosure_id> <enclosure_id_friendlyname></enclosure_id_friendlyname> <enclosure_name></enclosure_name> <nb_u></nb_u> <managementip></managementip> <powerA_id>0</powerA_id> <powerA_id_friendlyname></powerA_id_friendlyname> <powerA_name></powerA_name> <powerA_id_finalclass_recall></powerA_id_finalclass_recall> <powerB_id>0</powerB_id> <powerB_id_friendlyname></powerB_id_friendlyname> <powerB_name></powerB_name> <powerB_id_finalclass_recall></powerB_id_finalclass_recall> <fiberinterfacelist_list> <Set> </Set> </fiberinterfacelist_list> <san_list> <Set> </Set> </san_list> <osfamily_id>6</osfamily_id> <osfamily_id_friendlyname>Linux</osfamily_id_friendlyname> <osfamily_name>Linux</osfamily_name> <osversion_id>8</osversion_id> <osversion_id_friendlyname>Unbuntu 11.10</osversion_id_friendlyname> <osversion_name>Unbuntu 11.10</osversion_name> <oslicence_id>0</oslicence_id> <oslicence_id_friendlyname></oslicence_id_friendlyname> <oslicence_name></oslicence_name> <cpu></cpu> <ram></ram> <logicalvolumes_list> <Set> </Set> </logicalvolumes_list> </Server> <Server alias="Server" id="2"> <name>Server2</name> <description></description> <org_id>3</org_id> <org_id_friendlyname>Demo</org_id_friendlyname> <organization_name>Demo</organization_name> <business_criticity>low</business_criticity> <move2production></move2production> <contacts_list> <Set> </Set> </contacts_list> <documents_list> <Set> </Set> </documents_list> <applicationsolution_list> <Set> </Set> </applicationsolution_list> <providercontracts_list> <Set> </Set> </providercontracts_list> <services_list> <Set> </Set> </services_list> <softwares_list> <Set> <DBServer id="7"> <name>Oracle</name> <description></description> <org_id>3</org_id> <organization_name>Demo</organization_name> <business_criticity>low</business_criticity> <move2production></move2production> <software_id>1</software_id> <software_name>MySql</software_name> <softwarelicence_id>0</softwarelicence_id> <softwarelicence_name></softwarelicence_name> <path></path> <status></status> <finalclass>DB Server</finalclass> <org_id_friendlyname>Demo</org_id_friendlyname> <software_id_friendlyname>MySql 5.3</software_id_friendlyname> <softwarelicence_id_friendlyname></softwarelicence_id_friendlyname> </DBServer> </Set> </softwares_list> <tickets_list> <Set> </Set> </tickets_list> <serialnumber></serialnumber> <location_id>2</location_id> <location_id_friendlyname>Grenoble</location_id_friendlyname> <location_name>Grenoble</location_name> <status>production</status> <brand_id>0</brand_id> <brand_id_friendlyname></brand_id_friendlyname> <brand_name></brand_name> <model_id>0</model_id> <model_id_friendlyname></model_id_friendlyname> <model_name></model_name> <asset_number></asset_number> <purchase_date></purchase_date> <end_of_warranty></end_of_warranty> <networkdevice_list> <Set> </Set> </networkdevice_list> <physicalinterface_list> <Set> </Set> </physicalinterface_list> <rack_id>0</rack_id> <rack_id_friendlyname></rack_id_friendlyname> <rack_name></rack_name> <enclosure_id>0</enclosure_id> <enclosure_id_friendlyname></enclosure_id_friendlyname> <enclosure_name></enclosure_name> <nb_u></nb_u> <managementip></managementip> <powerA_id>0</powerA_id> <powerA_id_friendlyname></powerA_id_friendlyname> <powerA_name></powerA_name> <powerA_id_finalclass_recall></powerA_id_finalclass_recall> <powerB_id>0</powerB_id> <powerB_id_friendlyname></powerB_id_friendlyname> <powerB_name></powerB_name> <powerB_id_finalclass_recall></powerB_id_finalclass_recall> <fiberinterfacelist_list> <Set> </Set> </fiberinterfacelist_list> <san_list> <Set> </Set> </san_list> <osfamily_id>0</osfamily_id> <osfamily_id_friendlyname></osfamily_id_friendlyname> <osfamily_name></osfamily_name> <osversion_id>0</osversion_id> <osversion_id_friendlyname></osversion_id_friendlyname> <osversion_name></osversion_name> <oslicence_id>0</oslicence_id> <oslicence_id_friendlyname></oslicence_id_friendlyname> <oslicence_name></oslicence_name> <cpu></cpu> <ram></ram> <logicalvolumes_list> <Set> </Set> </logicalvolumes_list> </Server> </Set>
Format specific parameters
When using the XML format, the following additional parameters are available:
Parameter name | Description | Default value |
---|---|---|
linksets | Set to 1 to retrieve links to related objects (1-Many or Many-Many relations). 0 means only “scalar” values of the object. | 0 |
no_localize | If set to 0, then header and values are supposed to be localized in the language of the logged in user. Set to 1 to use internal attribute codes and values (enums) | 0 |
CSV
Example of output produced by the CSV format:
id,Name,Management IP 1,"dbserver1.demo.com","10.1.1.10" 6,"webserver.demo.com","10.1.1.20"
Format specific parameters
When using the CSV format, the following additional parameters are available:
Parameter name | Description | Default value |
---|---|---|
charset | The character set to be used for encoding the output. | UTF-8 |
date_format | The format to use for exporting date and time fields (specified using PHP date & time specifiers). | Same as in the GUI |
formatted_text | Whether or not to export rich text fields with their HTML markup. 1 = export the markup, 0 = no markup. | 0 |
no_localize | If set to 0, then header and values are supposed to be localized in the language of the logged in user. Set to 1 to use internal attribute codes and values (enums) | 0 |
separator | The character to be used as the separator between fields in the output | , (comma) |
text-qualifier | The character to be used around text strings | " (double quotes) |
This format produces a PDF document, containing one big table
with the requested data. Note: the automatic layout of the table
makes all columns the same width. Therefore it is best suited for a
small (<10) number of columns. To fit a larger number of
columns, use the Portrait
orientation of the page.
Format specific parameters
When using the PDF format, the following additional parameters are available:
Parameter name | Description | Default value |
---|---|---|
date_format | The format to use for exporting date and time fields (specified using PHP date & time specifiers). | Same as in the GUI |
page_size | The page's paper size to use for the layout. Possible values: A3, A4 or Letter | A4 |
page_orientation | The orientation of the page's paper for the layout. Possible values: Landscape or Portrait | Portrait |
XLSX
Format specific parameters
When using the XLSX format, the following additional parameters are available:
Parameter name | Description | Default value |
---|---|---|
date_format | The format to use for exporting date and time fields (specified using PHP date & time specifiers). | Same as in the GUI |
formatted_text | Whether or not to export rich text fields with their HTML markup. 1 = export the markup, 0 = no markup. | 0 |
Allowed users
Portal users
are not allowed to access this service, as this profile has no
security included, security being done by the Portal it-self.The users must have the bulk read
privilege on the
specified class of object. In the standard iTop data model, all
users have read
and bulk read
rights on
all classes.
History
-
0.9 First implementation of this feature
-
1.2.1 Added the argument 'query', as an alternative to 'expression'.
-
1.2.1 Added the format 'spreadsheet', which splits date and time columns.
-
1.2.1 Fixed stopper issues with HTTPS and MS-IIS (Trac: #485).
-
1.2.1 The parameter file can be specified as an argument (the documentation was wrong: it was not actually implemented).
-
Branch 1.2.1 Spreadsheet format: fixed issue with times (Trac: #541). The workaround is to use the XML format (XCel does support this)
-
2.0: added the flag no_localize
-
2.1.0: added the XLSX format
-
2.2.0: redesign of the export by creating the new
export-v2.php
page