---

##ElasticTab - Elasticsearch to Excel Report

• Simple Web UI
• Download Excel Reports
• Schedule/Edit/Delete Reports
• E-Mail Reports

Go to https://github.com/raghavendar-ts/ElasticTab-Elasticsearch-to-Excel-Report ___ # Elasticsearch Report Plugin ##Elasticsearch Report Plugin to Generate Excel Report

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 Elasticsearch plugin can be used to generate reports(Excel) with simple JSON input. This plugin can save the report to the server and also can send E-Mail to the configured recipients. We can also perform some basic operations on the fields to get a computed field in the Excel report.

---

####Installing and Removing the Plugin : Go to ES_HOME/bin

Command to Install :

 plugin --install esreport --url https://github.com/raghavendar-ts/Elasticsearch-Report-Plugin/releases/download/v1/es-report-plugin.zip

Command to Remove :

plugin --remove esreport

Configure E-Mail Properties :

You need to follow the below procedure if you want the generated reports to be sent as E-Mail.

1. Once you have installed the plugin, go to ES_HOME/plugins/esreport/properties.
2. Open the file named mail.properties.
3. Give a valid username and password with the mail server properties (The existing properties can be used for G-Mail account).
4. Note :
   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. Once this option is turned on, the Elasticsearch Report Plugin can access the G-Mail account using the credentials given in mail.properties to send the generated report as mail attachment.
   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.
   3. You can also make the plugin to store the reports in the server where elasticsearch is running or to any folder shared using the network.

---

####List of Operation :

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

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

---

####Detailed Example with Sample Data :

Sample Input Data :

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

Sample HTTP Request to the Plugin :

POST /_river/report/now
{
	"reportTitle": "Student Details Report",
	"reportName": "Student Details",
	"reportAccess": {
		"fileName": "Student Details Report",
		"ftp": {
			"filePath": "F:\\properties\\"
		},
		"email": {
			"subject": "Student Report - Detailed Report",
			"description": "The attachment contains detailed report of students and their marks.",
			"deliverTo": ["E-Mail ID 1",
			"E-Mail ID 2"]
		}
	},
	"batchSize": 250,
	"index": "student",
	"type": "details",
	"valueMapping": {
		"genderMapping": {
			"Male": "M",
			"Female": "F"
		},
		"isPassMapping": {
			"x>=40": "PASS",
			"x<40": "FAIL"
		},
		"courseList": {
			"5": "Bachelor of Technology (B.Tech)",
			"6": "Master of Technology (M.Tech)",
			"default": "NA"
		},
		"branchList": {
			"7": "Computer Science",
			"8": "Information Technology"
		}
	},
	"statement": {
		"query": {
			"match_all": {
				
			}
		},
		"fields": ["name",
		"gender",
		"register_number",
		"marks.computer_science",
		"marks.data_mining",
		"marks.dbms",
		"marksArrayInt",
		"marksArrayString"]
	},
	"config": [{
		"title": "Name",
		"format": "[0,name]"
	},
	{
		"title": "Name (String Length)",
		"format": "[2,[0,name]]"
	},
	{
		"title": "Gender",
		"format": "[0,gender]"
	},
	{
		"title": "Register Number",
		"format": "[0,register_number]"
	},
	{
		"title": "Computer Science",
		"format": "[0,marks.computer_science]"
	},
	{
		"title": "English",
		"format": "[0,marks.data_mining]"
	},
	{
		"title": "Economics",
		"format": "[0,marks.dbms]"
	},
	{
		"title": "Course Code",
		"format": "[5,[0,register_number],0]"
	},
	{
		"title": "Course",
		"format": "[1,courseList,[5,[0,register_number],0]]"
	},
	{
		"title": "Branch Code",
		"format": "[5,[0,register_number],1]"
	},
	{
		"title": "Branch",
		"format": "[1,branchList,[5,[0,register_number],1]]"
	},
	{
		"title": "Roll No",
		"format": "[4,[0,register_number],2,3]"
	},
	{
		"title": "Total Marks",
		"format": "[6,[0,marks.computer_science]+[0,marks.data_mining]+[0,marks.dbms]]"
	},
	{
		"title": "Average",
		"format": "[6,([0,marks.computer_science]+[0,marks.data_mining]+[0,marks.dbms])/3]"
	},
	{
		"title": "PASS/FAIL",
		"format": "[7,isPassMapping,[6,[0,marks.computer_science]+[0,marks.data_mining]+[0,marks.dbms]/3]]"
	}]
}

####Operation and Example :

#	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

---

####Sample Output :

Download Excel

#####Sample Student Report (Excel)

####Future Enhancements :

• In case of improper inputs, output proper errors