Kueri dan analisis log menggunakan pernyataan SQL - Security Center

Laman Log Analysis di Security Center memungkinkan Anda menulis kueri kustom dan melakukan analisis statistik terhadap log keamanan. Gunakan sintaks kueri yang dijelaskan dalam topik ini untuk menyaring log berdasarkan kata kunci atau bidang, mengagregasi hasil dengan SQL, serta menganalisis pola logon berdasarkan waktu atau alamat IP.

Cara kerja

Pernyataan kueri log terdiri dari dua bagian, dipisahkan oleh tanda pipa vertikal (|):

<sintaks pencarian> | <sintaks analitik>

Kedua bagian bersifat opsional:

Bagian	Fungsinya	Batasannya
Search	Menyaring log berdasarkan kata kunci, nilai bidang, atau rentang numerik	Tidak dapat melakukan agregasi atau menghitung statistik
Analytics	Mengagregasi dan menghitung statistik pada hasil yang telah disaring atau seluruh data dalam rentang waktu tertentu	Tidak dapat menyaring berdasarkan kata kunci teks penuh secara mandiri

Jika bagian pencarian berupa * atau kosong, tidak ada penyaringan yang diterapkan — semua log dalam rentang waktu yang dipilih akan diteruskan ke bagian analitik.
Jika bagian analitik kosong, entri log yang cocok akan dikembalikan apa adanya, tanpa perhitungan statistik.

Untuk membuka editor kueri, buka Risk Governance > Log Analysis di panel navigasi sebelah kiri Konsol Security Center, lalu masukkan pernyataan Anda di Search & Analyze.

Sintaks pencarian

Bagian pencarian mendukung kueri teks penuh dan pencarian berdasarkan bidang tertentu. Editor kueri mendukung input multi-baris dan penyorotan sintaks.

Kueri teks penuh

Lakukan pencarian di seluruh bidang log tanpa menentukan nama bidang. Pisahkan beberapa kata kunci dengan spasi atau and. Apit frasa yang mengandung spasi dengan tanda kutip ganda (").

Kueri	Hasilnya
`www.aliyundoc.com 404`	Log yang mengandung kedua istilah tersebut
`www.aliyundoc.com and 404`	Sama seperti di atas
`www.aliyundoc.com and (error or 404)`	Log yang mengandung `www.aliyundoc.com` dan salah satu dari `error` atau `404`
`www.aliyundoc.com and failed_*`	Log yang mengandung `www.aliyundoc.com` dan istilah apa pun yang diawali dengan `failed_`

Pencocokan wildcard hanya mendukung tanda bintang di akhir (failed_*). Tanda bintang di awal (*failed) tidak didukung.

Untuk referensi lengkap sintaks pencarian, lihat Ikhtisar pencarian log.

Pencarian berdasarkan bidang

Filter berdasarkan bidang tertentu menggunakan Field: Value atau perbandingan numerik seperti Field >= Value. Gabungkan beberapa kondisi bidang dengan and atau or, atau campur dengan istilah teks penuh.

Setiap entri log mencakup bidang __topic__ yang mengidentifikasi tipe log. Gunakan __topic__ untuk menargetkan sumber log tertentu, lalu tambahkan filter bidang untuk tipe log tersebut.

Contoh

Cari log peringatan dengan tingkat keparahan serious:

__topic__ : sas-security-log and level: serious

Cari event logon SSH dari alamat IP tertentu:

__topic__:aegis-log-login and ip:1.2.XX.XX and warn_type:SSHLOGIN

Cari kueri DNS internal dengan waktu respons lebih dari 1 detik:

__topic__:local-dns and time_usecond > 1000000

Cari kueri DNS internal dengan waktu respons antara 1 detik hingga 10 detik:

__topic__:local-dns and time_usecond in [1000000,10000000]

Sintaks analitik

Bagian analitik menggunakan pernyataan SQL-92. Klausul from log — setara dengan from <nama tabel> dalam SQL standar — dapat dihilangkan.

Secara default, kueri mengembalikan 100 entri log pertama. Gunakan klausa LIMIT untuk mengubah jumlah tersebut. Untuk detailnya, lihat Klausa LIMIT.

Untuk daftar lengkap fungsi SQL yang didukung, lihat Ikhtisar analisis log.

Kueri berbasis waktu

Setiap entri log memiliki bidang bawaan __time__ yang menyimpan stempel waktu log sebagai Stempel waktu UNIX (jumlah detik sejak 1 Januari 1970 pukul 00:00:00 UTC). Konversi bidang ini sebelum menampilkannya dalam hasil kueri.

Ambil event logon terbaru dari IP tertentu

Kembalikan 10 event logon terbaru dari IP 1.2.XX.XX, dengan stempel waktu yang mudah dibaca:

__topic__: aegis-log-login and ip: 1.2.XX.XX
| select date_format(__time__, '%Y-%m-%d %H:%i:%s') as time, warn_ip, warn_type
order by __time__ desc
limit 10

Hitung berapa hari yang telah berlalu sejak setiap logon

Tambahkan kolom days_passed yang menunjukkan usia setiap event logon dalam satuan hari:

__topic__: aegis-log-login and ip: 1.2.XX.XX
| select date_format(__time__, '%Y-%m-%d %H:%i:%s') as time, warn_ip, warn_type ,
round((to_unixtime(now()) - __time__)/86400,1) as "days_passed"
order by __time__ desc
limit 10

round((to_unixtime(now()) - __time__)/86400, 1) bekerja sebagai berikut:

to_unixtime(now()) mengonversi waktu saat ini menjadi Stempel waktu UNIX.
Kurangi __time__ untuk mendapatkan jumlah detik yang telah berlalu.
Bagi dengan 86400 (jumlah detik dalam sehari).
round(..., 1) membulatkan hasilnya ke satu tempat desimal.

Hitung jumlah event logon harian untuk suatu perangkat

Kelompokkan event logon berdasarkan hari dan hitung jumlah yang terjadi setiap hari:

__topic__: aegis-log-login and ip: 1.2.XX.XX
| select date_trunc('day', __time__) as dt,
count(1) as PV
group by dt
order by dt

date_trunc('day', __time__) menyelaraskan setiap stempel waktu ke awal harinya. Ganti 'day' dengan 'second', 'minute', 'hour', 'week', 'month', atau 'year' untuk mengelompokkan berdasarkan satuan waktu lainnya.

Hitung jumlah event logon per 5 menit

Kelompokkan event ke dalam bucket berdurasi 5 menit di seluruh perangkat:

__topic__: aegis-log-login
| select from_unixtime(__time__ - __time__% 300) as dt,
count(1) as PV
group by dt
order by dt
limit 1000

__time__ - __time__ % 300 membulatkan setiap stempel waktu ke batas 5 menit terdekat (300 detik). limit 1000 mengambil 1.000 bucket pertama, yang mencakup sekitar 83 jam data.

Untuk fungsi waktu lainnya seperti date_parse dan date_format, lihat Fungsi tanggal dan waktu.

Kueri berbasis alamat IP

Bidang warn_ip dalam entri log logon menyimpan alamat IP sumber. Gunakan fungsi geolokasi IP untuk menganalisis asal logon.

Identifikasi negara asal logon ke server tertentu

__topic__: aegis-log-login and uuid: 12344567
| SELECT ip_to_country(warn_ip) as country,
count(1) as "Number of logons"
group by country

ip_to_country(warn_ip) mengembalikan negara yang sesuai dengan alamat IP sumber.

Rincian logon berdasarkan provinsi atau negara bagian

__topic__: aegis-log-login and uuid: 12344567
| SELECT ip_to_province(warn_ip) as province,
         count(1) as "Number of logons"
         group by province

ip_to_province(warn_ip) mengembalikan provinsi untuk alamat IP Tiongkok. Untuk IP non-Tiongkok, fungsi ini mencoba mengembalikan provinsi atau negara bagian yang setara, tetapi hasilnya tidak dapat ditampilkan pada peta Tiongkok.

Petakan distribusi geografis logon

__topic__: aegis-log-login and uuid: 12344567
| SELECT ip_to_geo(warn_ip) as geo,
         count(1) as "Number of logons"
         group by geo
         limit 10000

ip_to_geo(warn_ip) mengembalikan lintang dan bujur alamat IP sumber. limit 10000 mengambil hingga 10.000 entri untuk pemetaan geografis.

Untuk fungsi IP tambahan — termasuk ip_to_provider (pencarian ISP) dan ip_to_domain (deteksi IP publik vs. privat) — lihat Fungsi IP.