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
- Download one the below ZIP
- ElasticTab_v1.01.zip (https://github.com/raghavendar-ts/ElasticTab-Elasticsearch-to-Excel-Report/releases/download/v1.01/ElasticTab.zip)
- 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)
- 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)
- Test Environment
- ElasticTab_v1.01 – Elasticsearch v1.4.4
- ElasticTab_v1.3.1 – Elasticsearch v2.2.1
- ElasticTab_v5.4.0 – Elasticsearch v5.4.0
- Extract ElasticTab.zip to required location. For example: F:/ElasticTab. E.g. ELASTICTAB_HOME=F:/ElasticTab
- Folder Structure in ELASTICTAB_HOME
5. Configuring the Property Files
- elasticsearch.properties
- Specify the clustername and hostname of the Elasticsearch server from which you want to download or schedule reports
- 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.
- h2.properties
- Update ELASTICTAB_HOME path in db_connection property.
- jetty.properties (Not mandatory to change)
- If required, change the port to which the webserver should listen.
- 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.
- 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.
- 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.
- 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.
- quartzClient.properties
- Updated the property org.quartz.scheduler.rmi.registryHost to the hostname where the ElasticTab application is going to run.
- 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
- Go to ELASTICTAB_HOME directory in command prompt
- Run the command java -jar ElasticTab.jar
- 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
- Open web browser (Google Chrome is recommended browser)
- ElasticTab is two page web application
- Report Page – http://localhost:8083/index.html#/ (http://localhost:8083/index.html#/report)
- Job Page – http://localhost:8083/index.html#/job
- Report Page Usage
- Download Report
- Schedule Report
- Update Report
- E-Mail Report
- 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

ElasticTab – Report Page
Report Page Description:
- Load Job – This option is used to load the configuration parameters for an existing scheduled job.
Load Job
- Load Default Query – This option is used to load the default Elasticsearch query with fields
Load Default Query
- Add Fields to Query – This option is used to add Elasticsearch fields to existing query
Add Fields to Query
- Copy to Clipboard – This option is used to you copy the field name to clipboard
Copy to Clipboard
- Load Default Excel Report Columns – This option is used to load the default Excel report column configuration
Load Default Excel Report Columns
- Add Excel Report Column – This option is used to add new Excel report column
Add Excel Report Column
- Download Report – This option is used to download the report with currently selected options
Download Report
- Schedule Job – This option is used to schedule a job with currently selected options
Schedule Job
- Update Job – This option is used to update a existing schedule job with currently selected options
Update Job
- 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

