akunzai · June 9, 2025 10:01
diff --git a/loki_json_to_csv b/loki_json_to_csv
 #!/bin/bash

 # Script to convert Loki logs JSON to CSV format
 # Usage: ./loki_json_to_csv.sh input.json output.csv

 # Check if jq is installed
 if ! command -v jq &> /dev/null; then
    echo "Error: jq is not installed. Please install it first."
    echo "On macOS: brew install jq"
    echo "On Ubuntu/Debian: sudo apt-get install jq"
    exit 1
 fi

 # Check arguments
 if [ "$#" -ne 2 ]; then
    echo "Usage: $0 <input.json> <output.csv>"
    exit 1
 fi

 INPUT_FILE="$1"
 OUTPUT_FILE="$2"

 # Check if input file exists
 if [ ! -f "$INPUT_FILE" ]; then
    echo "Error: Input file '$INPUT_FILE' not found."
    exit 1
 fi

 # Define the fields we want to extract
 # You can modify this list to include/exclude fields as needed
 FIELDS=(
    "timestamp"
    "body"
    "severity"
    "traceid"
    "spanid"
    "attributes.Application"
    "attributes.VerificationEventId"
    "attributes.ChildEventId"
    "attributes.ChildEventType"
    "attributes.State"
    "attributes.Source"
    "attributes.FinalState"
    "attributes.NewState"
    "attributes.NewSource"
    "attributes.MQ__Exchange"
    "attributes.MQ__MessageId"
    "attributes.MQ__RoutingKey"
    "instrumentation_scope.name"
 )

 # Create CSV header
 HEADER=""
 for field in "${FIELDS[@]}"; do
    if [ -z "$HEADER" ]; then
        HEADER="\"$field\""
    else
        HEADER="$HEADER,\"$field\""
    fi
 done

 echo "$HEADER" > "$OUTPUT_FILE"

 # Function to safely extract JSON value and escape for CSV
 extract_field() {
    local json="$1"
    local field="$2"
    
    # Extract the value using jq
    value=$(echo "$json" | jq -r ".$field // empty" 2>/dev/null)
    
    # If value contains quotes, double them for CSV
    value="${value//\"/\"\"}"
    
    # If value contains comma, newline, or quote, wrap in quotes
    if [[ "$value" =~ [,\"\n] ]]; then
        echo "\"$value\""
    else
        echo "$value"
    fi
 }

 # Function to convert nanosecond timestamp to ISO 8601 format
 convert_timestamp() {
    local timestamp_ns="$1"
    
    # Convert nanoseconds to seconds and nanoseconds remainder
    local seconds=$((timestamp_ns / 1000000000))
    local nanoseconds=$((timestamp_ns % 1000000000))
    
    # Format the date using the date command
    # On macOS, use -r flag for seconds since epoch
    if [[ "$OSTYPE" == "darwin"* ]]; then
        local date_str=$(date -r "$seconds" -u +"%Y-%m-%dT%H:%M:%S")
    else
        # On Linux, use -d flag
        local date_str=$(date -d "@$seconds" -u +"%Y-%m-%dT%H:%M:%S")
    fi
    
    # Add nanoseconds and UTC timezone
    printf "%s.%09dZ" "$date_str" "$nanoseconds"
 }

 # Process each JSON object
 echo "Processing JSON file..."
 line_count=0

 # Read the JSON array and process each object
 jq -c '.[]' "$INPUT_FILE" 2>/dev/null | while IFS= read -r json_obj; do
    line_count=$((line_count + 1))
    
    # Parse the line field which contains the actual log data
    line_data=$(echo "$json_obj" | jq -r '.line' 2>/dev/null)
    
    if [ -n "$line_data" ]; then
        # Parse the nested JSON in the line field
        parsed_data=$(echo "$line_data" | jq '.' 2>/dev/null)
        
        if [ $? -eq 0 ]; then
            # Build CSV row
            ROW=""
            
            # First add the timestamp from the outer object
            timestamp=$(echo "$json_obj" | jq -r '.timestamp // empty' 2>/dev/null)
            if [ -n "$timestamp" ]; then
                iso_timestamp=$(convert_timestamp "$timestamp")
                ROW="$iso_timestamp"
            else
                ROW=""
            fi
            
            # Then add fields from the parsed line data
            for field in "${FIELDS[@]:1}"; do
                value=$(extract_field "$parsed_data" "$field")
                ROW="$ROW,$value"
            done
            
            echo "$ROW" >> "$OUTPUT_FILE"
        else
            echo "Warning: Failed to parse JSON in line $line_count"
        fi
    fi
    
    # Show progress every 100 lines
    if [ $((line_count % 100)) -eq 0 ]; then
        echo "Processed $line_count lines..."
    fi
 done

 echo "Conversion complete! Processed $line_count lines."
 echo "Output saved to: $OUTPUT_FILE"

 # If you want to see the first few rows of the output
 echo -e "\nFirst 5 rows of the CSV:"
 head -n 6 "$OUTPUT_FILE"
	#!/bin/bash

	# Script to convert Loki logs JSON to CSV format
	# Usage: ./loki_json_to_csv.sh input.json output.csv

	# Check if jq is installed
	if ! command -v jq &> /dev/null; then
	echo "Error: jq is not installed. Please install it first."
	echo "On macOS: brew install jq"
	echo "On Ubuntu/Debian: sudo apt-get install jq"
	exit 1
	fi

	# Check arguments
	if [ "$#" -ne 2 ]; then
	echo "Usage: $0 <input.json> <output.csv>"
	exit 1
	fi

	INPUT_FILE="$1"
	OUTPUT_FILE="$2"

	# Check if input file exists
	if [ ! -f "$INPUT_FILE" ]; then
	echo "Error: Input file '$INPUT_FILE' not found."
	exit 1
	fi

	# Define the fields we want to extract
	# You can modify this list to include/exclude fields as needed
	FIELDS=(
	"timestamp"
	"body"
	"severity"
	"traceid"
	"spanid"
	"attributes.Application"
	"attributes.VerificationEventId"
	"attributes.ChildEventId"
	"attributes.ChildEventType"
	"attributes.State"
	"attributes.Source"
	"attributes.FinalState"
	"attributes.NewState"
	"attributes.NewSource"
	"attributes.MQ__Exchange"
	"attributes.MQ__MessageId"
	"attributes.MQ__RoutingKey"
	"instrumentation_scope.name"
	)

	# Create CSV header
	HEADER=""
	for field in "${FIELDS[@]}"; do
	if [ -z "$HEADER" ]; then
	HEADER="\"$field\""
	else
	HEADER="$HEADER,\"$field\""
	fi
	done

	echo "$HEADER" > "$OUTPUT_FILE"

	# Function to safely extract JSON value and escape for CSV
	extract_field() {
	local json="$1"
	local field="$2"

	# Extract the value using jq
	value=$(echo "$json" \| jq -r ".$field // empty" 2>/dev/null)

	# If value contains quotes, double them for CSV
	value="${value//\"/\"\"}"

	# If value contains comma, newline, or quote, wrap in quotes
	if [[ "$value" =~ [,\"\n] ]]; then
	echo "\"$value\""
	else
	echo "$value"
	fi
	}

	# Function to convert nanosecond timestamp to ISO 8601 format
	convert_timestamp() {
	local timestamp_ns="$1"

	# Convert nanoseconds to seconds and nanoseconds remainder
	local seconds=$((timestamp_ns / 1000000000))
	local nanoseconds=$((timestamp_ns % 1000000000))

	# Format the date using the date command
	# On macOS, use -r flag for seconds since epoch
	if [[ "$OSTYPE" == "darwin"* ]]; then
	local date_str=$(date -r "$seconds" -u +"%Y-%m-%dT%H:%M:%S")
	else
	# On Linux, use -d flag
	local date_str=$(date -d "@$seconds" -u +"%Y-%m-%dT%H:%M:%S")
	fi

	# Add nanoseconds and UTC timezone
	printf "%s.%09dZ" "$date_str" "$nanoseconds"
	}

	# Process each JSON object
	echo "Processing JSON file..."
	line_count=0

	# Read the JSON array and process each object
	jq -c '.[]' "$INPUT_FILE" 2>/dev/null \| while IFS= read -r json_obj; do
	line_count=$((line_count + 1))

	# Parse the line field which contains the actual log data
	line_data=$(echo "$json_obj" \| jq -r '.line' 2>/dev/null)

	if [ -n "$line_data" ]; then
	# Parse the nested JSON in the line field
	parsed_data=$(echo "$line_data" \| jq '.' 2>/dev/null)

	if [ $? -eq 0 ]; then
	# Build CSV row
	ROW=""

	# First add the timestamp from the outer object
	timestamp=$(echo "$json_obj" \| jq -r '.timestamp // empty' 2>/dev/null)
	if [ -n "$timestamp" ]; then
	iso_timestamp=$(convert_timestamp "$timestamp")
	ROW="$iso_timestamp"
	else
	ROW=""
	fi

	# Then add fields from the parsed line data
	for field in "${FIELDS[@]:1}"; do
	value=$(extract_field "$parsed_data" "$field")
	ROW="$ROW,$value"
	done

	echo "$ROW" >> "$OUTPUT_FILE"
	else
	echo "Warning: Failed to parse JSON in line $line_count"
	fi
	fi

	# Show progress every 100 lines
	if [ $((line_count % 100)) -eq 0 ]; then
	echo "Processed $line_count lines..."
	fi
	done

	echo "Conversion complete! Processed $line_count lines."
	echo "Output saved to: $OUTPUT_FILE"

	# If you want to see the first few rows of the output
	echo -e "\nFirst 5 rows of the CSV:"
	head -n 6 "$OUTPUT_FILE"