FortiSOAR Discussions
shashankkumar
New Contributor

Convert step output to csv format

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"
}

Shashank
Shashank
3 REPLIES 3
Jtamboli
Staff
Staff

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 :)

Junaid
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

Shashank
Shashank
ranjeet
New Contributor III

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.


json to csv.PNG