Automated CSV export with i-doit

Do you often work with CSV files? We have already shown you how to automatically import this CSV data into i-doit. Today we will go the other way around and show you how to automatically export data as a CSV file from i-doit.

Depending on the tools to which you want to transfer data, these can be compiled in advance via the Report Manager and then automatically generated as a CSV file via the CLI.

In our example, we want to generate a list of all our servers with IP address, host name, location and operating system as a CSV file on our server. This list should be updated automatically every 3 minutes.

Preparation

Compile data in report

In the first step, the data that is to be available later in the CSV file must be compiled in a report. This can be easily created via the graphical user interface of i-doit with the Report Manager.

We recommend creating a separate report category for reports that are used for automations and restricting access to these reports via the authorisation system. If a user makes changes to this report out of ignorance, this will have a direct effect on the automation behind it. In this category we now create a new report. This must contain all the information that we want to find later in the CSV file. Depending on which value is needed at which position, the category images can be moved further up or down.

Based on our example above, the report now looks like this:

When this report is now called up, it should be displayed in the desired form.

Create user

Now we create a new user who will later be used for exporting the CSV. We give this user the name "System User CSV Export" or su-csv-export for short.

We assign a username and a secure password in the category. If you are wondering why the user is needed: This user is later only to be responsible for generating the CSV. He is later only authorised to create files from reports. This ensures granular separation and a higher level of security.

Assign authorisations

In the next step, we must now grant the newly created user access to the "Report export" function. To do this, we switch to the administration and call up the rights system. Under the item "Administration" we assign the right "ReportExportCommand" for the newly created user.

Generate CSV file

Now we go to the CLI to configure the automatic CSV export. For this we will create a new configuration file in the first step. Please check in advance what the path to your i-doit installation directory actually is. Depending on the distribution and your own requirements, this may differ (e.g. /var/www/html/i-doit/ instead of /var/www/html/).

sudo nano /var/www/html/src/handler/config/csv-export.ini

In this file we will now define the configuration properties of the CSV export. To do this, we store the access data of the previously created user "su-export-csv". The tenantId is always 1, unless you are using several clients. The report ID can be found in the overview of the Report Manager. The exportPath and exportFilename specify where the CSV file should be saved and what its name should be. The exportFileType specifies the file type.

[commandArguments]
[commandOptions]
user=su-csv-export
password=123456
tenantId=1
reportId=74
exportPath=/var/www/html/
exportFilename=Report-Server
exportFileType=csv

With Ctrl + O the file can now be saved. Subsequently, the editing process is closed with Ctrl + X. 

We can now test this configuration simply. To do this, we have to execute our configuration file via the console. This can be done with the following command.

sudo -u www-data php /var/www/html/console.php report-export -c /var/www/html/src/handler/config/csv-export.ini

If the command cannot be executed, it is often because PHP is not in the default directory. Then you must first check where PHP is located on your instance. You will often find what you are looking for under /usr/bin/php. However, this depends on the installation and distribution used.

The easiest way to determine PHP is with the command "whereis php". Then add the path to PHP.

admin@linux: whereis php
php: /usr/bin/php7.4 /usr/bin/php /usr/lib/php /etc/php /usr/include/php /usr/share/php7.4-ldap /usr/share/php7.4-gd /usr/share/php7.4-xml /usr/share/php7.4-mysql /usr/share/php7.4-opcache /usr/share/php7.4-common /usr/share/php7.4-mbstring /usr/share/php7.4-pgsql /usr/share/php7.4-readline /usr/share/php7.4-bcmath /usr/share/php /usr/share/php7.4-zip /usr/share/php7.4-curl /usr/share/php7.4-soap /usr/share/php7.4-json /usr/share/man/man1/php.1.gz

Then add the path to PHP:

sudo -u www-data /usr/bin/php /var/www/html/console.php report-export -c /var/www/html/src/handler/config/csv-export.ini

The report is now generated and saved in the main directory. You can call it up via https://your-server.domain/Report-Server.csv

Create Cronjob

Now, of course, this report should not only be created once, but on a regular basis. To do this, we simply have to create a cronjob.

To do this, we change to the directory /etc/cron.d/ and create a new file csv-export

cd /etc/cron.d/
sudo nano csv-export

Here we now create a new cronjob:

*/3 * * * * www-data /usr/bin/php /var/www/html/console.php report-export -c /var/www/html/src/handler/config/csv-export.ini >/dev/null 2>&1

This cronjob would now be executed every 3 minutes and thus overwrite the existing CSV file. Depending on the application, this time can of course be corrected in any direction. Often it is sufficient to have daily updated data available. In this case, a cronjob that is executed in the morning at 5:00 a.m. would be sufficient.

* 5 * * * www-data /usr/bin/php /var/www/html/console.php report-export -c /var/www/html/src/handler/config/csv-export.ini >/dev/null 2>&1

Further possibilities

By creating reports, any data can be compiled in the CMDB and exported as a CSV file on the server. Using various command line programmes such as cURL or rclone, you can also transfer these CSV files to other servers or cloud storages such as OneDrive, Amazon S3 or Google Cloud without any problems. Or perhaps you would like to pass the whole thing to an API for further processing. i-doit sets no limits here either.

Do you have any further questions? The experts of our support team will be happy to help you. Please also take a look at our knowledge base.