json log

[zaydwazuh]

  Hello Community,

Any idea for using Wazuh server  alerts.log and alerts.json with python.

how can i read json files and how can convert it to csv.

i try to read files but i get many errors.

[Md. Nazmur Sakib]

Hi Zayd,

Hope you are doing well. Thank you for using Wazuh.

You can achieve this by writing some Python script.

For the given example you will need pthon3 and Pandas Python library.

First, install python3 

Next, install the pandas library with pip

sudo apt install python3-pip

pip3 install pandas

Create a python script code.py

vi /tmp/code.py

Copy the code:

import json

import pandas as pd

def convert_json_to_csv(input_file, output_file):

    with open(input_file, 'r') as f:

        data = f.readlines()

    json_data = [json.loads(line) for line in data]  # Load JSON data safely

    df = pd.json_normalize(json_data)  # Convert JSON data to DataFrame

    df.to_csv(output_file, index=False)  # Save DataFrame to CSV

# Example usage:

convert_json_to_csv('alert.json', 'alert.csv')

And save the code.py file.

Note: the alert.json is the name of the JSON file.

Copy the JSON file you want to convert to the same directory where the copy.py is.

Ex:

cp /var/ossec/logs/alerts/2023/Aug/ossec-alerts-20.json /tmp/alert.json

Now run 

python3 code.py

It will create an alert.csv file.

Please let me know if this helps or if you need any further information.

Regards

Md. Nazmur Sakib

[zaydwazuh]

thanks a lot for your help its worked

[zaydwazuh]

Unfortunately, when I used another file to convert it, this error appeared

json.decoder.JSONDecodeError: Extra data: line 1 column 713 (char 712)

also first time this error appeared (UnicodeDecodeError: 'charmap' codec can't decode byte 0x9d in position 3466: character maps to <undefined>)

i added (( encoding="utf-8")) to the code and it worked

[Md. Nazmur Sakib]

Hi Zayd!

Hope you are doing well. 

You have some records in your json file, and json.loads() is not able to decode. You need to do it record by record. 

See Python json.loads shows ValueError: Extra data

I hope this information helps.

Regards

Md. Nazmur Sakib