Skip to content

Instantly share code, notes, and snippets.

@akunzai

akunzai/loki_json_to_csv

Last active June 9, 2025 10:01
Show Gist options
  • Save akunzai/0e14f68d810da2bc01930a063fe1a5c7 to your computer and use it in GitHub Desktop.
Save akunzai/0e14f68d810da2bc01930a063fe1a5c7 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. akunzai revised this gist Jun 9, 2025. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion loki_json_to_csv
    View file
    Original file line number Diff line number Diff line change
    @@ -46,7 +46,6 @@ FIELDS=(
    "attributes.MQ__Exchange"
    "attributes.MQ__MessageId"
    "attributes.MQ__RoutingKey"
    "resources.service.name"
    "instrumentation_scope.name"
    )

  2. akunzai created this gist Jun 9, 2025.
    156 changes: 156 additions & 0 deletions loki_json_to_csv
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,156 @@
    #!/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"
    "resources.service.name"
    "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"