Halaman ini diterjemahkan oleh Cloud Translation API.

Contoh kueri SQL

Dokumen ini berisi contoh kueri atas entri log yang disimpan di bucket log yang diupgrade untuk menggunakan Log Analytics. Di bucket ini, Anda dapat menjalankan kueri SQL dari halaman Log Analytics di Konsol Google Cloud. Untuk contoh lainnya, lihat logging-analytics-samples dan repositori GitHub security-analytics.

Dokumen ini tidak menjelaskan SQL atau cara merutekan dan menyimpan entri log. Untuk mengetahui informasi tentang topik tersebut, lihat bagian Langkah berikutnya.

Sebelum memulai

Untuk menggunakan kueri yang ditampilkan dalam dokumen ini di halaman Log Analytics, ganti TABLE dengan nama tabel yang sesuai dengan tampilan yang ingin Anda kueri. Nama tabel memiliki format project_ID.region.bucket_ID.view_ID. Anda dapat menemukan nama tabel untuk tampilan di halaman Log Analytics; kueri default untuk tampilan log mencantumkan nama tabel dalam pernyataan FROM. Untuk mengetahui informasi tentang cara mengakses kueri default, lihat Membuat kueri tampilan log.
Untuk menggunakan kueri yang ditampilkan dalam dokumen ini di halaman BigQuery Studio, ganti TABLE dengan jalur ke tabel di set data yang ditautkan. Misalnya, untuk membuat kueri tampilan _AllLogs di set data tertaut mydataset yang ada di project myproject, tetapkan kolom ini ke myproject.mydataset._AllLogs:

Di panel navigasi konsol Google Cloud, pilih BigQuery:
Buka BigQuery
Untuk membuka halaman Log Analytics, lakukan tindakan berikut:
1. Di panel navigasi konsol Google Cloud, pilih Logging, lalu pilih Log Analytics:
  Buka Log Analytics
2. Opsional: Untuk mengidentifikasi skema tabel bagi tampilan log, dalam daftar Log views, temukan tampilan, lalu pilih nama tampilan.
Skema untuk tabel akan ditampilkan. Anda dapat menggunakan kolom Filter untuk menemukan kolom tertentu. Anda tidak dapat mengubah skema.

Memfiter log

Kueri SQL menentukan baris di tabel yang akan diproses, lalu mengelompokkan baris dan melakukan operasi agregat. Jika tidak ada operasi pengelompokan dan agregasi yang dicantumkan, hasil kueri akan menyertakan baris yang dipilih oleh operasi filter. Contoh di bagian ini menggambarkan pemfilteran.

Filter menurut waktu

Untuk menetapkan rentang waktu kueri, sebaiknya gunakan pemilih rentang waktu. Pemilih ini digunakan secara otomatis saat kueri tidak menentukan kolom timestamp dalam klausa WHERE. Misalnya, untuk melihat data selama seminggu terakhir, pilih 7 hari terakhir dari pemilih rentang waktu. Anda juga dapat menggunakan pemilih rentang waktu untuk menentukan waktu mulai dan berakhir, menentukan waktu melihat sekitar, dan mengubah zona waktu.

Jika Anda menyertakan kolom timestamp dalam klausa WHERE, setelan pemilih rentang waktu tidak akan digunakan. Contoh berikut memfilter data menggunakan fungsi TIMESTAMP_SUB, yang memungkinkan Anda menentukan interval lihat balik dari waktu saat ini:

WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

Untuk mengetahui informasi selengkapnya tentang cara memfilter berdasarkan waktu, lihat Fungsi waktu dan Fungsi stempel waktu.

Filter menurut referensi

Untuk memfilter menurut resource, tambahkan batasan resource.type.

Misalnya, kueri berikut membaca data jam terakhir, lalu mempertahankan baris yang jenis resource-nya cocok dengan gce_instance, lalu mengurutkan dan menampilkan hingga 100 entri:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  resource.type = "gce_instance"
ORDER BY timestamp ASC
LIMIT 100

Filter menurut tingkat keseriusan

Anda dapat memfilter menurut tingkat keparahan tertentu dengan pembatasan seperti severity = 'ERROR'. Opsi lainnya adalah menggunakan pernyataan IN dan menentukan kumpulan nilai yang valid.

Misalnya, kueri berikut membaca data dari jam terakhir, lalu hanya mempertahankan baris yang berisi kolom severity yang nilainya adalah 'INFO' atau 'ERROR':

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  severity IS NOT NULL AND
  severity IN ('INFO', 'ERROR')
ORDER BY timestamp ASC
LIMIT 100

Kueri sebelumnya memfilter menurut nilai kolom severity. Namun, Anda juga dapat menulis kueri yang memfilter berdasarkan nilai numerik keparahan log. Misalnya, jika Anda mengganti baris severity dengan baris berikut, kueri akan menampilkan semua entri log yang tingkat keparahannya minimal NOTICE:

  severity_number IS NOT NULL AND
  severity_number > 200

Untuk mengetahui informasi tentang nilai yang dienumerasi, lihat LogSeverity.

Filter menurut nama log

Untuk memfilter berdasarkan nama log, Anda dapat menambahkan batasan pada nilai kolom log_name atau log_id. Kolom log_name menyertakan jalur resource. Artinya, kolom ini memiliki nilai seperti projects/myproject/logs/mylog. Kolom log_id hanya menyimpan nama log seperti mylog.

Misalnya, kueri berikut membaca data dari jam terakhir, lalu mempertahankan baris tersebut dengan nilai dalam kolom log_id adalah cloudaudit.googleapis.com/data_access, lalu mengurutkan dan menampilkan hasilnya:

SELECT
  timestamp, log_id, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  log_id = "cloudaudit.googleapis.com/data_access"
ORDER BY timestamp ASC
LIMIT 100

Filter menurut label resource

Sebagian besar deskripsi resource yang dimonitor menentukan label yang digunakan untuk mengidentifikasi resource tertentu. Misalnya, deskripsi untuk instance Compute Engine menyertakan label untuk zona, project ID, dan ID instance. Saat entri log ditulis, nilai ditetapkan ke setiap kolom. Berikut adalah contohnya:

{
   type: "gce_instance"
   labels: {
      instance_id: "1234512345123451"
      project_id: "my-project"
      zone: "us-central1-f"
   }
}

Karena jenis data kolom labels adalah JSON, menyertakan batasan seperti resource.labels.zone = "us-centra1-f" dalam kueri akan menghasilkan error sintaksis. Untuk mendapatkan nilai kolom dengan jenis data JSON, gunakan fungsi JSON_VALUE.

Misalnya, kueri berikut membaca data terbaru, lalu mempertahankan baris yang resource-nya adalah instance Compute Engine yang terletak di zona us-central1-f:

SELECT
  timestamp, log_name, severity, JSON_VALUE(resource.labels.zone) AS zone, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  resource.type = "gce_instance" AND
  JSON_VALUE(resource.labels.zone) = "us-central1-f"
ORDER BY timestamp ASC
LIMIT 100

Untuk mengetahui informasi tentang semua fungsi yang dapat mengambil dan mengubah data JSON, lihat Fungsi JSON.

Filter menurut permintaan HTTP

Untuk memfilter tabel agar hanya menyertakan baris yang sesuai dengan permintaan atau balasan HTTP, tambahkan batasan http_request IS NOT NULL:

SELECT
  timestamp, log_name, severity, http_request, resource, labels
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL
ORDER BY timestamp
LIMIT 100

Kueri berikut hanya menyertakan baris yang sesuai dengan permintaan GET atau POST:

SELECT
  timestamp, log_name, severity, http_request, resource, labels
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL AND
  http_request.request_method IN ('GET', 'POST')
ORDER BY timestamp ASC
LIMIT 100

Filter berdasarkan status HTTP

Untuk memfilter berdasarkan status HTTP, ubah klausa WHERE agar kolom http_request.status ditentukan:

SELECT
  timestamp, log_name, http_request.status, http_request, resource, labels
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL AND
  http_request.status IS NOT NULL
ORDER BY timestamp ASC
LIMIT 100

Untuk menentukan jenis data yang disimpan dalam kolom, lihat skema atau tampilkan kolom tersebut. Hasil kueri sebelumnya menunjukkan bahwa kolom http_request.status menyimpan nilai bilangan bulat.

Memfilter menurut kolom dengan jenis JSON

Untuk mengekstrak nilai dari kolom yang jenis datanya adalah JSON, gunakan fungsi JSON_VALUE.

Pikirkan kueri berikut ini:

SELECT
  json_payload
FROM
  `TABLE`
WHERE
  json_payload.status IS NOT NULL

dan

SELECT
  json_payload
FROM
  `TABLE`
WHERE
  JSON_VALUE(json_payload.status) IS NOT NULL

Kueri sebelumnya menguji nilai kolom json_payload; isi kolom ini ditentukan oleh konten entri log. Kedua kueri akan menghapus baris yang tidak berisi kolom berlabel json_payload. Perbedaan antara kedua kueri ini adalah baris terakhir, yang menentukan apa yang diuji terhadap NULL. Sekarang, pikirkan sebuah tabel yang memiliki dua baris. Dalam satu baris, kolom json_payload memiliki bentuk berikut:

{
    status: {
        measureTime: "1661517845"
    }
}

Di baris lainnya, kolom json_payload memiliki struktur yang berbeda:

{
    @type: "type.googleapis.com/google.cloud.scheduler.logging.AttemptFinished"
    jobName: "projects/my-project/locations/us-central1/jobs/test1"
    relativeUrl: "/food=cake"
    status: "NOT_FOUND"
    targetType: "APP_ENGINE_HTTP"
}

Kedua baris sebelumnya memenuhi batasan json_payload.status IS NOT NULL. Artinya, hasil kueri menyertakan kedua baris tersebut. Namun, jika batasannya adalah JSON_VALUE(json_payload.status) IS NOT NULL, hanya baris kedua yang akan disertakan dalam hasil.

Filter menurut ekspresi reguler

Untuk menampilkan substring yang cocok dengan ekspresi reguler, gunakan fungsi REGEXP_EXTRACT. Jenis nilai yang ditampilkan dari fungsi ini adalah STRING atau BYTES.

Kueri berikut menampilkan entri log terbaru yang diterima, menyimpan entri tersebut dengan kolom json_payload.jobName, lalu menampilkan bagian nama yang dimulai dengan test:

SELECT
  timestamp, REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
FROM
  `TABLE`
WHERE
  json_payload.jobName IS NOT NULL
ORDER BY timestamp DESC
LIMIT 20

Untuk contoh tambahan, lihat dokumentasi REGEXP_EXTRACT. Untuk contoh ekspresi reguler lain yang dapat Anda gunakan, lihat Fungsi, operator, dan kondisional.

Kueri yang ditampilkan dalam contoh ini tidak efisien. Untuk pencocokan substring, seperti yang diilustrasikan, gunakan fungsi CONTAINS_SUBSTR.

Mengelompokkan dan menggabungkan entri log

Bagian ini dibuat berdasarkan contoh sebelumnya dan menggambarkan cara mengelompokkan dan menggabungkan baris tabel. Jika Anda tidak menentukan pengelompokan, tetapi menentukan agregasi, satu hasil akan dicetak karena SQL memperlakukan semua baris yang memenuhi klausa WHERE sebagai satu grup.

Setiap ekspresi SELECT harus disertakan dalam kolom grup atau digabungkan.

Kelompokkan menurut waktu

Untuk mengelompokkan data berdasarkan waktu, gunakan fungsi TIMESTAMP_TRUNC, yang akan memotong stempel waktu ke tingkat perincian yang ditentukan seperti MINUTE. Misalnya, stempel waktu 15:30:11, yang diformat sebagai hours:minutes:seconds, menjadi 15:30:00 saat perincian ditetapkan ke MINUTE.

