# Get value from arguments
while getopts "::U::w::p::f::z::s::e::h" arg
do
    case "$arg" in
        U) USER=${OPTARG};;
        w) PASSWORD=${OPTARG};;
        p) PORT=${OPTARG};;
        f) OUT_FORMAT=${OPTARG};;
        z) DBHOST=${OPTARG};;
        s) START_TIME=${OPTARG};;
        e) END_TIME=${OPTARG};;
        h) echo " "
           echo " Collect information for Spread issues"
           echo " "
           echo " Usage: "
           echo "   ./collect_for_spread.sh [-U username] [-w password] [-p port] [-f output_format] [-z dbhost] -s start_time -e end_time"
           echo " "
           echo " Arguments description:"
           echo "   -U Use database administrator account for username."
           echo "   -w Database administrator account password in single quotes. Can be skipped in case no password is set."
           echo "   -p Database server port. Can be skipped if database is running on default port 5433."
           echo "   -f Specifies output mode. Can be stdout or file. Default is stdout."
           echo "   -s Specifies start time in single quotes. Format is YYYY-MM-DD HH24:MI:SS."
           echo "   -e Specifies start time in single quotes. Format is YYYY-MM-DD HH24:MI:SS."
           echo "   -z is optional if dbhost is needed for connecting to Vertica."
           echo " "
           exit 0;;
        *) echo "Insufficient/invalid arguments. Run './collect_for_spread.sh -h' to see correct usage."; exit 1;;
    esac
done

# Check parameter values
if [ "$USER" != "" ]; then
    us="-U $USER"
    if [ "$DBHOST" != "" ]; then
        us="-X $us -h $DBHOST"
    fi
fi
if [ "$PASSWORD" != "" ]; then
    pw="-w $PASSWORD"
fi
if [ "$PORT" != "" ]; then
    pt="-p $PORT"
fi
if [ "$OUT_FORMAT" != "" -a "$OUT_FORMAT" != "stdout" -a "$OUT_FORMAT" != "file" ]; then
    echo "Allowed value for -f is stdout or file. Run './collect_for_spread.sh -h' for more information."
    exit 1
elif [ "$OUT_FORMAT" = "file" ]; then
    of="-At"
fi
if [ "$START_TIME" = "" -o "$END_TIME" = "" ]; then
	echo "-s and -e cannot be empty. Run './collect_for_spread.sh -h' for more information."
	exit 1
fi

# Collect information for 'b. Network Bandwidth Starvation' section
echo " "
echo " ======================================================="
echo "  b. Network Bandwidth Starvation"
echo " ======================================================="
echo "   b-1. Network usage from DC_NETWORK_INFO"
echo " ======================================================="
echo " "
if [ "$of" != "" ]; then
    ofile="-o ./spdebug_dc_network.dat"
    echo "   outputting to spdebug_dc_network.dat file..."
    echo " "
fi
/opt/vertica/bin/vsql $us $pw $pt $of $ofile << EOF
SELECT
    node_name
   ,start_time
   ,end_time
   ,tx_kbytes_per_sec
   ,rx_kbytes_per_sec
   ,tx_kbytes_per_sec + rx_kbytes_per_sec total_kbytes_per_sec
FROM (
    SELECT
        node_name
       ,round(min(start_time), 'SS') AS start_time
       ,round(max(end_time), 'SS') AS end_time
       ,round(((sum(tx_bytes_end_value - tx_bytes_start_value) / 1024) / (datediff('millisecond', min(start_time), max(end_time)) / 1000))::FLOAT, 2) AS tx_kbytes_per_sec
       ,round(((sum(rx_bytes_end_value - rx_bytes_start_value) / 1024) / (datediff('millisecond', min(start_time), max(end_time)) / 1000))::FLOAT, 2) AS rx_kbytes_per_sec
    FROM v_internal.dc_network_info_by_second
    WHERE start_time >= '$START_TIME' AND end_time <= '$END_TIME'
      AND (node_name, interface_id) IN (
              SELECT
                  node_name
                 ,interface
              FROM v_monitor.network_interfaces
              WHERE (node_name, ip_address, ip_address_family) IN (SELECT node_name, node_address, node_address_family FROM v_catalog.nodes)
          )
     GROUP BY
        node_name
       ,round(start_time, 'SS')
     ) a
ORDER BY start_time, node_name;
EOF
echo " "
echo " ======================================================="
echo "   b-2. Network performance from vnetperf"
echo " ======================================================="
echo " "
if [ "$of" != "" ]; then
    echo "   outputting to spdebug_vnetperf.dat file..."
    echo " "
    /opt/vertica/bin/vnetperf > ./spdebug_vnetperf.dat
