Convert step output to csv format

[shashankkumar]

Hi Team,

After running aql query using qradar connector i am getting output as below, and now i want to convert this output to csv format and attach that in send email step. What can be done ? 

{
"data": {
"events": [
{
"Action": null,
"SourceIP": "10.44.1.249",
"Username": "root",
"EventName": "User Logout",
"SourcePort": 0,
"DestinationIP": "127.0.0.1",
"DestinationPort": 0,
"ActivityStartTime": "2024-06-17 09:09:59"
},
{
"Action": null,
"SourceIP": "10.44.1.249",
"Username": "root",
"EventName": "User Login",
"SourcePort": 0,
"DestinationIP": "127.0.0.1",
"DestinationPort": 0,
"ActivityStartTime": "2024-06-17 09:09:59"
},
{
"Action": null,
"SourceIP": "10.44.1.249",
"Username": "root",
"EventName": "User Logout",
"SourcePort": 0,
"DestinationIP": "127.0.0.1",
"DestinationPort": 0,
"ActivityStartTime": "2024-06-17 09:09:59"
},
{
"Action": null,
"SourceIP": "10.254.189.11",
"Username": "root",
"EventName": "Ticket issued",
"SourcePort": 0,
"DestinationIP": "10.254.189.11",
"DestinationPort": 0,
"ActivityStartTime": "2024-06-17 09:09:59"
},

"status": "Success",
"message": "",
"operation": null,
"execution_time": "22 seconds 767 ms"
}

[Jtamboli]

Hi Shashank,
To convert the JSON output data into CSV format you can use a CSV Data Management connector in that "Convert JSON to CSV file" action.
Note: This file will be created in attachment module once you run the connector action
Below is screenshots attached

Thanks :)

[shashankkumar]

I am getting below error:

Error in processing CSV File: 404 Client Error: Not Found for url: https://localhost/api/3//localhostNone :: {'type': 'NotFoundHttpException', 'message': 'No route found for \"GET https://localhost/api/3//localhostNone\"'} :: Url: https://localhost/api/3//localhostNone  Connector :: csv-data-managementV1.2.0

[ranjeet]

Hi @shashankkumar ,

The JSON you provided contains a syntax error,

{
"data": {
"events": [

The braces ( '{', '[' ) are not properly closed.

Please use the corrected JSON format provided below.

{
"data": {
"events": [
{
"Action": null,
"SourceIP": "10.44.1.249",
"Username": "root",
"EventName": "User Logout",
"SourcePort": 0,
"DestinationIP": "127.0.0.1",
"DestinationPort": 0,
"ActivityStartTime": "2024-06-17 09:09:59"
},
{
"Action": null,
"SourceIP": "10.44.1.249",
"Username": "root",
"EventName": "User Login",
"SourcePort": 0,
"DestinationIP": "127.0.0.1",
"DestinationPort": 0,
"ActivityStartTime": "2024-06-17 09:09:59"
},
{
"Action": null,
"SourceIP": "10.44.1.249",
"Username": "root",
"EventName": "User Logout",
"SourcePort": 0,
"DestinationIP": "127.0.0.1",
"DestinationPort": 0,
"ActivityStartTime": "2024-06-17 09:09:59"
},
{
"Action": null,
"SourceIP": "10.254.189.11",
"Username": "root",
"EventName": "Ticket issued",
"SourcePort": 0,
"DestinationIP": "10.254.189.11",
"DestinationPort": 0,
"ActivityStartTime": "2024-06-17 09:09:59"
}
]
},
"status": "Success",
"message": "",
"operation": null,
"execution_time": "22 seconds 767 ms"
}

For the 'convert Json to csv' action, you need to pass the JSON data in list format, specifically from the 'events' field, rather than the entire JSON object.

Please refer the attached screenshot for further details.