ElasticTab – Job Page
Job Page Description:
- Run Job – Trigger the selected job to execute and get the report as E-Mail
Run Job
- Edit Job – Loads the selected job to report page where you can edit and update the report or pull instant report
Edit Job
- Delete Job – Deletes the selected job
Delete Job
8. List of Operations: (Used for building custom columns in Excel from Elasticsearch data)
- getValue – Get the Elasticsearch field value
- getDValue – Get custom value based on the Elasticsearch field value
- Length – Get the length of Elasticsearch field value
- Format Number Length – Format integer to given length
- Sub String – Get substring of Elasticsearch field value
- Character at index – Get the character at given index of Elasticsearch field value
- Calculate – Perform arithmetic operation on Elasticsearch field values
- Range – Get custom value based on range condition of Elasticsearch field value
- Array indexOf(int value) – Get index of given integer value from an Elasticsearch array
- Array indexOf(String value) – Get index of given string value from an Elasticsearch array
- 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 |
|
|
|
2 | getDerivedValue |
|
|
|
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 |
|
|
|
7 | Calculate |
|
|
|
8 | Range | Pass/Fail |
|
|
9 | Array indexOf(int value) |
|
|
|
10 | Array indexOf(String value) |
|
|
|
11 | Array valueAt(index) |
|
|
|
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)
Is it support Elasticsearch version 2.0 or above ?
Hi,
I updated code to compatible with Eleasticsearch 2.2.
Its running fine when I run code via Eclipse, but when I build jar with dependency and run jar directly on server its giving following error, can you please let us know why it is…
Scheduler Client Initialized
**************************ElasticTab Started**************************
Mar 17, 2016 5:58:48 PM com.sun.jersey.api.core.PackagesResourceConfig init
INFO: Scanning for root resource and provider classes in the packages:
com.elastictab.report
Mar 17, 2016 5:58:49 PM com.sun.jersey.api.core.ScanningResourceConfig logClasses
INFO: Root resource classes found:
class com.elastictab.report.ESReportREST
Mar 17, 2016 5:58:49 PM com.sun.jersey.api.core.ScanningResourceConfig init
INFO: No provider classes found.
Mar 17, 2016 5:58:49 PM com.sun.jersey.server.impl.application.WebApplicationImpl _initiate
INFO: Initiating Jersey application, version ‘Jersey: 1.19 02/11/2015 03:25 AM’
Mar 17, 2016 5:58:50 PM com.sun.jersey.spi.container.ContainerResponse write
SEVERE: A message body writer for Java class java.util.HashMap, and Java type java.util.Map, and MIME media type application/json was not found.
The registered message body writers compatible with the MIME media type are:
*/* ->
com.sun.jersey.server.impl.template.ViewableMessageBodyWriter
Mar 17, 2016 5:58:50 PM com.sun.jersey.spi.container.ContainerResponse logException
SEVERE: Mapped exception to response: 500 (Internal Server Error)
javax.ws.rs.WebApplicationException: com.sun.jersey.api.MessageException: A message body writer for Java class java.util.HashMap, and Java type java.util.Map, and MIME media type application/json was not found.
at com.sun.jersey.spi.container.ContainerResponse.write(ContainerResponse.java:284)
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1510)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:558)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:733)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:806)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:587)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:550)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1128)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1062)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:113)
at org.eclipse.jetty.server.Server.handle(Server.java:507)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:284)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:237)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:240)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:93)
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:53)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceRun.produceAndRun(ExecuteProduceRun.java:191)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceRun.run(ExecuteProduceRun.java:126)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:641)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:559)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.sun.jersey.api.MessageException: A message body writer for Java class java.util.HashMap, and Java type java.util.Map, and MIME media type application/json was not found.
… 30 more
Hi
The issue which you are facing is due to some missing dependency. I have updated the application with newer version of Elasticsearch (2.2.0). Check out the new version of ElasticTab v1.1. I have tested ElasticTab v1.1 with Elasticsearch v2.2.1 and works fine.
I am getting the following exception after hitting the UI.
SEVERE: The RuntimeException could not be mapped to a response, re-throwing to the HTTP container
org.elasticsearch.client.transport.NoNodeAvailableException: None of the configured nodes are available: []
at
I have verified that the Elasticsearch nodes are reachable by issuing a curl command. What am I missing here??
Hi
The issue which you are facing is due to version compatibility. I have updated ElasticTab with with newer version of Elasticsearch (2.2.0). Try using the latest version which I have updated in GitHub and in this blog.
Awesome… thanks a lot.
is the application working as specified?
Yes, but is there any way to change application, to send mail with default sever mail setting (postmail) as I don’t want to give any individual email id due to security reasons.
With the current implementation, it is not possible. I suggest you to create a new E-Mail ID and use it for this application. Mean while I will also try to make the application use detfault systems’s mail settings. By the way, is ElasticTab solving the problem which you are trying to address?
Hi, i am wondering how schedule function works actually. Becuase there are 5 fields in traditional crontabs but yours have 6 fields (e.g. 0 30 10-3 ? * WED,FRI) Can you explain the idea behind that syntax
Thanks in advance.
ElasticTab uses Quartz Scheduler to run scheduled reports which has a little different CRON expression compared to the linux CRON. Have a look at this page http://www.quartz-scheduler.org/documentation/quartz-2.x/tutorials/tutorial-lesson-06.html for detailed information.
Hello, the link for version 1.2 is unavailable at the moment. Could you please update it? Thanks for making this available.
Thanks for the info. The link is now available.
In my elastic search Data one field is “MessageDeliveryStatus”. I need this count in the hourly basis like in the 0th hour how many SUCCESS, FAILURES are present. How to write query for this.
Your usecase requires aggregation queries which is not yet supported in ElasticTab. The work around is that you can query the required documents, schedule it to get as report and then filter/count using Excel. The other way is that you can schedule 2 reports. One with SUCCESS filter and the other with FAILURE filter.