gcp_cloud_status_dataset
1.0.0
這是一個簡單的 Bigquery 資料集，其中包含 Google Cloud Service Health (CSH) 事件。
您可以使用它來查詢事件並過濾您感興趣的事件。
每分鐘都會觸發一次。如果現有中斷沒有更新或未偵測到新中斷，則不會插入新行。
您也可以將其與資產清單 API 結合使用，將給定位置/區域中的事件與可能受影響的資產關聯起來。
不管怎樣，現有的CSH儀表板提供了各種格式的數據，例如RSS和JSON ，同時您可以使用jq發出簡單的查詢和過濾器
curl -s https://status.cloud.google.com/incidents.json | jq -r ' .[] | select(.service_name == "Google Compute Engine") '然而，它只是不易於使用。
因此，您或任何人都可以在 bigquery 表中使用而不是原始 json...
這是數據集：
若要使用此功能，請先將下列項目新增至 UI gcp-status-log 。完成後，您發出的任何查詢都將使用此資料集，但會根據您自己的使用情況對您的專案進行計費。 （即，我只是提供資料......您運行的查詢就是您將支付的費用）
注意：Google不支援此儲存庫、資料集和程式碼。買者自負
bq query --nouse_legacy_sql '
SELECT
DISTINCT(id), service_name,severity,external_desc, begin,`end` , modified
FROM
gcp-status-log.status_dataset.status
WHERE
service_name = "Google Compute Engine"
ORDER BY
modified
'
+----------------------+-----------------------+----------+-----------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------------------+
| id | service_name | severity | external_desc | begin | end | modified |
+----------------------+-----------------------+----------+-----------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------------------+
| pxD6QciVMd9gLQGcREbV | Google Compute Engine | medium | Requests to and from Google Compute Instances in us-west2 may see increase traffic loss when using the instance's public IP | 2021-05-05 02:11:10 | 2021-05-05 04:54:57 | 2021-05-05 04:54:57 |
| LGFBxyLwbh92E47fAzJ5 | Google Compute Engine | medium | Mutliregional Price for E2 Free Tier core is set incorrectly | 2021-08-01 07:00:00 | 2021-08-04 23:18:00 | 2021-08-05 17:35:12 |
| gwKjX9Lukav15SaFPbBF | Google Compute Engine | medium | us-central1, europe-west1, us-west1, asia-east1: Issue with Local SSDs on Google Compute Engine. | 2021-09-01 02:35:00 | 2021-09-03 03:55:00 | 2021-09-07 21:39:46 |
| rjF86FbooET3FDpMV9w1 | Google Compute Engine | medium | Increased VM failure rates in a subset of Google Cloud zones | 2021-09-17 15:00:00 | 2021-09-17 18:25:00 | 2021-09-20 23:33:53 |
| ZoUf49v2qbJ9xRK63kaM | Google Compute Engine | medium | Some Users might have received credit cards deemed invalid email erroneously. | 2021-11-13 07:14:48 | 2021-11-13 08:29:30 | 2021-11-13 08:29:30 |
| SjJ3FN51MAEJy7cZmoss | Google Compute Engine | medium | Global: pubsub.googleapis.com autoscaling not worked as expected | 2021-12-07 09:56:00 | 2021-12-14 00:59:00 | 2021-12-14 19:59:08 |
+----------------------+-----------------------+----------+-----------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------------------+
……這就是我 BQ 技能的極限。如果您有任何建議的查詢，請在 github issues 中給我留言
此處使用的架構與 Incidents.schema.json 中顯示的儀表板的 JSON 輸出提供的格式幾乎相同。
唯一的區別是 BQ 中的每一行都是單獨的事件，而不是根據上面提供的架構將所有事件封裝到單一 JSON 中。
此外，該架構還有兩個新欄位：
insert_timestamp ：這是插入行/事件時的TIMESTAMPsnapshot_hash ：這是下載的incident.json檔案的 Base64 編碼雜湊值。您可以在此儲存庫中查看範例架構。
bq show --format=prettyjson --schema gcp-status-log:status_dataset.status
對於其他人，您可以使用以下組合自行設定整個事情
Cloud Scheduler -> Cloud Run -> BigQUery下面的設定是：
Cloud Scheduler每分鐘都會安全地呼叫Cloud Run服務Cloud Run從 GCS 儲存桶下載文件，該儲存桶保存上次插入的JSON CSH 檔案的雜湊值Cloud Run下載並解析 JSON CSH 數據Cloud Run將 CSH 事件插入BigQuery當然，如果沒有更新，此方案取決於 JSON CSH 檔案保留相同的雜湊值（例如，它不包含其自身更新的新鮮度時間戳記）
export PROJECT_ID= ` gcloud config get-value core/project `
export PROJECT_NUMBER= ` gcloud projects describe $PROJECT_ID --format= ' value(projectNumber) ' `
gcloud services enable containerregistry.googleapis.com
run.googleapis.com
bigquery.googleapis.com
cloudscheduler.googleapis.com
storage.googleapis.com
# # create the datasets. We are using DAY partitioning
bq mk -d --data_location=US status_dataset
bq mk --table status_dataset.status schema.json
# # create service accounts for cloud run and scheduler
gcloud iam service-accounts create schedulerunner --project= $PROJECT_ID
gcloud iam service-accounts create cloudrunsvc --project= $PROJECT_ID
bq add-iam-policy-binding
--member=serviceAccount:cloudrunsvc@ $PROJECT_ID .iam.gserviceaccount.com
--role=roles/bigquery.admin status_dataset.status
gcloud projects add-iam-policy-binding $PROJECT_ID
--member= " serviceAccount:cloudrunsvc@ $PROJECT_ID .iam.gserviceaccount.com "
--role= " roles/bigquery.jobUser "
# create a gcs bucket to store hash of the incidents json file
# the first value of the hash will force a reload of the incidents.json file
gsutil mb -l us-central1 gs:// $PROJECT_ID -status-hash
echo -n " foo " > /tmp/hash.txt
gsutil cp /tmp/hash.txt gs:// $PROJECT_ID -status-hash/
gsutil iam ch serviceAccount:cloudrunsvc@ $PROJECT_ID .iam.gserviceaccount.com:roles/storage.admin gs:// $PROJECT_ID -status-hash/
# # you may also need to allow your users access to the dataset https://cloud.google.com/bigquery/docs/dataset-access-controls
# # build and deploy the cloud run image
docker build -t gcr.io/ $PROJECT_ID /gstatus .
docker push gcr.io/ $PROJECT_ID /gstatus
gcloud run deploy gcp-status --image gcr.io/ $PROJECT_ID /gstatus
--service-account cloudrunsvc@ $PROJECT_ID .iam.gserviceaccount.com
--set-env-vars " BQ_PROJECTID= $PROJECT_ID " --no-allow-unauthenticated
export RUN_URL= ` gcloud run services describe gcp-status --region=us-central1 --format= " value(status.address.url) " `
# # allow cloud scheduler to call cloud run
gcloud run services add-iam-policy-binding gcp-status --region=us-central1
--member=serviceAccount:schedulerunner@ $PROJECT_ID .iam.gserviceaccount.com --role=roles/run.invoker
# # deploy cloud scheduler
gcloud scheduler jobs create http status-scheduler- $region --http-method=GET --schedule " */5 * * * * "
--attempt-deadline=420s --time-zone= " Pacific/Tahiti " --location=us-central1
--oidc-service-account-email=schedulerunner@ $PROJECT_ID .iam.gserviceaccount.com
--oidc-token-audience= $RUN_URL --uri= $RUN_URL[等待5分鐘]
您也可以將 bq 事件與資產庫存資料結合起來，以協助縮小事件是否影響您的服務的範圍。
例如，如果您知道us-central1-a中存在影響 GCE 實例的事件，您可以發出限制潛在資產清單的搜尋查詢：
$ gcloud organizations list
DISPLAY_NAME ID DIRECTORY_CUSTOMER_ID
esodemoapp2.com 673202286123 C023zwabc
$ gcloud asset search-all-resources --scope= ' organizations/673202286123 '
--query= " location:us-central1-a "
--asset-types= " compute.googleapis.com/Instance " --format= " value(name) "
//compute.googleapis.com/projects/in-perimeter-gcs/zones/us-central1-a/instances/in-perimeter
//compute.googleapis.com/projects/ingress-vpcsc/zones/us-central1-a/instances/ingress
//compute.googleapis.com/projects/fabled-ray-104117/zones/us-central1-a/instances/instance-1
//compute.googleapis.com/projects/fabled-ray-104117/zones/us-central1-a/instances/nginx-vm-1
//compute.googleapis.com/projects/clamav-241815/zones/us-central1-a/instances/instance-1
//compute.googleapis.com/projects/fabled-ray-104117/zones/us-central1-a/instances/windows-1
您也可以使用以下方式查詢世界各地的 IAM 角色和權限：
來源事件是 JSON，因此您也可以使用 BQ 對 JSON 資料類型的本機支援將每個事件載入到 BQ 中。
這可能是一個 TODO 和一個範例工作流程可能是這樣的：
export PROJECT_ID= ` gcloud config get-value core/project `
export PROJECT_NUMBER= ` gcloud projects describe $PROJECT_ID --format= ' value(projectNumber) ' `
bq mk --table status_dataset.json_dataset events:JSON
curl -o incidents.json -s https://status.cloud.google.com/incidents.json
cat incidents.json | jq -c ' .[] | . ' | sed ' s/"/""/g ' | awk ' { print """$0"""} ' - > items.json
bq load --source_format=CSV status_dataset.json_dataset items.json
bq show status_dataset.json_dataset
$ bq show status_dataset.json_dataset
Last modified Schema Total Rows Total Bytes Expiration Time Partitioning Clustered Fields Labels
----------------- ----------------- ------------ ------------- ------------ ------------------- ------------------ --------
08 Apr 09:39:48 | - events: json 125 822184 然後查詢時可以直接引用各個filed：
$ bq query --nouse_legacy_sql '
SELECT events["id"] as id, events["number"] as number, events["begin"] as begin
FROM `status_dataset.json_dataset`
LIMIT 10
'
+------------------------+------------------------+-----------------------------+
| id | number | begin |
+------------------------+------------------------+-----------------------------+
| " ukkfXQc8CEeFZbSTYQi7 " | " 14166479295409213890 " | " 2022-03-31T19:15:00+00:00 " |
| " RmPhfQT9RDGwWLCXS2sC " | " 3617221773064871579 " | " 2022-03-31T18:07:00+00:00 " |
| " B1hD4KAtcxiyAWkcANfV " | " 17742360388109155603 " | " 2022-03-31T15:30:00+00:00 " |
| " 4rRjbE16mteQwUeXPZwi " | " 8134027662519725646 " | " 2022-03-29T21:00:00+00:00 " |
| " 2j8xsJMSyDhmgfJriGeR " | " 5259740469836333814 " | " 2022-03-28T22:30:00+00:00 " |
| " MtMwhU6SXrpBeg5peXqY " | " 17330021626924647123 " | " 2022-03-25T07:00:00+00:00 " |
| " R9vAbtGnhzo6n48SnqTj " | " 2948654908633925955 " | " 2022-03-22T22:30:00+00:00 " |
| " aA3kbJm5nwvVTKnYbrWM " | " 551739384385711524 " | " 2022-03-18T22:20:00+00:00 " |
| " LuGcJVjNTeC5Sb9pSJ9o " | " 5384612291846020564 " | " 2022-03-08T18:07:00+00:00 " |
| " Hko5cWSXxGSsxfiSpg4n " | " 6491961050454270833 " | " 2022-02-22T05:45:00+00:00 " |
+------------------------+------------------------+-----------------------------+
對 Cloud Run 的相應修改將涉及建立 CSV 格式的載入（自4/8/22起，支援 CSV 舊版載入器）
var rlines [] string
for _ , event := range events {
event . InsertTimestamp = now
event . SnapshotHash = sha256Value
strEvent , err := json . Marshal ( event )
if err != nil {
fmt . Printf ( "Error Marshal Event %v" , err )
http . Error ( w , err . Error (), http . StatusInternalServerError )
return
}
// for JSON Datatype
// https://cloud.google.com/bigquery/docs/reference/standard-sql/json-data
line := strings . Replace ( string ( strEvent ), " " " , " " " " , - 1 )
line = fmt . Sprintf ( " " %s " " , line )
rlines = append ( rlines , line )
}
dataString := strings . Join ( rlines , " n " )
rolesSource := bigquery . NewReaderSource ( strings . NewReader ( dataString ))
rolesSource . SourceFormat = bigquery . CSV無論如何，JSON 資料類型只是一個 TODO，我不確定目前是否有必要
為什麼我再次選擇大溪地時間作為調度程序？
為什麼不呢，你自己看看：
