ElasticTab – Elasticsearch to Excel Report

ElasticTab - Elasticsearch to Excel Report

ElasticTab – Elasticsearch to Excel Report

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)
  2. Test Environment
    1. ElasticTab_v1.01 – Elasticsearch v1.4.4
    2. ElasticTab_v1.3.1 – Elasticsearch v2.2.1
  3. Extract ElasticTab.zip to required location. For example: F:/ElasticTab. E.g. ELASTICTAB_HOME=F:/ElasticTab
  4. Folder Structure in ELASTICTAB_HOMECapture

 

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

ElasticTab - Report Page

ElasticTab – Report Page

Report Page Description: 

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

    Load Job

    Load Job

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

    Load Default Query

    Load Default Query

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

    Add Fields to Query

    Add Fields to Query

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

    Copy 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

    Load Default Excel Report Columns

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

    Add Excel Report Column

    Add Excel Report Column

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

    Download Report

    Download Report

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

    Schedule Job

    Schedule Job

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

    Update Job

    Update Job

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

    E-Mail Report

     

7.1.2 Job Page Sample Screenshot

ElasticTab - Job Page

ElasticTab – Job Page

Job Page Description: 

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

    Run Job

    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

    Edit Job

  3. Delete Job – Deletes the selected job

    Delete 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
  1. Name
  2. Gender
  3. Register Number
  4. Mark in Computer Science
  5. Mark in Data Mining
  6. Mark in DBMS
  1. [0,name]
  2. [0,gender]
  3. [0,register_number]
  4. [0,marks.computer_science]
  5. [0,marks.data_mining]
  6. [0,marks.dbms]
  1. Ramu
  2. Male
  3. 5723
  4. 78
  5. 80
  6. 75
2 getDerivedValue
  1. Gender (Short Form)
  2. Course
  3. Branch
  1. [1,genderMapping,Male]
  2. [1,courseList,[5,register_number,0]]
  3. [1,branchList,[5,register_number,1]]
  1. M
  2. Bachelor of Technology (B.Tech)
  3. 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
  1. Course Code
  2. Branch Code
  1. [5,register_number,0]
  2. [5,register_number,1]
  1. 5
  2. 7
7 Calculate
  1. Total
  2. Average
  1. [6,[0,marks.computer_science]+[0,marks.data_mining]+[0,marks.dbms]]
  2. [6,[0,marks.computer_science]+[0,marks.data_mining]+[0,marks.dbms]/3]
  1. 233
  2. 77.66
8 Range Pass/Fail
  1. [7,isPassMapping,85]
  2. [7,isPassMapping,35]
  1. Pass
  2. Fail
9 Array indexOf(int value)
  1. Index of Value 80
  2. Index of value 78
  1. [8,marksArrayInt, 80]
  2. [8,marksArrayInt, 78]
  1. 1
  2. 0
10 Array indexOf(String value)
  1. Index of value 80
  2. Index of value 75
  1. [9,marksArrayString,80]
  2. [9,marksArrayString,75]
  1. 1
  2. 2
11 Array valueAt(index)
  1. Value at index 2
  2. Value at index 0
  1. [10,marksArrayInt,2]
  2. [10,marksArrayInt,0]
  1. 75
  2. 78

9.3 Sample Output :

Student Details Report

Student Details Report

10. Libraries and Frameworks Used

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

 

Related Links:

Web Application for Elasticsearch :
  1. ElasticTab – Elasticsearch to Excel Report (Web Application)
Elasticsearch Plugin:
  1. Elasticsearch Plugin To Generate (Save and E-Mail) Excel Reports
Elasticsearch:
  1. Execute Multiple Search Query in Elasticsearch
  2. Monitor Elasticsearch Servers with Shell Script - E-Mail Notification
  3. Execute Raw Elasticsearch Query using Transport Client – Java API
  4. Elasticsearch – Apply Nested Filter on Nested (Inner) Aggregation
  5. Execute Multiple Search Query in Elasticsearch
  6. Enable CORS to Send Cross Domain Request to Elasticsearch using AJAX
  7. Elasticsearch Java API – Get Index List
  8. Elasticsearch Java API – Get Alias List
  9. Elasticsearch Java API - Get Type List from given Index
  10. Elasticsearch Java API – Get Field List for a given Index and Type
  11. Elasticsearch Java API – Get Index Type List Mapping
  12. Elasticsearch – Use Script Filter/Conditon in Aggregation/Sub-Aggreagtion
  13. Elasticsearch – Compare/ScriptFilter/Condition on Two Fields using Script Filter – REST Query + Java API
  14. Elasticsearch - Date/Time(String)  Add/Subtract Duration - Days,Months,Years,Hours,Minutes,Seconds
Logstash:
  1. Logstash – Process Log File Once and Exit/Stop Logstash After Reading Log File Once
  2. Measure Logstash Performance using Metrics Filter – Issue/Error in Syntax (Unknown setting ‘message’ for stdout)
  3. Logstash – Process Same Log File (File Input) from Beginning/Start
  4. Create Custom Filter/Plugin to Emit New Events Manually in Logstash
Logstash and Elasticsearch:
  1. Query Elasticsearch Cluster in Filter Section when using Logstash
  2. Custom Elasticsearch Index Name/Type Name based on Events in Logstash
MongoDB and Elasticsearch:
  1. Import Data from Mongo DB to Elasticsearch using Elasticsearch River
 Read More...

[ YOU MAY ALSO LIKE ]

15 thoughts on “ElasticTab – Elasticsearch to Excel Report

    1. RahulGojame

      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

    2. admin@280392

      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.

  1. Saurabh Guru (@SaurabhGuru)

    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??

    Reply
    1. admin@280392

      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.

  2. RahulGojame

    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.

    Reply
    1. admin@280392

      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?

  3. Melih

    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.

    Reply
  4. Bob

    Hello, the link for version 1.2 is unavailable at the moment. Could you please update it? Thanks for making this available.

    Reply
  5. Ravi Shanker Reddy

    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.

    Reply
    1. admin@280392

      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.

Leave a Reply