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

[sureshy2015]

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.

[edubey]

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

[kautuk_sahni]

Hi As mentioned by Praveen we need to create custom service:

Link:- https://helpx.adobe.com/experience-manager/using/aem-reporting-service.html

Another reference link:- https://helpx.adobe.com/experience-manager/using/creating-custom-excel-service-experience.html

Thanks and Regards

Kautuk Sahni

[Jitendra_S_Toma]

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.

[Kunal_Gaba_]

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]

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 · Accepted solution]

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

[sureshy2015]

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.