Convert from UDM Search JSON Result to CSV

[donkos]

I'd like to convert the JSON result from a UDM Search into a csv string. 

The current issue I'm having is that if I just specify JsonResult|"udm.events", then the csv sheet seems to merge all the sections into one big single row blob - would be curious to see some suggestions without using some custom action code.

[James_E]

@donkos Can you provide more information on what you're using to get the UDM Search results? Are you using the API or the Export through the UI?

[cincyohio]

Hi @donkos 

I'm going to try my best with the limited amount of information given. The information may or may not be factually accurate, given your scope. 

Converting JSON from UDM Search to CSV

To convert JSON results from a UDM search into a CSV string without writing custom code, follow these steps:

1. Understand Your JSON Structure:
Carefully examine the structure of your JSON data. It likely contains nested objects or arrays that need to be flattened for CSV conversion.

2. Select Relevant Data:
Determine which keys (fields) from the JSON you want to include as columns in your CSV.

3. Use Transformation Tools:
Leverage tools like PowerShell, Python, or even Excel to handle the conversion. These tools often have built-in functions to simplify the process.

Example Using Python:
Let's say your JSON looks like this:

{
    "udm": {
        "events": [
            {"id": 1, "name": "Event 1", "date": "2024-01-01", "location": "Location A"},
            {"id": 2, "name": "Event 2", "date": "2024-02-01", "location": "Location B"}
        ]
    }
}
content_copy
Use code with caution.
Json

Here's a Python script to convert it to CSV:
import json
import csv
import io

# Sample JSON data
json_data = '''
{
    "udm": {
        "events": [
            {"id": 1, "name": "Event 1", "date": "2024-01-01", "location": "Location A"},
            {"id": 2, "name": "Event 2", "date": "2024-02-01", "location": "Location B"}
        ]
    }
}
'''

# Load JSON data
data = json.loads(json_data)

# Extract events
events = data['udm']['events']

# Create CSV in string format
output = io.StringIO()
csv_writer = csv.DictWriter(output, fieldnames=events[0].keys())
csv_writer.writeheader()
csv_writer.writerows(events)

# Get CSV string
csv_string = output.getvalue()

print(csv_string)
content_copy
Use code with caution.
Python

Output:
id,name,date,location
1,Event 1,2024-01-01,Location A
2,Event 2,2024-02-01,Location B
content_copy

Using Built-in Tools:
Many tools have built-in features to help:
Import JSON: Directly import your JSON file.
Flatten JSON: Some tools have options to automatically flatten nested structures.
Export to CSV: Once your data is in a tabular format, export it as a CSV file.

Key Points:
Understand your JSON structure.
Flatten nested data if necessary.
Use built-in transformation tools in your environment.

[AbdElHafez]

If you are allowed to use external python libraries then probably the easiset way is to use Pandas and the normalize_json() function to flatten your json into a CSV, there are some non-default libraries that could do so as well, or you could build nested loops to flatten the repeated fields.
otherwise you could use some template-based conversion like Apache-nifi with Avro format or even a logstash parser but that is a huge effort.

A possible is to use an locally downloaded LLM like BERT to do the conversion.