Kueri berikut membaca data yang diterima dalam interval yang ditentukan oleh alat pilih rentang waktu, lalu mempertahankan baris tersebut yang nilai kolom json_payload.status bukan NULL. Kueri memotong stempel waktu di setiap baris berdasarkan jam, lalu mengelompokkan baris berdasarkan stempel waktu dan status yang terpotong:

SELECT
  TIMESTAMP_TRUNC(timestamp, HOUR) AS hour,
  JSON_VALUE(json_payload.status) AS status,
  COUNT(*) AS count
FROM
  `TABLE`
WHERE
  json_payload IS NOT NULL AND
  JSON_VALUE(json_payload.status) IS NOT NULL
GROUP BY hour,status
ORDER BY hour ASC

Untuk contoh tambahan, lihat dokumentasi TIMESTAMP_TRUNC. Untuk mengetahui informasi tentang fungsi berbasis waktu lainnya, lihat Fungsi tanggal dan waktu.

Kelompokkan menurut referensi

Kueri berikut membaca data jam terakhir, lalu mengelompokkan baris berdasarkan jenis resource. {i>Function<i} ini kemudian menghitung jumlah baris untuk setiap jenis, dan menghasilkan tabel dengan dua kolom. Kolom pertama mencantumkan jenis resource, sedangkan kolom kedua berisi jumlah baris untuk jenis resource tersebut:

SELECT
   resource.type, COUNT(*) AS count
FROM
  `TABLE`
GROUP BY resource.type
LIMIT 100

Kelompokkan menurut tingkat keparahan

Kueri berikut membaca data dari jam terakhir, lalu mempertahankan baris yang memiliki kolom tingkat keparahan. Kueri kemudian mengelompokkan baris berdasarkan tingkat keparahan dan menghitung jumlah baris untuk setiap grup:

SELECT
  severity, COUNT(*) AS count
FROM
  `TABLE`
WHERE
  severity IS NOT NULL
GROUP BY severity
ORDER BY severity
LIMIT 100

Kelompokkan menurut `log_id`

Hasil dari kueri berikut adalah tabel dengan dua kolom. Kolom pertama mencantumkan nama log, sedangkan kolom kedua mencantumkan jumlah entri log yang ditulis ke log tersebut dalam satu jam terakhir. Kueri mengurutkan hasil menurut jumlah entri:

SELECT
  log_id, COUNT(*) AS count
FROM
  `TABLE`
GROUP BY log_id
ORDER BY count DESC
LIMIT 100

Menghitung latensi rata-rata untuk permintaan HTTP

Kueri berikut mengilustrasikan pengelompokan menurut beberapa kolom, dan menghitung nilai rata-rata. Kueri mengelompokkan baris menurut URL yang terdapat dalam permintaan HTTP dan menurut nilai kolom labels.checker_location. Setelah mengelompokkan baris, kueri menghitung latensi rata-rata untuk setiap grup:

SELECT
  JSON_VALUE(labels.checker_location) AS location,
  AVG(http_request.latency.seconds) AS secs, http_request.request_url
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL AND
  http_request.request_method IN ('GET')
GROUP BY http_request.request_url, location
ORDER BY location
LIMIT 100

Pada ekspresi sebelumnya, JSON_VALUE diperlukan untuk mengekstrak nilai kolom labels.checker_location karena jenis data untuk labels adalah JSON. Namun, Anda tidak menggunakan fungsi ini untuk mengekstrak nilai dari kolom http_request.latency.seconds. Kolom kedua berisi jenis data bilangan bulat.

Hitung byte rata-rata yang dikirim untuk pengujian subnetwork

Kueri berikut mengilustrasikan cara menampilkan jumlah rata-rata byte yang dikirim oleh lokasi.

Kueri membaca data satu jam terakhir, lalu hanya mempertahankan baris dengan kolom jenis resource gce_subnetwork dan kolom json_payload yang bukan NULL. Selanjutnya, kueri mengelompokkan baris berdasarkan lokasi resource. Berbeda dengan contoh sebelumnya yang menyimpan data sebagai nilai numerik, nilai kolom bytes_sent adalah string sehingga Anda harus mengonversi nilai tersebut menjadi FLOAT64 sebelum menghitung rata-rata:

SELECT JSON_VALUE(resource.labels.location) AS location,
   AVG(CAST(JSON_VALUE(json_payload.bytes_sent) AS FLOAT64)) AS bytes
FROM
  `TABLE`
WHERE
  resource.type = "gce_subnetwork" AND
  json_payload IS NOT NULL
GROUP BY location
LIMIT 100

Hasil kueri sebelumnya adalah tabel yang setiap barisnya mencantumkan lokasi dan byte rata-rata yang dikirim untuk lokasi tersebut.

Untuk mengetahui informasi tentang semua fungsi yang dapat mengambil dan mengubah data JSON, lihat Fungsi JSON.

Untuk informasi tentang CAST dan fungsi konversi lainnya, lihat Fungsi konversi.

Menghitung entri log dengan kolom yang cocok dengan pola

Untuk menampilkan substring yang cocok dengan ekspresi reguler, gunakan fungsi REGEXP_EXTRACT. Jenis nilai yang ditampilkan dari fungsi ini adalah STRING atau BYTES.

Kueri berikut mempertahankan entri log yang nilai kolom json_payload.jobName-nya bukan NULL. Kemudian, kode ini mengelompokkan entri menurut akhiran nama yang dimulai dengan test. Terakhir, kueri menghitung jumlah entri di setiap grup:

SELECT
  REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
  COUNT(*) AS count
FROM
  `TABLE`
WHERE
  json_payload.jobName IS NOT NULL
GROUP BY name
ORDER BY count
LIMIT 20

Untuk contoh tambahan, lihat dokumentasi REGEXP_EXTRACT. Untuk contoh ekspresi reguler lain yang dapat Anda gunakan, lihat Fungsi, operator, dan kondisional.

Penelusuran lintas kolom

Bagian ini menjelaskan dua pendekatan berbeda yang dapat Anda gunakan untuk menelusuri beberapa kolom tabel.

Penelusuran berbasis token

Untuk menelusuri tabel untuk entri yang cocok dengan kumpulan istilah penelusuran, gunakan fungsi SEARCH. Fungsi ini memerlukan dua parameter: tempat untuk menelusuri dan kueri penelusuran. Karena fungsi SEARCH memiliki aturan khusus terkait cara penelusuran data, sebaiknya baca dokumentasi SEARCH.

Kueri berikut hanya mempertahankan baris yang memiliki kolom yang sama persis dengan "35.193.12.15":

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE` AS t
WHERE
  proto_payload IS NOT NULL AND
  log_id = "cloudaudit.googleapis.com/data_access" AND
  SEARCH(t,"`35.193.12.15`")
ORDER BY timestamp ASC
LIMIT 20

Pada kueri sebelumnya, {i>backtick <i}menggabungkan nilai yang akan ditelusuri. Cara ini memastikan bahwa fungsi SEARCH menelusuri kecocokan persis antara nilai kolom dan nilai di antara tanda kutip terbalik.

Jika tanda backtick dihilangkan dalam string kueri, string kueri akan dipisahkan berdasarkan aturan yang ditentukan dalam dokumentasi SEARCH. Misalnya, saat pernyataan berikut dijalankan, string kueri akan dibagi menjadi empat token: "35", "193", "12", dan "15":

  SEARCH(t,"35.193.12.15")

Pernyataan SEARCH sebelumnya cocok dengan baris jika satu kolom cocok dengan keempat token. Urutan token tidak berpengaruh.

Anda dapat menyertakan beberapa pernyataan SEARCH dalam kueri. Misalnya, pada kueri sebelumnya, Anda dapat mengganti filter pada ID log dengan pernyataan seperti berikut:

  SEARCH(t,"`cloudaudit.googleapis.com/data_access`")

Pernyataan sebelumnya menelusuri seluruh tabel, sedangkan pernyataan asli hanya menelusuri kolom log_id.

Untuk melakukan beberapa penelusuran di kolom, pisahkan setiap string dengan spasi. Misalnya, pernyataan berikut cocok dengan baris yang kolomnya berisi "Hello World", "happy", dan "days":

  SEARCH(t,"`Hello World` happy days")

Terakhir, Anda dapat mencari kolom tertentu dari sebuah tabel, daripada mencari di seluruh tabel. Misalnya, pernyataan berikut hanya menelusuri kolom bernama text_payload dan json_payload:

   SEARCH((text_payload, json_payload) ,"`35.222.132.245`")

Untuk mendapatkan informasi tentang cara pemrosesan parameter fungsi SEARCH, lihat halaman referensi BigQuery, Fungsi penelusuran.

Penelusuran substring

Untuk melakukan pengujian yang tidak peka huruf besar/kecil guna menentukan apakah suatu nilai ada dalam ekspresi, gunakan fungsi CONTAINS_SUBSTR. Fungsi ini menampilkan TRUE jika nilainya ada dan FALSE jika tidak. Nilai penelusuran harus berupa literal STRING, tetapi bukan NULL literal.

Misalnya, kueri berikut mengambil semua entri log audit Akses Data dengan alamat IP tertentu yang stempel waktunya berada dalam rentang waktu tertentu. Terakhir, kueri mengurutkan hasil, lalu menampilkan 20 hasil terlama:

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE` AS t
WHERE
  proto_payload IS NOT NULL AND
  log_id = "cloudaudit.googleapis.com/data_access" AND
  CONTAINS_SUBSTR(t,"35.193.12.15")
