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?

Avatar

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

Avatar

Correct answer by
Administrator

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

7 Replies

Avatar

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

Avatar

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.

 

Avatar

Employee Advisor

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/

Avatar

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.

Avatar

Correct answer by
Administrator

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

Avatar

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.