else
    /opt/vertica/bin/vnetperf
fi

# Collect information for 'c. Disk I/O Bandwidth Starvation' section
echo " "
echo " ======================================================="
echo "  c. Disk I/O Bandwidth Starvation"
echo " ======================================================="
echo "   c-1. Disk I/O usage from DC_IO_INFO"
echo " ======================================================="
echo " "
if [ "$of" != "" ]; then
    ofile="-o ./spdebug_dc_io.dat"
    echo "   outputting to spdebug_dc_io.dat file..."
    echo " "
fi
/opt/vertica/bin/vsql $us $pw $pt $of $ofile << EOF
SELECT
    node_name
   ,device_name
   ,start_time
   ,end_time
   ,read_mbytes_per_sec
   ,written_mbytes_per_sec
   ,read_mbytes_per_sec + written_mbytes_per_sec total_mbytes_per_sec
FROM (
    SELECT
        node_name
       ,device_name
       ,round(min(start_time), 'SS') AS start_time
       ,round(max(end_time), 'SS') AS end_time
       ,round(((sum(total_read_bytes_end_value - total_read_bytes_start_value) / 1024 / 1024) / (datediff('millisecond', min(start_time), max(end_time)) / 1000))::FLOAT, 2) AS read_mbytes_per_sec
       ,round(((sum(total_written_bytes_end_value - total_written_bytes_start_value) / 1024 / 1024) / (datediff('millisecond', min(start_time), max(end_time)) / 1000))::FLOAT, 2) AS written_mbytes_per_sec
    FROM v_internal.dc_io_info_by_second
    WHERE start_time >= '$START_TIME' AND end_time <= '$END_TIME'
    GROUP BY
        node_name
       ,device_name
       ,round(start_time, 'SS')
    ) a
ORDER BY start_time, node_name, device_name;
EOF
echo " "
echo " ======================================================="
echo "   c-2. Disk I/O performance from vioperf"
echo " ======================================================="
echo " "
if [ "$of" != "" ]; then
    echo "   outputting to spdebug_vioperf.dat file..."
    echo " "
    > ./spdebug_vioperf.dat
fi

for node_info in `/opt/vertica/bin/vsql -At -c 'SELECT name, address FROM v_internal.vs_nodes ORDER BY name'`
do
    IFS="|"
    set -- $node_info
    node_name=$1
    node_address=$2
    disk_info=`/opt/vertica/bin/vsql -At -c "SELECT storage_path FROM disk_storage WHERE node_name = '$node_name' AND storage_status = 'Active'" | tr '\n' ' '`
    if [ "$of" != "" ]; then
        echo "[$node_name]" >> ./spdebug_vioperf.dat
        ssh $node_address "/opt/vertica/bin/vioperf --duration=\"2 minutes\" $disk_info" >> ./spdebug_vioperf.dat
    else
        echo "[$node_name]"
        ssh $node_address "/opt/vertica/bin/vioperf --duration=\"2 minutes\" $disk_info"
    fi
    unset IFS
done

# Collect information for 'd. Network UDP Receive Error and TCP Retransmitted' section
echo " "
echo " ======================================================="
echo "  d. Network UDP Receive Error and TCP Retransmitted"
echo " ======================================================="
echo " "
if [ "$of" != "" ]; then
    ofile="-o ./spdebug_dc_netstats.dat"
    echo "   outputting to spdebug_dc_netstats.dat file..."
    echo " "
fi
/opt/vertica/bin/vsql $us $pw $pt $of $ofile << EOF
SELECT
    node_name
   ,start_time
   ,end_time
   ,upd_packet_receive_errors
   ,udp_packets_received
   ,CASE udp_packets_received
      WHEN 0 THEN 0
      ELSE round((upd_packet_receive_errors / udp_packets_received * 100)::FLOAT, 2)
    END AS upd_packet_receive_error_ratio
   ,tcp_segments_retransmited
   ,tcp_segments_received
   ,CASE tcp_segments_received
      WHEN 0 THEN 0
      ELSE round((tcp_segments_retransmited / tcp_segments_received * 100)::FLOAT, 2)
    END AS tcp_segments_retrans_ratio
