GitHub Page : ElasticTab – Elasticsearch to Excel Report ( https://github.com/raghavendar-ts/ElasticTab-Elasticsearch-to-Excel-Report )

1. Introduction

There are a lot of companies(small scale to large scale companies) who use Elasticsearch to store massive amount of to data.Most of them find it hard to generate simple reports from Elasticsearch to get information out of it. So this Java application can be used to generate Excel reports with simple web UI. This app can also E-Mail and schedule reports to the configured recipients. We can also perform some basic operations on the fields to get computed fields in the Excel report.

2. Features

• Intuitive User Interface (UI)
• Download or E-Mail reports
• Schedule jobs with our simple application to get the reports on timely basis as E-Mail
• Edit , Enable/Disable or Delete the scheduled reports configuration as and when required
• Write custom expression configuration to get complex computed columns in Excel report
• ElasticTab library can be integrated with any other Java application

3. UI Options Highlights

• Get list of indices, types and aliases
• Get list of fields in any given index and type
• Auto generate default Elasticsearch query with fields and excel column configuration with simple click of a button.

4. Installing the Java Application

1. Download one the below ZIP
   1. ElasticTab_v1.01.zip (https://github.com/raghavendar-ts/ElasticTab-Elasticsearch-to-Excel-Report/releases/download/v1.01/ElasticTab.zip)
   2. ElasticTab v1.3.1.zip (https://github.com/raghavendar-ts/ElasticTab-Elasticsearch-to-Excel-Report/releases/download/v1.3.1/ElasticTab_v1.3.1.zip)
   3. ElasticTab v5.4.0.zip (https://github.com/raghavendar-ts/ElasticTab-Elasticsearch-to-Excel-Report/releases/download/v5.4.0/ElasticTab.v5.4.0.zip)
2. Test Environment
   1. ElasticTab_v1.01 – Elasticsearch v1.4.4
   2. ElasticTab_v1.3.1 – Elasticsearch v2.2.1
   3. ElasticTab_v5.4.0 – Elasticsearch v5.4.0
3. Extract ElasticTab.zip to required location. For example: F:/ElasticTab. E.g. ELASTICTAB_HOME=F:/ElasticTab
4. Folder Structure in ELASTICTAB_HOME

 

5. Configuring the Property Files

1. elasticsearch.properties
   • Specify the clustername and hostname of the Elasticsearch server from which you want to download or schedule reports
2. elastictab.properties
   • Update save.location property to required folder. This property is used to save the reports generated to the path specified in the property save.location.
3. h2.properties
   • Update ELASTICTAB_HOME path in db_connection property.
4. jetty.properties (Not mandatory to change)
   • If required, change the port to which the webserver should listen.
5. mail.properties
   • Update the E-Mail ID, password and other properties as required to configure the E-Mail client. The reports which are sent from the application will be sent using the configured E-Mail account. If you are using G-Mail account, make sure to follow the below steps.
     1. Google (G-Mail) by default will not allow any third party applications to access the G-Mail account programmatically. But Google provides an option to turn it on. To enable the option, go to Less secure apps (https://www.google.com/settings/security/lesssecureapps) and Turn On the option for Access for less secure apps.
     2. Make sure to disable any anti-virus program running in your machine since it may block the outgoing mail requests sent by third party applications. In my case Avast was blocking the outgoing mail requests.
6. quartzClient.properties
   • Updated the property org.quartz.scheduler.rmi.registryHost to the hostname where the ElasticTab application is going to run.
7. quartzServer.properties
   • Updated the property org.quartz.scheduler.rmi.registryHost to the hostname where the ElasticTab application is going to run.
   • Update ELASTICTAB_HOME  in org.quartz.dataSource.quartzDataSource.URL property to match the path.

6. Starting ElasticTab Java Application

1. Go to ELASTICTAB_HOME directory in command prompt
2. Run the command java -jar ElasticTab.jar
3. The following is the sample messages for successful start of the application

E:\ElasticTab>java -jar ElasticTab.jar
Property files laoded from the path E:\ElasticTab
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further detail
s.
Dec 29, 2015 1:45:26 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
Dec 29, 2015 1:45:26 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.1.1 [built 15-March-2007 01:32:31; debug? true; trac
e: 10]
Elasticsearch Transport Client Initialized:org.elasticsearch.client.transport.Tr
ansportClient@17dc87b7
Dec 29, 2015 1:45:27 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource ge
tPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acqu
ireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCo
mmitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> fa
lse, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTest
erClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName
-> 2tgcih9d1tpe246baekhd|769e1df4, debugUnreturnedConnectionStackTraces -> false
, description -> null, driverClass -> org.h2.Driver, factoryClassLocation -> nul
l, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2tgcih9d1tpe246b
aekhd|769e1df4, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl ->
jdbc:h2:file:ELASTICTAB_HOME/h2data/elastictab, lastAcquisitionFailureDefaultUse
r -> null, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime ->
 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 2, maxStatements -> 0, max
StatementsPerConnection -> 120, minPoolSize -> 1, numHelperThreads -> 3, numThre
adsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {
user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> fa
lse, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTr
aditionalReflectiveProxies -> false ]

7. Using Web Application

1. Open web browser (Google Chrome is recommended browser)
2. ElasticTab is two page web application
   1. Report Page – http://localhost:8083/index.html#/ (http://localhost:8083/index.html#/report)
   2. Job Page – http://localhost:8083/index.html#/job
3. Report Page Usage
   • Download Report
   • Schedule Report
   • Update Report
   • E-Mail Report
4. Job Page Usage
   • View Scheduled Jobs
   • Edit Scheduled Jobs
   • Delete Scheduled Jobs
   • Enable/Disable Scheduled Job

ElasticTab uses Quartz Scheduler to run scheduled reports which has a little different CRON expression compared to the linux CRON.

Quartz Scheduler CRON Expression:

http://www.quartz-scheduler.org/documentation/quartz-2.x/tutorials/tutorial-lesson-06.html

7.1 Web Application UI

7.1.1 Report Page Sample Screenshot

Report Page Description: 

1. Load Job – This option is used to load the configuration parameters for an existing scheduled job.
   

   Load Job

2. Load Default Query – This option is used to load the default Elasticsearch query with fields
   

   Load Default Query

3. Add Fields to Query – This option is used to  add Elasticsearch fields to existing query
   

   Add Fields to Query

4. Copy to Clipboard – This option is used to you copy the field name to clipboard
   

   Copy to Clipboard

5. Load Default Excel Report Columns – This option is used to load the default Excel report column configuration
   

   Load Default Excel Report Columns

6. Add Excel Report Column – This option is used to add new Excel report column
   

   Add Excel Report Column

7. Download Report – This option is used to download the report with currently selected options
   

   Download Report

8. Schedule Job – This option is used to schedule a job with currently selected options
   

   Schedule Job

9. Update Job – This option is used to update a existing schedule job with currently selected options
   

   Update Job

10. E-Mail Report – This option is used to get the report as E-Mail with currently selected options
    

    E-Mail Report

     

7.1.2 Job Page Sample Screenshot

Job Page Description: 

1. Run Job – Trigger the selected job to execute and get the report as E-Mail
   

   Run Job

2. Edit Job – Loads the selected job to report page where you can edit and update the report or pull instant report
   

   Edit Job

3. Delete Job – Deletes the selected job
   

   Delete Job

8. List of Operations: (Used for building custom columns in Excel from Elasticsearch data)

1. getValue – Get the Elasticsearch field value
2. getDValue – Get custom value based on the Elasticsearch field value
3. Length – Get the length of Elasticsearch field value
4. Format Number Length – Format integer to given length
5. Sub String – Get substring of Elasticsearch field value
6. Character at index – Get the character at given index of Elasticsearch field value
7. Calculate – Perform arithmetic operation on Elasticsearch field values
8. Range – Get custom value based on range condition of Elasticsearch field value
9. Array indexOf(int value) – Get index of given integer value from an Elasticsearch array
10. Array indexOf(String value) – Get index of given string value from an Elasticsearch array
11. Array valueAt(index) – Get the value of given index from an Elasticsearch array

8.1 Operation Syntax and Object Type

#	Operation	Syntax	Object Type
1	getValue	[0,ES_FIELD]	[0,String]
2	getDValue	[1,valueMappingKey,VALUE]	[1,String,String]
3	Length	[2,VALUE]	[2,String]
4	Format Number Length	[3,VALUE,FORMAT_LENGTH]	[3,String,int]
5	Sub String	[4,VALUE,from,to]	[4,String,int,int]
6	Character at index	[5,VALUE,index]	[5,String,int]
7	Calculate	[6, ARITHMETIC_EXPRESSION]	[6,String]
8	Range	[7,valueMappingKey,VALUE]	[7,String,String]
9	Array indexOf(int value)	[8,ES_ARRAY_FIELD, value]	[8,String,int]
10	Array indexOf(String value)	[9,ES_ARRAY_FIELD,value]	[9,String,String]
11	Array valueAt(index)	[10,VALUE,index]	[10,String,int]

Syntax Description

Operation	Description
ES_FIELD	A field in a Elasticsearch document
ES_ARRAY_FIELD	A field in a Elasticsearch document
valueMappingKey	A field in input JSON given by user
ARITHMETIC_EXPRESSION	Any string representing conditional or arithmetic expression
VALUE	Can be either string given by user or any other OPERATION. i.e. we can nest the above operations. In other words, we can perform multi-level nested OPERATION on the Elasticsearch fields

 

9. Detailed Example with Sample Data :

9.1 Sample Input Data in Elasticsearch:

{
   "name":"Ramu",
   "gender":"Male",
   "register_number": "5723",
   "marks":{
      "computer_science":78,
      "data_mining":80,
      "dbms":75
      },
   "marksArrayInt":[78,80,75],
   "marksArrayString":["78","80","75"]
}

9.2 Operation and Example with Expression:

#	Operation	Column	Example	Output
1	getValue	Name Gender Register Number Mark in Computer Science Mark in Data Mining Mark in DBMS	[0,name] [0,gender] [0,register_number] [0,marks.computer_science] [0,marks.data_mining] [0,marks.dbms]	Ramu Male 5723 78 80 75
2	getDerivedValue	Gender (Short Form) Course Branch	[1,genderMapping,Male] [1,courseList,[5,register_number,0]] [1,branchList,[5,register_number,1]]	M Bachelor of Technology (B.Tech) Computer Science
3	Length	Name	[2,Ramu]	4
4	Format Number Length	–	[3,632,5]	00632
5	Sub String	Roll No	[4,[0,register_number],2,3]	23
6	Character at index	Course Code Branch Code	[5,register_number,0] [5,register_number,1]	5 7
7	Calculate	Total Average	[6,[0,marks.computer_science]+[0,marks.data_mining]+[0,marks.dbms]] [6,[0,marks.computer_science]+[0,marks.data_mining]+[0,marks.dbms]/3]	233 77.66
8	Range	Pass/Fail	[7,isPassMapping,85] [7,isPassMapping,35]	Pass Fail
9	Array indexOf(int value)	Index of Value 80 Index of value 78	[8,marksArrayInt, 80] [8,marksArrayInt, 78]	1 0
10	Array indexOf(String value)	Index of value 80 Index of value 75	[9,marksArrayString,80] [9,marksArrayString,75]	1 2
11	Array valueAt(index)	Value at index 2 Value at index 0	[10,marksArrayInt,2] [10,marksArrayInt,0]	75 78

9.3 Sample Output :

10. Libraries and Frameworks Used

• Jetty Server, Quartz Server, H2 Database, Apache POI
• HTML
• Javascript (Angular JS)
• CSS (Angular Material, Bootstrap)

 

Related Links: