#!/usr/bin/env bash set -e scope="test" 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" tmp_json_file="/tmp/resulofsomething.json" mysql_command="mysql -ustaging -htidb.e23bdccc.cb17aaf2.us-west-2.prod.aws.tidbcloud.com -P4000 -pZqfjTO5G_CJx%AdiqFuyVS6P staging_central" sleep_time="10" tmp_new_cluster_file="/tmp/tm_new_cluster_test" tmp_import_file="/tmp/tm_import_test" tmp_manual_backup_file="/tmp/tm_manual_backup_test" tmp_restore_file="/tmp/tm_restore_test" tmp_30day_cluster_file="/tmp/tm_30day_cluster_test" region_color_mapping="/tmp/region_color_mapping_test" daily_report_bot_url="$bot_url" 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" mysql_command="mysql -h metadb-tidb -P 4000 dbaas" sleep_time="900" tmp_new_tenant_file="/tmp/tm_new_tenant_poc" tmp_new_cluster_file="/tmp/tm_new_cluster_poc" tmp_import_file="/tmp/tm_import_poc" tmp_manual_backup_file="/tmp/tm_manual_backup_poc" tmp_30day_cluster_file="/tmp/tm_30day_cluster_poc" region_color_mapping="/tmp/region_color_mapping_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" mysql_command="mysql -h metadb-tidb -P 4000 dbaas" sleep_time="900" tmp_new_tenant_file="/tmp/tm_new_tenant_ondemand" tmp_new_cluster_file="/tmp/tm_new_cluster_ondemand" tmp_import_file="/tmp/tm_import_ondemand" tmp_manual_backup_file="/tmp/tm_manual_backup_ondemand" tmp_30day_cluster_file="/tmp/tm_30day_cluster_ondemand" region_color_mapping="/tmp/region_color_mapping_ondemand" elif [ $scope == "devtier" ] then bot_url="https://open.feishu.cn/open-apis/bot/v2/hook/b8571e57-a353-4b9c-937f-5195f297f29f" temp_file_sql="/tmp/temp_file_sql_dt" tmp_json_file="/tmp/resulofsomething_dt.json" mysql_command="mysql -h metadb-tidb -P 4000 dbaas" sleep_time="86400" tmp_new_cluster_file="/tmp/tm_new_cluster_devtier" tmp_import_file="/tmp/tm_import_devtier" tmp_manual_backup_file="/tmp/tm_manual_backup_devtier" tmp_restore_file="/tmp/tm_restore_devtier" region_color_mapping="/tmp/region_color_mapping_devtier" tmp_30day_cluster_file="/tmp/tm_30day_cluster_devtier" daily_report_bot_url="$bot_url" tester_url="https://open.feishu.cn/open-apis/bot/v2/hook/6aed0f4e-f7f1-4223-b5a3-d503e7100587" fi ondemand_bot_url="" daily_report_file="" countrycode_file="/tmp/fileforcountrycode_dt" cat > $region_color_mapping << EOF APAC carmine CO orange EMEA turquoise NA purple Japan violet EOF appendRegionInfo() { # 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 #head -n 5 $countrycode_file while IFS= read -r line do country=$(echo "$line" | cut -f1) 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() { while IFS= read -r line do echo "$line" | sed "s/TIMESTAMP_TAG_LEFT/'$1'/" | sed "s/TIMESTAMP_TAG_RIGHT/'$2'/" done } # Dev tier user metrics - 1 day per run # a shared cluster created during the time devtier_new_cluster_sql="SELECT t.country, t.id as tenant_id, t.name as tenant_name, t.alias as tenant_alias, c.id as cluster_id, c.name, c.status as cluster_statust, t.email FROM tenants t join clusters c on t.id = c.tenant_id WHERE (not lower(t.name) like '%pingcap%' ) and t.status = 'active' and t.kind = 'external' and c.deploy_type = 'shared' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and c.created_at >= TIMESTAMP_TAG_LEFT and c.created_at < TIMESTAMP_TAG_RIGHT;" # a shared cluster finish a import during the time, message if needed devtier_import_finished_sql="SELECT t.country, t.id as tenant_id, t.name as tenant_name, t.alias as tenant_alias, c.id as cluster_id,c.name as cluster_name, c.status as clusters_status,i.id as import_id,i.status as status,i.total_size as total_size,i.completed_tables as completed_tables, t.email 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.status = 'active' and t.kind = 'external' and c.deploy_type = 'shared' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and i.finished_at >= TIMESTAMP_TAG_LEFT and i.finished_at < TIMESTAMP_TAG_RIGHT;" # a shared cluster start a manual(because auto is too much) backup during the time, not all have finished time devtier_manual_backup_created_sql="SELECT t.country,t.id,t.name,t.alias,c.id,c.name,c.status,b.id,b.status,bs.type,b.backup_size,t.email 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.status = 'active' and t.kind = 'external' and c.deploy_type = 'shared' 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';" # a shared cluster start a restore during the time, not all have finished time devtier_restore_created_sql="SELECT t.country,t.id,t.name,t.alias,c.id,c.name,c.status,r.id,r.status,t.email 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.status = 'active' and t.kind = 'external' and c.deploy_type = 'shared' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and r.created_at >= TIMESTAMP_TAG_LEFT and r.created_at < TIMESTAMP_TAG_RIGHT;" # cluster not deleted and created before 30 days devtier_cluster_exist_30day_sql="SELECT t.country, t.id as tenant_id, t.name as tenant_name, t.alias as tenant_alias, c.id as cluster_id, c.name as cluster_name, c.status, t.email FROM tenants t join clusters c on t.id = c.tenant_id WHERE (not lower(t.name) like '%pingcap%' ) and t.status = 'active' and t.kind = 'external' and c.deploy_type = 'shared' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and c.created_at < date_add(now(),interval - 30 day) and c.created_at > date_add(now(),interval - 31 day);" # daily report for regions # daily new cluster devtier_daily_new_cluster_sql="SELECT t.country, c.id, c.status, t.name, t.id, t.email FROM clusters c JOIN tenants t ON c.tenant_id = t.id WHERE (not lower(t.name) like '%pingcap%' ) and t.kind = 'external' and t.status = 'active' and c.status in ('created','initializing','preparing','launching', 'normal') and c.deploy_type = 'shared' and date(c.updated_at) = 'DATE_TAG';" # daily import devtier_daily_import_sql="select t.country, c.id, c.status, t.name,t.id,t.email,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.status = 'active' and t.kind = 'external' and c.deploy_type = 'shared' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and date(i.created_at)='DATE_TAG';" # daily manual backup devtier_daily_manual_backup_sql="SELECT t.country, c.id, c.status, t.name,t.id,t.email,b.id,b.status 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.status = 'active' and t.kind = 'external' and c.deploy_type = 'shared' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and date(b.created_at) = 'DATE_TAG' and bs.type = 'manual';" # daily restore devtier_daily_restore_sql="select t.country, c.id, c.status, t.name, t.id, t.email,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.status = 'active' and t.kind = 'external' and c.deploy_type = 'shared' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and date(r.created_at)='DATE_TAG';" i="0" old_ts="$(date -u '+%Y-%m-%d %T')" if [[ "$scope" == "test" ]] then date="$(date -u '+%Y-%m-%d')" else date="$(date -d '-1 day' -u '+%Y-%m-%d')" fi first_loop="true" while true; do new_ts="$(date -u '+%Y-%m-%d %T')" first_appeared="false" # # dev tier bot # printf %s '{"msg_type" : "interactive", "card": {"config":{"wide_screen_mode": true},' > $tmp_json_file # printf %s '"header": {"title": {"tag": "plain_text", "content": "Dev tier User Behavior"}, "template": "green"},'>> $tmp_json_file # printf %s '"elements": [{"tag": "markdown", "content":"'>> $tmp_json_file # echo $devtier_new_cluster_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "(1) **[%s]** **New** dev tier cluster **[%s]**(%s) was created by tenant **[%s]**(%s), email: %s.\\n", $1, $5, $6, $2, $3, $8}' > $temp_file_sql # echo $devtier_import_finished_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "(2) **[%s]** **Data Import** (%s) [status: **%s**, completed_tables: %s, total_size: %s] of cluster **[%s]**(%s) was done by tenant **[%s]** (%s), email: %s.\\n", $1, $8, $9, $11, $10, $5, $6, $2, $3, $12}' >> $temp_file_sql # echo $devtier_manual_backup_created_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "(3) **[%s]** **Manual Backup** (%s) [status: **%s**, backup_size: %s] of cluster **[%s]**(%s) was triggered by tenant **[%s]** (%s), email: %s.\\n", $1, $8, $9, $11, $5, $6, $2, $3, $12}' >> $temp_file_sql # echo $devtier_restore_created_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "(4) **[%s]** **Restore** (%s) [status: **%s**] of cluster **[%s]**(%s) was triggered by tenant **[%s]** (%s), email: %s.\\n", $1, $8, $9, $5, $6, $2, $3, $10}' >> $temp_file_sql # echo $devtier_cluster_exist_30day_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "(5) **[%s]** **30 days active** cluster **[%s]**(%s) was created by tenant **[%s]**(%s), email: %s.\\n", $1, $5, $6, $2, $3, $8}' >> $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 '**7 days updates:**\n' >> $tmp_json_file # cat $temp_file_sql | sed 's/\\n/\\n\\n/g' >> $tmp_json_file # fi # # rm $temp_file_sql # printf %s '"}]}}' >> $tmp_json_file # 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":"dev tier 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 -u '+%Y-%m-%d')" || "$first_loop" == "true" || "$scope" == "test" ]] then #echo $devtier_daily_new_tenant_sql | sed "s/DATE_TAG/$date/" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf("**[%s]** New tenant **[%s]**[id:**(%s)**, email:**(%s)**] signed up\n", $1, $2, $3, $4) }' > $tmp_new_tenant_file echo $devtier_daily_new_cluster_sql | sed "s/DATE_TAG/$date/" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf("[%s] Tenant **[%s]**(id:**[%s]**, email:**[%s]**) created a shared cluster\n", $1, $4, $5, $6)}' > $tmp_new_cluster_file echo $devtier_daily_import_sql | sed "s/DATE_TAG/$date/" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf("[%s] Tenant **[%s]**(id:**[%s]**, email:**[%s]**) had an import\n", $1, $4, $5, $6)}' > $tmp_import_file echo $devtier_daily_manual_backup_sql | sed "s/DATE_TAG/$date/" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf("[%s] Tenant **[%s]**(id:**[%s]**, email:**[%s]**) had an manual backup\n", $1, $4, $5, $6)}' > $tmp_manual_backup_file echo $devtier_daily_restore_sql | sed "s/DATE_TAG/$date/" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf("[%s] Tenant **[%s]**(id:**[%s]**, email:**[%s]**) had a restore\n", $1, $4, $5, $6)}' > $tmp_restore_file echo $devtier_cluster_exist_30day_sql | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf("[%s] Tenant **[%s]**(id:**[%s]**, email:**[%s]**) had a 30-days active cluster\n", $1, $3, $2, $8)}' > $tmp_30day_cluster_file for region in "APAC" "CO" "EMEA" "NA" "Japan" do color="$(grep $region $region_color_mapping | cut -d ' ' -f 2)" echo "region = $region, color = $color" printf %s '{"msg_type" : "interactive", "card": {"config":{"wide_screen_mode": true},' > $tmp_json_file printf %s "\"header\": {\"title\": {\"tag\": \"plain_text\", \"content\": \"$date Dev-tier Daily Report For $region\"}, \"template\": \"$color\"},">> $tmp_json_file printf %s '"elements": [{"tag": "markdown", "content":"'>> $tmp_json_file printf %s '**Bronze Users** (New-try):\n' >> $tmp_json_file cat $tmp_new_cluster_file | grep "$region-" | sed "s/$region-//" | sed 's/$/\\n/g' | tr -d '\n' >> $tmp_json_file printf %s '\n --------------\n**Silver Users** (Deeper-try):\n' >> $tmp_json_file cat $tmp_import_file | grep "$region-" | sed "s/$region-//" | sed 's/$/\\n/g' | tr -d '\n' >> $tmp_json_file cat $tmp_manual_backup_file | grep "$region-" | sed "s/$region-//" | sed 's/$/\\n/g' | tr -d '\n' >> $tmp_json_file cat $tmp_restore_file | grep "$region-" | sed "s/$region-//" | sed 's/$/\\n/g' | tr -d '\n' >> $tmp_json_file printf %s '\n --------------\n**Gold Users** (Heavily-used):\n' >> $tmp_json_file cat $tmp_30day_cluster_file | grep "$region-" | sed "s/$region-//" | sed 's/$/\\n/g' | tr -d '\n' >> $tmp_json_file printf %s '"}]}}' >> $tmp_json_file if [[ "$first_loop" == "true" ]] then curl -XPOST -H "Content-Type: application/json" $daily_report_bot_url -d @$tmp_json_file else curl -XPOST -H "Content-Type: application/json" $daily_report_bot_url -d @$tmp_json_file fi done if [[ "$scope" != "test" && "$first_loop" != "true" ]] then date="$(date -u '+%Y-%m-%d')" fi fi if [[ "$scope" == "devtier" && "$first_loop" == "true" ]] then first_loop="false" sleep "$(( (24 - $(date -u '+%H') ) * 3600 ))" else sleep $sleep_time fi done