shell script to find out long running session details in AWS Redshift database

 Here is the shell script to monitor the Long running session details in AWS redshift, we can schedule it .

##################################################################################################

### purpose     :script used to collect Redshift long running session details                     #

### Author      : Bala P                                                                          #

### Developed   :10-july-2023 V1                                                                  #

###################################################################################################

##

##!/usr/bin/bash

##

export PGDATABASE=$1

export PGUSER=rsdbadmin

export PGHOST=XXXXXXXXXX.redshift.amazonaws.com

export PGPORT='5439'

export PGPASSWORD='XXXXXXXXXXXXXXX'

export wdir=/redshiftadmin/aws/audit/scripts/

export logdir=/redshiftadmin/aws/audit/log

 query_result=$(psql -tA -c "select SPLIT_PART(SUBSTRING(user_name, 5), '@', 1) as user_name, DATE_TRUNC('second',starttime) as start_time,status,pid,duration/ (1000000.0 * 60 * 60) AS exec_time_hoursi,query  from stv_recents where user_name not in ('rdsdb') and duration/(1000000.0 * 60 * 60) > 3;")

#

 if [[ -n "$query_result" ]]; then

recipient="balamani@abc.com"

       subject="Queries with Execution Time > 3 hours @ $PGDATABASE in PROD"

       body="user_name|starttime |state|pid|exec_time_hours, queries  execution time greater than 3 hours PROD:\n\n$query_result"

     echo -e "$body" | mailx -s "$subject" "$recipient"

            fi