ORDER BY timestamp ASC
LIMIT 20

Kueri sebelumnya melakukan pengujian substring. Oleh karena itu, baris yang berisi "35.193.12.152" cocok dengan pernyataan CONTAINS_SUBSTR.

Menggabungkan data dari berbagai sumber

Pernyataan kueri memindai satu atau beberapa tabel atau ekspresi dan menampilkan baris hasil terkomputasi. Misalnya, Anda dapat menggunakan pernyataan kueri untuk menggabungkan hasil pernyataan SELECT pada tabel atau set data yang berbeda dengan berbagai cara, lalu memilih kolom dari data gabungan.

Menggabungkan data dari dua tabel dengan {i>join<i}

Untuk menggabungkan informasi dari dua tabel, gunakan salah satu operator join. Jenis gabungan dan klausa bersyarat yang Anda gunakan menentukan cara baris digabungkan dan dibuang.

Kueri berikut memberi Anda kolom json_payload dari baris dalam dua tabel berbeda yang ditulis oleh span rekaman aktivitas yang sama. Kueri menjalankan JOIN bagian dalam pada dua tabel untuk baris yang memiliki nilai kolom span_id dan trace di kedua tabel yang cocok. Dari hasil ini, kueri kemudian memilih kolom timestamp, severity, dan json_payload yang berasal dari TABLE_1, kolom json_payload dari TABLE_2, serta nilai kolom span_id dan trace yang digabungkan dengan kedua tabel, dan menampilkan hingga 100 baris:

SELECT
  a.timestamp, a.severity, a.json_payload, b.json_payload, a.span_id, a.trace
FROM `TABLE_1` a
JOIN `TABLE_2` b
ON
  a.span_id = b.span_id AND
  a.trace = b.trace
LIMIT 100

Menggabungkan beberapa pilihan dengan union

Untuk menggabungkan hasil dari dua pernyataan SELECT atau lebih dan menghapus baris duplikat, gunakan operator UNION. Untuk mempertahankan baris duplikat, gunakan operator UNION ALL.

Kueri berikut membaca data jam terakhir dari TABLE_1, menggabungkan hasilnya dengan data jam terakhir dari TABLE_2, mengurutkan data yang digabungkan dengan meningkatkan stempel waktu, lalu menampilkan 100 entri terlama:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM(
  SELECT * FROM `TABLE_1`
  UNION ALL
  SELECT * FROM `TABLE_2`
)
ORDER BY timestamp ASC
LIMIT 100

Langkah selanjutnya

Untuk mendapatkan informasi tentang cara mengarahkan dan menyimpan entri log, lihat dokumen berikut:

Untuk dokumentasi referensi SQL, lihat dokumen berikut: