Uploading Data from Relational Databases
- Configure Local Database Connection Settings
- Specify SQL SELECT Queries to be executed on local database
- Configure Import Settings
- Executing the Analytics Plus agent
- Setting up Periodic Upload / Synchronization
- Viewing the data in the application
To upload data from local (in-house) databases, configure the proxy settings (if your network has a proxy server setup) in the common_params. conf file, and then specify your local database connection settings in the configuration file database_connection_params.conf as described below.
Specify Local Database Connection Settings
The following are the parameters that are to be given in database_connection_params.conf file:
The example in the above screenshot illustrates the connection settings for a MySQL database. You can fill similar values for Microsoft SQL Server, Sybase and Oracle.
Default Settings for different Databases
The following table captures the default connection settings that can be specified in the database_connections_params.conf for different database vendor type. If you are not using the default port provided by the database, then you need to specify the corresponding port for connecting.
Setting up JDBC Driver for MySQL
If you plan to use the Analytics Plus agent to fetch data from your local MySQL databases and upload it into ManageEngine Analytics Plus, then you need to setup the MySQL JDBC driver for the agent to work. For all other supported databases the corresponding JDBC drivers are bundled along with the agent, by default.
- Download the MySQL JDBC Driver from the following link: http://mysql.spd.co.il/Downloads/Connector-J/mysql-connector-java-3.0.17-ga.tar.gz
- Extract the downloaded file (using tools like Winzip, tar etc.,).
- Copy the JDBC driver file "mysql-connector-java-3.0.17-ga-bin.jar" into the Analytics Plus agent directory <Agent home>\lib.
Once the JDBC driver is set, you can connect the Analytics Plus agent to any local MySQL database to fetch the required records and upload the same into ManageEngine Analytics Plus.
Using SQL Server Windows Authentication
Windows Authentication can also be used to connect to your local SQL Server database. To do this, you need to provide the database name followed by the Windows domain name separated by semi-colon ( ; ) for the parameter DBNAME in the database_connections_params.conf file.
Specify SQL SELECT Queries to be executed on local database
The database_sql_queries.xml file contains all the settings related to the SQL SELECT Queries that are to be executed in the in-house database for fetching the required data, along with settings about the ManageEngine Analytics Plus database and table names into which the data is to be uploaded.
To fetch the data from your in-house database you need to provide the necessary SQL SELECT queries that are to be executed in the database. These queries will be executed by the Analytics Plus agent in your local database to fetch the necessary records and upload the same into ManageEngine Analytics Plus according to the settings provided.
You can provide any number of SQL SELECT queries to execute. Each query is to be provided in a <Query> <Query> XML element. Multiple queries can be grouped within the <Queries> <Queries> element.
As part of the <Query> node, you also need the provide details about the ManageEngine Analytics Plus database into which the data that is fetched from executing the SQL SELECT query is to be uploaded.
The following table explains all the parameters to be provided as part of the <Query> node.
You can specify any number of SQL SELECT queries using the <Query> XML Element as described above.
Configure Import Settings
The configuration file common_params.conf in /conf directory provides parameters that Analytics Plus agent uses while uploading data. These import settings help ManageEngine Analytics Plus properly understand the data being imported.
The following table lists the import parameters that can be configured in the common_params.conf file for uploading data from your local database.
Note: You will not find all the below given parameters present in the configuration file (except mandatory ones). In case you do not find them, add them into the file and provide the required value as specified below.
Executing the Analytics Plus agent
Once you have configured the parameters, you can execute the Analytics Plus agent by invoking the below file available in the bin folder.
In Windows OS - UploadFromDB.bat
Linux OS - UploadFromDB.sh
You can also execute the Analytics Plus agent using the command line. The following is an example command to invoke the Analytics Plus agent. Specifying the User emaii id, Auth token, and the DB owner mail address (if it exists) details in the common_params.conf file reduces the tedious process of repeating these details every time in your command line arguments.In case you specify these values in the command line apart from specifying in the common_params.conf file, then the values provided in the command line will take precedence.
- UploadFromDB.bat "<hostname> <port_number>"
Run the UploadFromDB.bat / UploadFromDB.sh file under <agent_Home>bin directory along with the required parameters. You have to provide ManageEngine Analytics Plus User Name and Password in the order shown below.
- UploadFromDB.bat "<hostname> <port_number> <analyticsplus_emailaddress> <analyticsplus_password>"
If you have been made as a database owner and need to import data into that particular database, the <manageengineanalytics_dbowner_mailaddress> has to be specified in the following format:
- UploadFromDB.bat "<hostname> <port_number> <analyticsplus_emailaddress> <analyticsplus_password> -D <database_owner_login_e-mail_address>"
You can also specify the authentication details such as ManageEngine Analytics Plus Login e-mail ID, AuthToken and Database owner e-mail ID in the command line. The following is an example command to invoke the Analytics Plus agent along with user e-mail ID and AuthToken.
- UploadFromDB.bat "<hostname> <port_number> <analyticsplus_emailaddress>-A <authtoken>"
The following is an example command to invoke the Analytics Plus agent along with user e-mail ID, AuthToken and Database owner e-mail ID for shared users
- UploadFromDB.bat "<hostname> <port_number> <analyticsplus_emailaddress>-A <authtoken> -D <database_owner_login_e-mail_address>"
- In case there is any failure in executing any of the given SQL Query, then the Analytics Plus agent will abort the execution of that query and the ones that follow. Whatever queries that had been successfully executed and uploaded into ManageEngine Analytics Plus till then will not be rolled back.
The details of the last upload executed will be logged in the file <Agent_Home>/logs/dbuploadserverout.txt
You can also execute this database upload periodically using a Scheduler to keep the data in ManageEngine Analytics Plus synchronized with your database. Read more in the next section.
Setting up Periodic Upload / Synchronization
Using Analytics Plus agent you can periodically schedule uploading data from a CSV file. With this mechanism, you can have the latest data from your application synced into ManageEngine Analytics Plus, and the reports that you create over this data stay current.
You can setup a periodic schedule for both the CSV Console mode utility as well as the Database Upload utility using the Operating System Scheduler feature as explained below.
Setting up Schedule in Windows Operating System
- Click Start -> Settings -> Control Panel ->Scheduled Tasks.
- Click Add Scheduled Task. The "Schedule Task" wizard will open.
- Click Action > Create Task to open the Create Task dialog.
- Open Action tab and then click New.
- Click Browse button and select the Analytics Plus agent command line batch file UploadFromDB.bat in the Program/Script field. Ensure that you have provided the necessary settings in the Analytics Plus agent configuration files.
- In the Add Argument field enter the following command line arguments.
Setting up Schedule in Linux Operating System:
- In Linux, you can use the crontab command for scheduling the migration process using the command line script CSVUploadConsole.sh. (Checkout Simple Help on Linux Crontab command)
- Ensure you have provided the necessary setting in the Analytics Plus agent configuration files.
Points to consider, while scheduling periodic upload using Command line mode:
- It is the users responsibility to ensure that the latest data is uploaded into ManageEngine Analytics Plus by setting the schedule interval accordingly. Also ensure that you have provided the appropriate SQL Select query to pull the data.
- In case you have deleted few records in the tables in your local database being uploaded, the only option to remove these records from ManageEngine Analytics Plus database is to set the Import Typeas TRUNCATEADD. This will delete all the records in the corresponding table in ManageEngine Analytics Plus and then add the data newly fetched records from the local database into ManageEngine Analytics Plus.
- In case you have modified few records in the tables in your local database being uploaded, then to get this modified in ManageEngine Analytics Plus database set the Import Type as UPDATEADD and specify the matching columns. The Analytics Plus agent will compare the records in the corresponding table in ManageEngine Analytics Plus with the data being uploaded from your local database table based on the matching columns. If the record already exists in the ManageEngine Analytics Plus table then it will be replaced with the new values available from the local database. If not, it will be added as new records in ManageEngine Analytics Plus.
Viewing the Uploaded Data
- To view the data that you have uploaded,
- Login to your ManageEngine Analytics Plus account.
- Click on the corresponding Database name under My Databases
- Click on the corresponding table at the Left to open the table and view the uploaded data.