Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

How to export audit logs of a page to excel sheet?

sureshy2015
Level 4
Level 4

How to export audit logs of a page to excel sheet?

 

Is there any OOTB functionality ?.If not tell me how to achieve this.Thanks in advance.

1 Accepted Solution
kautuk_sahni
Correct answer by
Community Manager
Community Manager

Hi

To fully automate this process, i would advice you to create CSV/Exel file within OSGI sling service.

 

Lets say, you have a JSON like the following :

{"infile": [{"field1": 11,"field2": 12,"field3": 13},{"field1": 21,"field2": 22,"field3": 23},{"field1": 31,"field2": 32,"field3": 33}]}

Lets see the code for converting it to csv :

import org.apache.commons.io.FileUtils;import org.json.CDL;import org.json.JSONArray;import org.json.JSONException;import org.json.JSONObject;public class JSON2CSV {public static void main(String myHelpers[]){String jsonString = "{\"infile\": [{\"field1\": 11,\"field2\": 12,\"field3\": 13},{\"field1\": 21,\"field2\": 22,\"field3\": 23},{\"field1\": 31,\"field2\": 32,\"field3\": 33}]}"JSONObject output = new JSONObject(jsonOut);JSONArray docs = response.getJSONArray("infile");File file=new File("yourpath/fromJSON.csv");String csv = CDL.toString(docs);FileUtils.writeStringToFile(file, csv);}}

Now you got the CSV generated from JSON.

It should look like this:

field1,field2,field311,22,3321,22,2331,32,33

Reference Link:- http://stackoverflow.com/questions/7172158/converting-json-to-xls-csv-in-java

 

Option 2:- Use Jquery Plugin to convert Json to Excel.

Link:- http://w3lessons.info/2015/07/13/export-html-table-to-excel-csv-json-pdf-png-using-jquery/

 

I hope this would help you.

Thanks and Regards

Kautuk Sahni

View solution in original post

0 Replies
edubey
Level 10
Level 10

I don't think so any OOTB functionality is available.

You have to create a custom reporting services. take a look @https://helpx.adobe.com/experience-manager/using/aem-reporting-service.html

Jitendra_S_Toma
Level 9
Level 9

Hi Suresh,

Unfortunately, there is no such functionality. However, you can write an API to get all audit reports from "/var/audit". 

For Page audit reports : /var/audit/com.day.cq.wcm.core.page

For Dam audit report : /var/audit/com.day.cq.dam

sureshy2015 wrote...

How to export audit logs of a page to excel sheet?

 

Is there any OOTB functionality ?.If not tell me how to achieve this.Thanks in advance.

 

kunal23
Level 10
Level 10

It is not available as inbuilt feature in the reporting interface but you can manually export the data to a CSV or an excel file. 

  1. Get the audit data of page activity report in the JSON format - http://localhost:4502/etc/reports/auditreport/jcr:content/report.data.json
  2. Use any free online tool to convert JSON to CSV/Excel. Example - http://konklone.io/json/
sureshy2015
Level 4
Level 4

thanks guys for your help.I am actually running a query using querybuilder which is giving me json then used some online excel converter.

kautuk_sahni
Correct answer by
Community Manager
Community Manager

Hi

To fully automate this process, i would advice you to create CSV/Exel file within OSGI sling service.

 

Lets say, you have a JSON like the following :

{"infile": [{"field1": 11,"field2": 12,"field3": 13},{"field1": 21,"field2": 22,"field3": 23},{"field1": 31,"field2": 32,"field3": 33}]}

Lets see the code for converting it to csv :

import org.apache.commons.io.FileUtils;import org.json.CDL;import org.json.JSONArray;import org.json.JSONException;import org.json.JSONObject;public class JSON2CSV {public static void main(String myHelpers[]){String jsonString = "{\"infile\": [{\"field1\": 11,\"field2\": 12,\"field3\": 13},{\"field1\": 21,\"field2\": 22,\"field3\": 23},{\"field1\": 31,\"field2\": 32,\"field3\": 33}]}"JSONObject output = new JSONObject(jsonOut);JSONArray docs = response.getJSONArray("infile");File file=new File("yourpath/fromJSON.csv");String csv = CDL.toString(docs);FileUtils.writeStringToFile(file, csv);}}

Now you got the CSV generated from JSON.

It should look like this:

field1,field2,field311,22,3321,22,2331,32,33

Reference Link:- http://stackoverflow.com/questions/7172158/converting-json-to-xls-csv-in-java

 

Option 2:- Use Jquery Plugin to convert Json to Excel.

Link:- http://w3lessons.info/2015/07/13/export-html-table-to-excel-csv-json-pdf-png-using-jquery/

 

I hope this would help you.

Thanks and Regards

Kautuk Sahni

View solution in original post

sureshy2015
Level 4
Level 4

kautuksahni wrote...

Hi

To fully automate this process, i would advice you to create CSV/Exel file within OSGI sling service.

 

Lets say, you have a JSON like the following :

Lets see the code for converting it to csv :

import org.apache.commons.io.FileUtils;import org.json.CDL;import org.json.JSONArray;import org.json.JSONException;import org.json.JSONObject;public class JSON2CSV {public static void main(String myHelpers[]){String jsonString = "{\"infile\": [{\"field1\": 11,\"field2\": 12,\"field3\": 13},{\"field1\": 21,\"field2\": 22,\"field3\": 23},{\"field1\": 31,\"field2\": 32,\"field3\": 33}]}"JSONObject output = new JSONObject(jsonOut);JSONArray docs = response.getJSONArray("infile");File file=new File("yourpath/fromJSON.csv");String csv = CDL.toString(docs);FileUtils.writeStringToFile(file, csv);}}

Now you got the CSV generated from JSON.

It should look like this:

field1,field2,field311,22,3321,22,2331,32,33

Reference Link:- http://stackoverflow.com/questions/7172158/converting-json-to-xls-csv-in-java

 

Option 2:- Use Jquery Plugin to convert Json to Excel.

Link:- http://w3lessons.info/2015/07/13/export-html-table-to-excel-csv-json-pdf-png-using-jquery/

 

I hope this would help you.

Thanks and Regards

Kautuk Sahni

 

 

 

 

 

brilliant kautuksahni this is what needed.thank you so much.