Basic reporting with MS Excel

There are several ways to do some reporting on the data stored within iTop.

One of them is to integrate dynamically data within an Excel sheet: once the report is setup, the data will be refreshed on demand (or automatically if configured as such).

1. In iTop, go to the menu Admin Tools / Query phrasebook
2. Create a new query
3. In the query creation form, enter the expression “SELECT Server”
4. Save the query
5. At the end of the properties of the query, iTop shows the “URL to use for MS-Excel web queries”
6. Copy this URL into your paper board: Control + C
7. Open an Excel document
8. Select the worksheet in which you will get the data.
9. Select the tab 'Data'
10. In the tool bar, push the button From the web
11. In the address field, paste the URL from the paper board, or type :https://my_itop/webservices/export.php?format=spreadsheet&login_mode=basic&query=1
12. Press the button Ok
13. Enter your iTop login and password.
14. An overview of the results is displayed, follow the instructions and select the table corresponding to the data.
15. Press the button Import

From now on, the data will be synchronized (see how to finetune this within the Help of Excel).

You can compute reports, graphs, etc.

You will have to learn the Object Query Language, which is very close to the MySQL language.

Example of a more sophisticated query:

SELECT Incident AS i WHERE i.close_date > DATE_ADD(i.start_date, INTERVAL 8 HOUR) AND MONTH(i.start_date) = MONTH(NOW()) - 1

… will list the incident tickets of the previous month, that took more than 8 hours to get closed.

• 1.2.1 Added the query phrase book. For older versions, you had to specify the OQL query as an argument to the page export.php (expression=…). As Excel does limit the length of the URL, some OQL queries did never work.
• 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 A parameter file can be specified as an argument to export.php. The admin can use the parameter file to give credentials.
• Branch 1.2.1 Spreadsheet format: fixed issue with times (Trac: #541). The workaround is to use the XML format (XCel does support this)

• Object Query Language Reference
• Export data in various formats