Export data - export-v2.php

FIXME: 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
One major difference with the previous version of the export is that the order of the fields in the output follows the order in which the attributes are specified in the fields parameter.

Output

HTML

The HTML format provides the output as an HTML table, as shown below:

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

The 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)

PDF

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
latest/advancedtopics/exportdata.txt · Last modified: 2024/09/10 10:25 by 127.0.0.1
Back to top

Table of Contents

Contact us
Contact us