FROM (
    SELECT
        node_name
       ,round(min(start_time), 'SS') AS start_time
       ,round(max(end_time), 'SS') AS end_time
       ,sum(udp_in_errors_end_value - udp_in_errors_start_value) AS upd_packet_receive_errors
       ,sum(udp_in_datagrams_end_value - udp_in_datagrams_start_value) AS udp_packets_received
       ,sum(tcp_retrans_segs_end_value - tcp_retrans_segs_start_value) AS tcp_segments_retransmited
       ,sum(tcp_in_segs_end_value - tcp_in_segs_start_value) AS tcp_segments_received
    FROM
        v_internal.dc_netstats_by_second
    WHERE start_time >= '$START_TIME' AND end_time <= '$END_TIME'
    GROUP BY
        node_name
       ,round(start_time, 'SS')
    ) a
ORDER BY start_time, node_name;
EOF

# Collect information for 'e. Memory Starvation' section
echo " "
echo " ======================================================="
echo "  e. Memory Starvation"
echo " ======================================================="
echo "   e-1. Memory usage from DC_MEMORY_INFO"
echo " ======================================================="
echo " "
if [ "$of" != "" ]; then
    ofile="-o ./spdebug_dc_memory.dat"
    echo "   outputting to spdebug_dc_memory.dat file..."
    echo " "
fi
/opt/vertica/bin/vsql $us $pw $pt $of $ofile << EOF
SELECT
    node_name
   ,start_time
   ,end_time
   ,average_memory_usage_percent
FROM (
    SELECT
        node_name
       ,round(min(start_time), 'SS') AS start_time
       ,round(max(end_time), 'SS') AS end_time
       ,round(100 -
           (max(free_memory_sample_sum) / max(free_memory_sample_count)
            + max(buffer_memory_sample_sum) / max(free_memory_sample_count)
            + max(file_cache_memory_sample_sum) / max(file_cache_memory_sample_count)) /
           (max(total_memory_sample_sum) / max(total_memory_sample_count)) * 100.0, 2)
        AS average_memory_usage_percent
    FROM
        v_internal.dc_memory_info_by_second
    WHERE start_time >= '$START_TIME' AND end_time <= '$END_TIME'
    GROUP BY
        node_name
       ,round(start_time, 'SS')
    ) a
ORDER BY start_time, node_name;
EOF
echo " "
echo " ======================================================="
echo "   e-2. Catalog size"
echo " ======================================================="
echo " "
if [ "$of" != "" ]; then
    ofile="-o ./spdebug_dc_allocation.dat"
    echo "   outputting to spdebug_dc_allocation.dat file..."
    echo " "
fi
/opt/vertica/bin/vsql $us $pw $pt $of $ofile << EOF
SELECT
    node_name
   ,max(ts) AS ts
   ,max(catalog_size_in_mb) AS catalog_size_in_mb
FROM (
    SELECT
        node_name
       ,trunc(("time")::TIMESTAMP, 'SS') AS ts
       ,sum((total_memory_max_value - free_memory_min_value)) / 1024 / 1024 AS catalog_size_in_mb
    FROM
        v_internal.dc_allocation_pool_statistics_by_second
    GROUP BY
        node_name
       ,trunc(("time")::TIMESTAMP, 'SS')
    ) a
GROUP BY node_name
ORDER BY node_name;
EOF

# Collect information for 'f. CPU Bound' section
echo " "
echo " ======================================================="
echo "  f. CPU Bound"
echo " ======================================================="
echo " "
if [ "$of" != "" ]; then
    ofile="-o ./spdebug_dc_cpu.dat"
    echo "   outputting to spdebug_dc_cpu.dat file..."
    echo " "
fi
/opt/vertica/bin/vsql $us $pw $pt $of $ofile << EOF
SELECT
    node_name
   ,processor_id
   ,start_time
   ,end_time
   ,average_cpu_usage_percent
FROM (
    SELECT
        node_name
       ,processor_id
       ,round(start_time, 'SS') AS start_time
       ,round(end_time, 'SS') AS end_time
       ,round(100 - (
            (idle_microseconds_end_value - idle_microseconds_start_value)
          / (user_microseconds_end_value + nice_microseconds_end_value + system_microseconds_end_value
               + idle_microseconds_end_value + io_wait_microseconds_end_value + irq_microseconds_end_value
               + soft_irq_microseconds_end_value + steal_microseconds_end_value + guest_microseconds_end_value
               - user_microseconds_start_value - nice_microseconds_start_value - system_microseconds_start_value
               - idle_microseconds_start_value - io_wait_microseconds_start_value - irq_microseconds_start_value
               - soft_irq_microseconds_start_value - steal_microseconds_start_value - guest_microseconds_start_value
            )
        ) * 100.0, 2) AS average_cpu_usage_percent
    FROM
        v_internal.dc_cpu_aggregate_by_second
    WHERE start_time >= '$START_TIME' AND end_time <= '$END_TIME'
    ) a
ORDER BY start_time, node_name, processor_id;
EOF
