#!/usr/bin/env bash set -e daily_report_bot_url="https://open.feishu.cn/open-apis/bot/v2/hook/6aed0f4e-f7f1-4223-b5a3-d503e7100587" alert_url="$daily_report_bot_url" scope="test" countrycode_file="/tmp/fileforcountrycode_$scope" if [ "$scope" == "test" ] then bot_url="https://open.feishu.cn/open-apis/bot/v2/hook/6aed0f4e-f7f1-4223-b5a3-d503e7100587" temp_file_sql="/tmp/temp_file_sql_test" tmp_json_file="/tmp/resulofsomething_test.json" plan_type="on_demand" user_count="383" alert_json_template="/tmp/alertjsontemplate.json" alert_json="/tmp/alertjsonitself.json" mysql_command="mysql -ustaging -htidb.e23bdccc.cb17aaf2.us-west-2.prod.aws.tidbcloud.com -P4000 -pZqfjTO5G_CJx%AdiqFuyVS6P staging_central" alert_title="Test bot" title="Testing User Behavior Bot" color="blue" sleep_time="10" machine="mac" clusterid_cache="/tmp/clusterid_cache_test" clusterid_to_remove="/tmp/clusterid_to_remove_test" tmp_clusterid="/tmp/tmp_clusterid_test" swap_file="/tmp/clusterid_cache_swap_test" elif [ "$scope" == "poc" ] then bot_url="https://open.feishu.cn/open-apis/bot/v2/hook/31da37e4-7a81-470f-b773-96dc09afb7e6" temp_file_sql="/tmp/temp_file_sql" tmp_json_file="/tmp/resulofsomething.json" plan_type="poc" user_count="25" alert_json_template="/tmp/alertjsontemplate4poc.json" alert_json="/tmp/alertjsonitself4poc.json" mysql_command="mysql -h metadb-tidb -P 4000 dbaas" alert_title="POC user consciousness bot" title="POC User Behavior" color="orange" sleep_time="900" machine="linux" clusterid_cache="/tmp/clusterid_cache_poc" clusterid_to_remove="/tmp/clusterid_to_remove_poc" tmp_clusterid="/tmp/tmp_clusterid_poc" swap_file="/tmp/clusterid_cache_swap_poc" elif [ "$scope" == "ondemand" ] then bot_url="https://open.feishu.cn/open-apis/bot/v2/hook/a82ed720-25b1-4a6a-93aa-f8a4d0d4f44c" temp_file_sql="/tmp/temp_file_sql_od" tmp_json_file="/tmp/resulofsomething_od.json" plan_type="on_demand" user_count="34" alert_json_template="/tmp/alertjsontemplate4od.json" alert_json="/tmp/alertjsonitself4od.json" mysql_command="mysql -h metadb-tidb -P 4000 dbaas" alert_title="On demand user consciousness bot" title="On-demand User Behavior" color="blue" sleep_time="900" machine="linux" clusterid_cache="/tmp/clusterid_cache_od" clusterid_to_remove="/tmp/clusterid_to_remove_od" tmp_clusterid="/tmp/tmp_clusterid_od" swap_file="/tmp/clusterid_cache_swap_od" else exit 1 fi # Generate country_code file cat > $countrycode_file << EOF AD,EMEA AE,EMEA AF,EMEA AG,NA AI,NA AL,EMEA AM,EMEA AO,EMEA AQ,EMEA AR,NA AS,NA AT,EMEA AU,APAC AW,NA AX,EMEA AZ,EMEA BA,EMEA BB,NA BD,APAC BE,EMEA BF,EMEA BG,EMEA BH,EMEA BI,EMEA BJ,EMEA BL,EMEA BM,NA BN,APAC BO,NA BQ,EMEA BR,NA BS,NA BT,APAC BV,EMEA BW,EMEA BY,EMEA BZ,NA CA,NA CC,APAC CD,EMEA CF,EMEA CG,EMEA CH,EMEA CI,EMEA CK,EMEA CL,NA CM,EMEA CN,CO CO,NA CR,NA CU,NA CV,EMEA CW,EMEA CX,APAC CY,EMEA CZ,EMEA DE,EMEA DJ,EMEA DK,EMEA DM,NA DO,NA DZ,EMEA EC,NA EE,EMEA EG,EMEA EH,EMEA ER,EMEA ES,EMEA ET,EMEA FI,EMEA FJ,APAC FK,NA FM,APAC FO,EMEA FR,EMEA GA,EMEA GB,EMEA GD,NA GE,EMEA GF,EMEA GG,EMEA GH,EMEA GI,EMEA GL,EMEA GM,EMEA GN,EMEA GP,EMEA GQ,EMEA GR,EMEA GS,NA GT,NA GU,NA GW,EMEA GY,NA HK,CO HM,EMEA HN,NA HR,EMEA HT,NA HU,EMEA ID,APAC IE,EMEA IL,EMEA IM,EMEA IN,APAC IO,EMEA IQ,EMEA IR,EMEA IS,EMEA IT,EMEA JE,EMEA JM,NA JO,EMEA JP,Japan KE,EMEA KG,EMEA KH,APAC KI,APAC KM,EMEA KN,NA KP,APAC KR,APAC KW,EMEA KY,NA KZ,EMEA LA,APAC LB,EMEA LC,NA LI,EMEA LK,APAC LR,EMEA LS,EMEA LT,EMEA LU,EMEA LV,EMEA LY,EMEA MA,EMEA MC,EMEA MD,EMEA ME,EMEA MF,EMEA MG,EMEA MH,APAC MK,EMEA ML,EMEA MM,APAC MN,APAC MO,CO MP,APAC MQ,EMEA MR,EMEA MS,NA MT,EMEA MU,EMEA MV,APAC MW,EMEA MX,NA MY,APAC MZ,EMEA NA,EMEA NC,EMEA NE,EMEA NF,APAC NG,EMEA NI,NA NL,EMEA NO,EMEA NP,APAC NR,APAC NU,APAC NZ,APAC OM,EMEA PA,NA PE,NA PF,EMEA PG,APAC PH,APAC PK,APAC PL,EMEA PM,EMEA PN,EMEA PR,NA PS,EMEA PT,EMEA PW,APAC PY,NA QA,EMEA RE,EMEA RO,EMEA RS,EMEA RU,EMEA RW,EMEA SA,EMEA SB,APAC SC,EMEA SD,EMEA SE,EMEA SG,APAC SH,EMEA SI,EMEA SJ,EMEA SK,EMEA SL,EMEA SM,EMEA SN,EMEA SO,EMEA SR,NA SS,EMEA ST,EMEA SV,NA SX,EMEA SY,EMEA SZ,EMEA TC,NA TD,EMEA TF,EMEA TG,EMEA TH,APAC TJ,EMEA TK,APAC TL,APAC TM,EMEA TN,EMEA TO,APAC TR,EMEA TT,NA TV,APAC TW,CO TZ,EMEA UA,EMEA UG,EMEA UM,NA US,NA UY,NA UZ,EMEA VA,EMEA VC,NA VE,NA VG,NA VI,NA VN,APAC VU,APAC WF,EMEA WS,APAC YE,EMEA YT,EMEA ZA,EMEA ZM,EMEA ZW,EMEA EOF cat > $alert_json_template <" } ] } } EOF cat $alert_json_template | sed "s/TITLE_PLACEHOLDER/$alert_title/" > $alert_json cat $alert_json appendRegionInfo() { while IFS= read -r line do country=$(echo "$line" | cut -f1) #country="SG" if [ -z "$country" ]; then region_country="null" else region_country=$(grep -i "$country," $countrycode_file | sed 's/^[ \t]*//g' | awk -F ',' '{print $2 "-" $1}') if [ -z "$region_country" ]; then region_country="null-$country" fi fi echo "$line" | awk -F "\t" -v OFS="\t" "{\$1=\"$region_country\";print \$0 }" done } preprocess() { if [ "$scope" == "test" ] then tr '\n' ' ' | sed 's/WHERE.*;/LIMIT 3;/' else while IFS= read -r line do echo "$line" | sed "s/TIMESTAMP_TAG_LEFT/'$1'/" | sed "s/TIMESTAMP_TAG_RIGHT/'$2'/" done fi } prepareAlertJson() { if [ "$machine" == "mac" ] then sed -i '' "s/OLD_COUNT_TAG/$user_count/" $alert_json sed -i '' "s/NEW_COUNT_TAG/$new_count/" $alert_json else sed -i "s/OLD_COUNT_TAG/$user_count/" $alert_json sed -i "s/NEW_COUNT_TAG/$new_count/" $alert_json fi } # Backup metrics backup_start_sql="SELECT t.country, t.id,t.name,t.alias,c.id,c.name,c.status,b.id,b.status, bs.type FROM backups b JOIN backup_schedules bs ON b.backup_schedule_id = bs.id JOIN clusters c ON c.id = bs.cluster_id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.status = 'active' and t.kind = 'external' And c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and b.created_at >= TIMESTAMP_TAG_LEFT and b.created_at < TIMESTAMP_TAG_RIGHT and (bs.type = 'manual' or b.status = 'failed');" backup_finished_sql="SELECT t.country, t.id,t.name,t.alias,c.id,c.name,c.status,b.id,b.status, bs.type, t.country FROM backups b JOIN backup_schedules bs ON b.backup_schedule_id = bs.id JOIN clusters c ON c.id = bs.cluster_id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.status = 'active' and t.kind = 'external' And c.status not in ( 'cleared', 'deleted', 'deletecomplete', 'unknown') and b.finished_at >= TIMESTAMP_TAG_LEFT and b.finished_at < TIMESTAMP_TAG_RIGHT and (bs.type = 'manual' or b.status = 'failed');" backup_longlast_sql="SELECT t.country, t.id,t.name,t.alias,c.id,c.name,c.status,b.id,b.status, bs.type, t.country FROM backups b JOIN backup_schedules bs ON b.backup_schedule_id = bs.id JOIN clusters c ON c.id = bs.cluster_id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and c.id <> '1311285575352848384' and t.plan = '$plan_type' and t.kind = 'external' and t.status = 'active' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and b.deleted_at is null and b.status = 'running' And b.created_at < date_add(now(), interval - 10 hour);" # Restore metrics restore_start_sql="select t.country, t.id,t.name,t.alias,c.id,c.name,c.status,r.id,r.status from restores r JOIN clusters c ON c.id = r.cluster_id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.status = 'active' and t.kind = 'external' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') And r.created_at >= TIMESTAMP_TAG_LEFT and r.created_at < TIMESTAMP_TAG_RIGHT;" restore_finished_sql="select t.country, t.id,t.name,t.alias,c.id,c.name,c.status,r.id,r.status from restores r JOIN clusters c ON c.id = r.cluster_id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.status = 'active' and t.kind = 'external' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') And r.finished_at >= TIMESTAMP_TAG_LEFT and r.finished_at < TIMESTAMP_TAG_RIGHT;" restore_longlast_sql="select t.country, t.id,t.name,t.alias,c.id,c.name,c.status,r.id,r.status from restores r JOIN clusters c ON c.id = r.cluster_id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.status = 'active' and t.kind = 'external' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and r.status = 'running' And r.created_at < date_add(now(), interval - 3 hour);" # Import metrics import_start_sql="select t.country, t.id,t.name,t.alias,c.id,c.name,c.status,i.id,i.status from imports i join clusters c ON c.id = i.cluster_id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.status = 'active' and t.kind = 'external' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') And i.created_at >= TIMESTAMP_TAG_LEFT and i.created_at < TIMESTAMP_TAG_RIGHT;" import_finished_sql="select t.country, t.id,t.name,t.alias,c.id,c.name,c.status,i.id,i.status from imports i join clusters c ON c.id = i.cluster_id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.status = 'active' and t.kind = 'external' And c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and i.finished_at >= TIMESTAMP_TAG_LEFT and i.finished_at < TIMESTAMP_TAG_RIGHT;" import_longlast_sql="select t.country, t.id,t.name,t.alias,c.id,c.name,c.status,i.id,i.status from imports i join clusters c ON c.id = i.cluster_id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.status = 'active' and t.kind = 'external' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and i.status = 'running' And i.created_at < date_add(now(), interval - 3 hour);" # New POC user metrics user_count_sql="select count(*) from tenants t WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.status = 'active' and t.kind = 'external';" new_user_sql="select t.country, t.id,t.name,t.alias from tenants t WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.status = 'active' And t.updated_at >= TIMESTAMP_TAG_LEFT and t.updated_at < TIMESTAMP_TAG_RIGHT and t.kind = 'external';" # Cluster creation metrics cluster_creation_start_sql="SELECT t.country, t.id, t.name, t.alias, c.id, c.name, c.status FROM clusters c JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.kind = 'external' and t.status = 'active' and c.status in ('created','initializing','preparing','launching', 'normal') AND c.created_at >= TIMESTAMP_TAG_LEFT and c.created_at < TIMESTAMP_TAG_RIGHT ;" cluster_creation_finish_sql="SELECT t.country, t.id, t.name, t.alias, c.id, c.name, c.status FROM clusters c JOIN tenants t ON c.tenant_id = t.id WHERE c.id = CLUSTERID_PLACEHOLDER and c.status not in ('created','initializing','preparing','launching');" cluster_creation_longlast_sql="SELECT t.country, t.id, t.name, t.alias, c.id, c.name, c.status FROM clusters c JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.kind = 'external' and t.status = 'active' and c.status in ('created', 'initializing', 'preparing', 'launching') and c.created_at < DATE_ADD(NOW(), INTERVAL - 30 minute);" # Cluster scaling metrics cluster_scaling_start_sql="SELECT t.country, t.id, t.name, t.alias, c.id, c.name, c.status,co.id,co.status FROM cluster_operations co join clusters c on co.cluster_id= c.id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.kind = 'external' and t.status = 'active' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') AND co.created_at >= TIMESTAMP_TAG_LEFT and co.created_at < TIMESTAMP_TAG_RIGHT ;" cluster_scaling_finished_sql="SELECT t.country, t.id, t.name, t.alias, c.id, c.name, c.status,co.id,co.status FROM cluster_operations co join clusters c on co.cluster_id= c.id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.kind = 'external' and t.status = 'active' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') AND co.completed_at >= TIMESTAMP_TAG_LEFT and co.completed_at < TIMESTAMP_TAG_RIGHT;" cluster_scaling_longlast_sql="SELECT t.country, t.id, t.name, t.alias, c.id, c.name, c.status,co.id,co.status FROM cluster_operations co join clusters c on co.cluster_id= c.id JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.plan = '$plan_type' and t.kind = 'external' and t.status = 'active' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown', 'normal') and co.status = 'created' AND co.created_at < DATE_ADD(NOW(), INTERVAL - 3 hour);" # Daily tenant report daily_report_tenant_sql="select id,name,alias,email,country,status,plan,bind_credit_card_at ,kind,created_at,updated_at FROM tenants WHERE (not lower(name) like '%pingcap%' ) and plan in ( 'poc','on_demand') and status = 'active' and kind = 'external' ORDER BY plan;" old_ts="$(date -u '+%Y-%m-%d %T')" date="$(date +%D)" i="0" cat /dev/null > "$clusterid_cache" while true; do new_ts="$(date -u '+%Y-%m-%d %T')" printf %s '{"msg_type" : "interactive", "card": {"config":{"wide_screen_mode": true},' > $tmp_json_file printf %s '"header": {"title": {"tag": "plain_text",' >> $tmp_json_file printf %s "\"content\": \"$title\"" >> $tmp_json_file printf %s '}, ' >> $tmp_json_file printf %s "\"template\": \"$color\"" >> $tmp_json_file printf %s '},'>> $tmp_json_file printf %s '"elements": [{"tag": "markdown", "content":"HEAD_TAG'>> $tmp_json_file first_appeared="false" # backup echo $backup_start_sql| preprocess "$old_ts" "$new_ts"|$mysql_command| appendRegionInfo |awk -F "\t" 'NR!=1{printf "**[%s]** The %s backup (%s) [status: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) **started**.\\n", $1, $10, $8, $9, $5, $7, $3, $2}' > $temp_file_sql echo $backup_finished_sql| preprocess "$old_ts" "$new_ts" |$mysql_command| appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** The %s backup (%s) [status: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) **finished**.\\n", $1, $10, $8, $9, $5, $7, $3, $2}' >> $temp_file_sql echo $backup_longlast_sql| $mysql_command| appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** The %s backup (%s) [status: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) lasts more than **10h**, please take a look.\\n", $1, $10, $8, $9, $5, $7, $3, $2}' >> $temp_file_sql if [ -s "$temp_file_sql" ] then if [ "$first_appeared" == "true" ] then printf %s '\n --------------\n' >> $tmp_json_file else first_appeared="true" fi printf %s '**Backups:**\n' >> $tmp_json_file cat $temp_file_sql | sed 's/\\n/\n/g' | sort -s -k 5,5| sed 's/$/\\n\\n/g' | tr -d '\n'>> $tmp_json_file fi rm $temp_file_sql # restore echo $restore_start_sql| preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo|awk -F "\t" 'NR!=1{printf "**[%s]** The restore (%s) [status: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) **started**.\\n", $1,$8, $9, $5, $7, $3, $2}' > $temp_file_sql echo $restore_finished_sql| preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** The restore (%s) [status: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) **finished**.\\n", $1, $8, $9, $5, $7, $3, $2}' >> $temp_file_sql echo $restore_longlast_sql| $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** The restore (%s) [status: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) lasts more than **3h**, please take a look.\\n", $1, $8, $9, $5, $7, $3, $2}' >> $temp_file_sql if [ -s "$temp_file_sql" ] then if [ "$first_appeared" == "true" ] then printf %s '\n --------------\n' >> $tmp_json_file else first_appeared="true" fi printf %s '**Restores:**\n' >> $tmp_json_file cat $temp_file_sql | sed 's/\\n/\n/g' | sort -s -k 4,4| sed 's/$/\\n\\n/g' | tr -d '\n'>> $tmp_json_file fi rm $temp_file_sql # import echo $import_start_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** The import (%s) [status: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) **started**.\\n", $1, $8, $9, $5, $7, $3, $2}' > $temp_file_sql echo $import_finished_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** The import (%s) [status: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) **finished**.\\n", $1, $8, $9, $5, $7, $3, $2}' >> $temp_file_sql echo $import_longlast_sql | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** The import (%s) [status: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) lasts more than **3h**, please take a look.\\n", $1, $8, $9, $5, $7, $3, $2}' >> $temp_file_sql if [ -s "$temp_file_sql" ] then if [ "$first_appeared" == "true" ] then printf %s '\n --------------\n' >> $tmp_json_file else first_appeared="true" fi printf %s '**Imports:**\n' >> $tmp_json_file cat $temp_file_sql | sed 's/\\n/\n/g' | sort -s -k 4,4 | sed 's/$/\\n\\n/g' | tr -d '\n'>> $tmp_json_file fi rm $temp_file_sql #new user new_count="$(echo $user_count_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | awk 'NR==2')" if [[ "$new_count" -gt "$user_count" ]] then if [ "$scope" == 'ondemand' ] then echo $new_user_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** Bind Credit Card **[%s]** (%s).\\n", $1, $3, $2}' > $temp_file_sql else echo $new_user_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** New tenant **[%s]** (%s).\\n", $1, $3, $2}' > $temp_file_sql fi if [ -s "$temp_file_sql" ] then if [ "$first_appeared" == "true" ] then printf %s '\n --------------\n' >> $tmp_json_file else first_appeared="true" fi if [ "$scope" == 'ondemand' ] then printf %s '**Bind Credit Card:**\n' >> $tmp_json_file cat $temp_file_sql >> $tmp_json_file else printf %s '**New Tenants:**\n' >> $tmp_json_file cat $temp_file_sql >> $tmp_json_file fi fi rm $temp_file_sql elif [[ "$new_count" -eq "$user_count" ]] then echo "Nothing to do" else prepareAlertJson curl -XPOST -H "Content-Type: application/json" $alert_url -d @$alert_json fi user_count="$new_count" # cluster creation echo $cluster_creation_start_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | tee "$tmp_clusterid" | awk -F "\t" 'NR!=1{printf "**[%s]** Creation of cluster (%s) [status: %s] of tenant **[%s]** (%s) **started**.\\n", $1, $5, $7, $3, $2}' > $temp_file_sql awk -F "\t" 'NR!=1 {print $5}' "$tmp_clusterid">> "$clusterid_cache" #cat $clusterid_cache > ./clusterid_dump cat /dev/null > "$clusterid_to_remove" cat /dev/null > "$tmp_clusterid" while read cluster_id do echo $cluster_creation_finish_sql | sed "s/CLUSTERID_PLACEHOLDER/$cluster_id/" | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | tee "$tmp_clusterid" |awk -F "\t" 'NR!=1{printf "**[%s]** Creation of cluster (%s) [status: %s] of tenant **[%s]** (%s) **finished**.\\n", $1, $5, $7, $3, $2}' >> $temp_file_sql awk -F "\t" 'NR!=1 {print $5}' "$tmp_clusterid" >> "$clusterid_to_remove" done < "$clusterid_cache" #cat $clusterid_to_remove > ./clusterid_remove_dump while read cluster_id do cat /dev/null > "$swap_file" awk "\$1 != $cluster_id {print \$0}" "$clusterid_cache"> "$swap_file" rm "$clusterid_cache" mv "$swap_file" "$clusterid_cache" done < "$clusterid_to_remove" echo $cluster_creation_longlast_sql | $mysql_command | appendRegionInfo | tee ./afterappendregion_dump| awk -F "\t" 'NR!=1{printf "**[%s]** Creation of cluster (%s) [status: %s] of tenant **[%s]** (%s) lasts more than **30 min**, please take a look.\\n", $1, $5, $7, $3, $2}' >> $temp_file_sql if [ -s "$temp_file_sql" ] then if [ "$first_appeared" == "true" ] then printf %s '\n --------------\n' >> $tmp_json_file else first_appeared="true" fi printf %s '**Cluster creations:**\n' >> $tmp_json_file cat $temp_file_sql | sed 's/\\n/\n/g' | sort -s -k 5,5 | sed 's/$/\\n\\n/g' | tr -d '\n'>> $tmp_json_file fi rm $temp_file_sql # cluster scaling echo $cluster_scaling_start_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** Cluster (%s) [status: %s] of tenant **[%s]** (%s) **started** a scaling operation (%s) [status: **%s**].\\n", $1, $5, $7, $3, $2, $8, $9}' > $temp_file_sql echo $cluster_scaling_finished_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** Cluster (%s) [status: %s] of tenant **[%s]** (%s) **finished** a scaling operation (%s) [status: **%s**].\\n", $1, $5, $7, $3, $2, $8, $9}' >> $temp_file_sql echo $cluster_scaling_longlast_sql | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** Cluster (%s) [status: %s] of tenant **[%s]** (%s) has a scaling operation (%s) [status: %s] lasting more than **3h**, please take a look.\\n", $1, $5, $7, $3, $2, $8, $9}' >> $temp_file_sql if [ -s "$temp_file_sql" ] then if [ "$first_appeared" == "true" ] then printf %s '\n --------------\n' >> $tmp_json_file else first_appeared="true" fi printf %s '**Cluster scalings:**\n' >> $tmp_json_file cat $temp_file_sql | sed 's/\\n/\n/g' | sort -s -k 3,3 | sed 's/$/\\n\\n/g' | tr -d '\n'>> $tmp_json_file fi rm $temp_file_sql printf %s '"}]}}' >> $tmp_json_file if [ -n "$(grep failed $tmp_json_file)" ]; then sed 's/failed/❌failed❌/g' $tmp_json_file > $swap_file sed 's/HEAD_TAG/**❌FAILURE DETECTED❌**\\n/' $swap_file > $tmp_json_file else sed 's/HEAD_TAG//' $tmp_json_file > $swap_file cat $swap_file > $tmp_json_file fi cat $tmp_json_file curl -XPOST -H "Content-Type: application/json" $bot_url -d @$tmp_json_file old_ts=$new_ts i=$(($i + 1)) printf %s '{"msg_type":"text","content":{"text":' > $tmp_json_file printf %s "\"$scope loop:" >> $tmp_json_file printf %s "$i" >> $tmp_json_file printf %s ' timestamp: ' >> $tmp_json_file printf %s "$old_ts" >> $tmp_json_file printf %s '"}}' >> $tmp_json_file curl -XPOST -H "Content-Type: application/json" $daily_report_bot_url -d @$tmp_json_file #if [[ "$date" != "$(date +%D)" ]] #then # printf %s '{"msg_type" : "interactive", "card": {"config":{"wide_screen_mode": true},' > $tmp_json_file # printf %s "\"header\": {\"title\": {\"tag\": \"plain_text\", \"content\": \"$date Daily Tenant Report\"}, \"template\": \"green\"},">> $tmp_json_file # printf %s '"elements": [{"tag": "markdown", "content":"\n'>> $tmp_json_file # # echo $daily_report_tenant_sql | $mysql_command | awk -F "\t" 'NR!=1{printf "**id**: %s\\n**name:** %s\\n**alias:** %s\\n**email:** %s\\n**country:** %s\\n**status:** %s\\n**plan:** %s\\n**bind_credit_card_at:** %s\\n**kind:** %s\\n**created_at:** %s\\n**updated_at:** %s\\n\\n", $1, $2, $3, $4,$5,$6,$7,$8,$9,$10,$11}' | tr -d '\n' >> $tmp_json_file # # printf %s '"}]}}' >> $tmp_json_file # curl -XPOST -H "Content-Type: application/json" $daily_report_bot_url -d @$tmp_json_file # # date="$(date +%D)" #fi sleep $sleep_time done