Message board

#!/bin/bash

if [ $# -lt 1 ]; then
   echo "Provide a ticket number. It's case sensitive"
   exit
fi


sql="
WITH t1
    AS ( SELECT *, round (( ( fetched :: NUMERIC / read :: NUMERIC ) * 100) :: NUMERIC, 2) AS Ratio 
         FROM   metrics_index_usage
         WHERE  fetched > 0
         AND project_id = '$1'
       )
SELECT tablename as relname, indexname, read, fetched, ratio,
      CASE
        WHEN t1.ratio = 100 THEN 'Excellent'
        WHEN t1.ratio >= 95 AND t1.ratio < 100 THEN 'Good'
        WHEN t1.ratio >= 85 AND t1.ratio < 95 THEN 'Average'
        WHEN t1.ratio >= 70 AND t1.ratio < 85 THEN 'Ok'
        WHEN t1.ratio >= 50 AND t1.ratio < 70 THEN 'Bad'
        WHEN t1.ratio >= 45 AND t1.ratio < 50 THEN 'Really bad'
        WHEN t1.ratio < 45 THEN 'Fire!'
      END AS status
FROM   t1
ORDER  BY t1.ratio;
"

export PGPASSWORD="postgres"

psql -h localhost -p 5434 -U postgres audits -c "$sql"