Generate report using API response

Scenario:

List [input .csv file] with user id and Salary API calls. Based on API response, calculate the total salary and append it as a new column to csv against each user id to generate a report.

Input:

User ID	Name	Salary API	Total Salary
1	Jerry	http://SalaryAPI/User/1
2	Terry	http://SalaryAPI/User/2

Solution:

1. Postman create new collection 
2. Add requests API call and push to ELK
3. In API call -> Tests add below to read the API response and set variables

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 // convert xml to json const xml2js = require('xml2js'); var jsonResponse = customXML2JSON(responseBody); function customXML2JSON(string) { var JSON = {}, options = { explicitArray: false, async: false, trim: false, mergeAttrs: false, valueProcessors: [parseNumbers, parseBooleans], attrValueProcessors: [parseNumbers, parseBooleans] }; xml2js.parseString(string, options, function(err, result) { console.log(err); console.log(result); JSON = result; }); return JSON; } //You can log it to Console console.log(jsonResponse); console.log(JSON.stringify(jsonResponse)); jsonResponse.MyResponse.Users.forEach(obj => { if (obj.Type == "Employee") { var TotalSalary = 0.0; if (Array.isArray(obj.Details.Detail)) { obj.Details.Detail.forEach(item => { TotalSalary = TotalSalary + item.Salary; }); } else { TotalSalary = item.Salary; } //read header of xlsx UserId and append the TotalSalary to it console.log (pm.iterationData.get("UserId") + "," + TotalSalary); //set properties for next step pm.variables.set("UserId",pm.iterationData.get("UserId")); pm.variables.set("Salary",TotalSalary); } })

4. In Save to Elk request -> 
1. url -> http://elasticurl:9200/users-log/documents/body -> 
2. Push to ELK. Include columns in the xslx as field here. Property is refereed in {{}} 

1 2 3 4 5 6 7

{ "UserID": "{{UserId}}", "url": "{{url}}", "TotalSalary": "{{totalSalary}}", "datetime" : "{{datetime}}", "response" : {{jsonResponse}} }

5. Go to Kibana
1. Index Patterns -> Create Index pattern -> [Index name] -> Time Filter field name [datetime] -> Create Index Pattern
2. Discover -> change index = users-log -> Select fields you want -> save -> MyReport1
3. Share -> CSV Reports -> Generate CSV. This would queue the report.
4. Manage Spaces -> Reporting -> MyReport1 -> Actions [download]

Output:

User ID	Name	Salary API	Total Salary
1	Jerry	http://SalaryAPI/User/1	4000
2	Terry	http://SalaryAPI/User/2	5000