Contoh kueri dan analisis - Simple Log Service

Topik ini menyajikan beberapa contoh analisis data log.

Aktifkan peringatan ketika laju kesalahan melebihi 40% dalam 5 menit terakhir

Hitung laju kesalahan HTTP 500 yang dikembalikan setiap menit untuk memicu peringatan ketika laju kesalahan melebihi 40% dalam 5 menit terakhir.

status:500 | select __topic__, max_by(error_count,window_time)/1.0/sum(error_count) as error_ratio, sum(error_count) as total_error  from (
select __topic__, count(*) as error_count , __time__ - __time__ % 300  as window_time  from log group by __topic__, window_time
) 
group by __topic__  having  max_by(error_count,window_time)/1.0/sum(error_count)   > 0.4  and sum(error_count) > 500 order by total_error desc limit 100

Kumpulkan statistik lalu lintas dan konfigurasikan peringatan

Kumpulkan statistik lalu lintas setiap menit untuk memicu peringatan jika volume lalu lintas turun di bawah ambang batas tertentu. Statistik yang dikumpulkan pada menit sebelumnya tidak mencakup satu menit penuh. Untuk menghitung rata-rata statistik lalu lintas per menit, bagi nilai statistik dengan greatest(max(__time__) - min(__time__),1) untuk normalisasi.

* | SELECT SUM(inflow) / greatest(max(__time__) - min(__time__),1) as inflow_per_minute, date_trunc('minute',__time__)  as minute group by minute

Hitung latensi rata-rata berdasarkan ukuran data

Distribusikan data ke beberapa bucket berdasarkan ukuran data dan hitung latensi rata-rata untuk setiap bucket.

* | select avg(latency) as latency , case when originSize < 5000 then 's1' when originSize < 20000 then 's2' when originSize < 500000 then 's3' when originSize < 100000000 then 's4' else 's5' end as os group by os

Kembalikan persentase hasil yang berbeda

Kembalikan hasil penghitungan dari departemen yang berbeda beserta persentase hasil tersebut. Kueri ini mencakup subkueri dan fungsi jendela. Ekspresi sum(c) over() menunjukkan jumlah nilai di semua baris.

* |  select   department, c*1.0/ sum(c) over ()  from(select  count(1) as c, department   from log group by department)

Hitung jumlah URL yang memenuhi kondisi kueri

Untuk menghitung jumlah URL berdasarkan karakteristiknya, Anda dapat menggunakan klausa CASE WHEN atau COUNT_IF. Klausa COUNT_IF lebih sederhana.

* | select  count_if(uri like '%login') as login_num, count_if(uri like '%register') as register_num, date_format(date_trunc('minute', __time__), '%m-%d %H:%i') as time  group by time order by time limit 100

Analisis agregat umum

Kueri distribusi global klien berdasarkan PV

Gunakan fungsi ip_to_country untuk mendapatkan negara tempat alamat IP berada, agregasikan negara berdasarkan bidang ip_country, dan hitung jumlah klien di setiap negara. Anda dapat mengonfigurasi peta dunia untuk menampilkan hasilnya. Untuk informasi lebih lanjut, lihat Konfigurasikan Peta Dunia.

* |
select
  ip_to_country(client_ip) as ip_country,
  count(*) as pv
group by
  ip_country
order by
  pv desc
limit
  500

Kueri kategori dan tren metode permintaan berdasarkan PV

Potong nilai waktu per menit, agregasikan dan kelompokkan nilai waktu yang dipotong untuk menghitung jumlah tampilan halaman (PV) berdasarkan bidang request_method, lalu urutkan nilai waktu. Anda dapat mengonfigurasi grafik garis untuk menampilkan hasilnya. Di grafik garis, atur sumbu x ke t, sumbu y ke pv, dan kolom agregat ke request_method. Untuk informasi lebih lanjut, lihat Grafik Garis.

* |
select
  date_format(date_trunc('minute', __time__), '%m-%d %H:%i') as t,
  request_method,
  count(*) as pv
group by
  t,
  request_method
order by
  t asc
limit
  10000

Kueri distribusi permintaan berbasis http_user_agent berdasarkan PV

Agregasikan dan kelompokkan data berdasarkan bidang http_user_agent, lalu hitung jumlah permintaan dari setiap agen dan ukuran respons total yang dikembalikan ke klien. Konversikan unit ukuran respons total dari byte menjadi MB dan bulatkan ke dua tempat desimal. Gunakan ekspresi CASE WHEN untuk mengategorikan string status ke dalam lapisan, termasuk 2xx, 3xx, 4xx, dan 5xx, serta hitung persentase setiap lapisan. Anda dapat mengonfigurasi tabel untuk menampilkan hasilnya. Untuk informasi lebih lanjut, lihat Tabel Dasar.

* |
select
  http_user_agent as "User agent",
  count(*) as pv,
  round(sum(request_length) / 1024.0 / 1024, 2) as "Request traffic (MB)",
  round(sum(body_bytes_sent) / 1024.0 / 1024, 2) as "Response traffic (MB)",
  round(
    sum(
      case
        when status >= 200
        and status < 300 then 1
        else 0
      end
    ) * 100.0 / count(1),
    6
  ) as "Persentase kode status 2xx (%)",
  round(
    sum(
      case
        when status >= 300
        and status < 400 then 1
        else 0
      end
    ) * 100.0 / count(1),
    6
  ) as "Persentase kode status 3xx (%)",
  round(
    sum(
      case
        when status >= 400
        and status < 500 then 1
        else 0
      end
    ) * 100.0 / count(1),
    6
  ) as "Persentase kode status 4xx (%)",
  round(
    sum(
      case
        when status >= 500
        and status < 600 then 1
        else 0
      end
    ) * 100.0 / count(1),
    6
  ) as "Persentase kode status 5xx (%)"
group by
  "User agent"
order by
  pv desc
limit
  100

Kueri konsumsi harian dan hasil prakiraan tren untuk bulan saat ini

Kelompokkan data berdasarkan nomor tagihan dan dapatkan informasi, termasuk waktu, pengenal layanan, jenis tagihan, dan jumlah yang harus dibayar. Hitung pengeluaran harian, cari hari terbaru di mana pengeluaran dihasilkan berdasarkan waktu, dan gunakan fungsi sls_inner_ts_regression untuk mendapatkan konsumsi harian dan hasil prakiraan tren untuk bulan saat ini. Waktu, pengeluaran, string yang dilewati, periode prakiraan, dan jumlah titik prakiraan ditentukan dalam fungsi sls_inner_ts_regression. Anda dapat menggunakan nama string yang dilewati sebagai alias. Anda dapat mengonfigurasi grafik garis untuk menampilkan konsumsi harian dan hasil prakiraan tren untuk bulan saat ini. Di grafik garis, atur sumbu x ke waktu dan sumbu y ke konsumsi aktual dan konsumsi prakiraan. Untuk informasi lebih lanjut, lihat Grafik Garis.

source :bill |
select
  date_format(res.stamp, '%Y-%m-%d') as time,
  res.real as "Konsumsi aktual",case
    when is_nan(res.real) then res.pred
    else null
  end as "Konsumsi prakiraan",
  res.instances
from(
    select
      sls_inner_ts_regression(
        cast(day as bigint),
        total,
        array ['total'],
        86400,
        60
      ) as res
    from
      (
        select
          *
        from
          (
            select
              *,
              max(day) over() as lastday
            from
              (
                select
                  to_unixtime(date_trunc('day', __time__)) as day,
                  sum(PretaxAmount) as total
                from
                  (
                    select
                      RecordID,
                      arbitrary(__time__) as __time__,
                      arbitrary(ProductCode) as ProductCode,
                      arbitrary(item) as item,
                      arbitrary(PretaxAmount) as PretaxAmount
                    from
                      log
                    group by
                      RecordID
                  )
                group by
                  day
                order by
                  day
              )
          )
        where
          day < lastday
      )
  )
limit
  1000

Kueri persentase konsumsi setiap layanan untuk bulan saat ini

Hitung total pengeluaran setiap layanan berdasarkan nama layanan, gunakan fungsi row_number untuk mengurutkan layanan berdasarkan pengeluaran secara menurun, cari enam layanan teratas dengan pengeluaran tertinggi, dan kelompokkan layanan lainnya ke dalam "Lainnya". Anda dapat mengonfigurasi grafik donat untuk menampilkan hasilnya. Untuk informasi lebih lanjut, lihat Konfigurasikan Grafik Donat.

source :bill |
select
  case
    when rnk > 6
    or pretaxamount <= 0 then 'Lainnya'
    else ProductName
  end as ProductName,
  sum(PretaxAmount) as PretaxAmount
from(
    select
      *,
      row_number() over(
        order by
          pretaxamount desc
      ) as rnk
    from(
        select
          ProductName,
          sum(PretaxAmount) as PretaxAmount
        from
          log
        group by
          ProductName
      )
  )
group by
  ProductName
order by
  PretaxAmount desc
limit
  1000

Hitung rasio pengeluaran hari sebelumnya terhadap pengeluaran hari yang sama di bulan sebelumnya

Hitung pengeluaran hari sebelumnya, gunakan ekspresi COALESCE untuk memastikan bahwa 0 dikembalikan jika tidak ada pengeluaran yang dihasilkan, gunakan fungsi round untuk membulatkan hasil ke tiga tempat desimal, dan gunakan fungsi compare untuk menghitung rasio pengeluaran hari sebelumnya terhadap pengeluaran hari yang sama di bulan sebelumnya. Anda dapat mengonfigurasi grafik tren untuk menampilkan hasilnya. Untuk informasi lebih lanjut, lihat Grafik Tren.

source :bill |
select
  round(diff [1], 2),
  round(diff [2], 2),
  round(diff [3] * 100 -100, 2)
from(
    select
      compare("Pengeluaran hari sebelumnya", 604800) as diff
    from(
        select
          round(coalesce(sum(PretaxAmount), 0), 3) as "Pengeluaran hari sebelumnya"
        from
          log
      )
  )

Analisis layanan web Tomcat

Kueri tren status permintaan Tomcat

Gunakan fungsi date_trunc untuk memotong nilai waktu di log per menit, gunakan fungsi date_format untuk mengekstrak nilai waktu dalam jam dan menit, dan gunakan klausa GROUP BY untuk mengagregasikan data berdasarkan nilai waktu yang diekstraksi dan kode status untuk menghitung jumlah setiap kode status per menit. Anda dapat mengonfigurasi bagan alir untuk menampilkan hasilnya. Di bagan alir, atur sumbu x ke waktu, sumbu y ke jumlah, dan kolom agregat ke status. Untuk informasi lebih lanjut, lihat Bagan Alir.

* |
select
  date_format(date_trunc('minute', __time__), '%H:%i') as time,
  COUNT(1) as c,
  status
GROUP by
  time,
  status
ORDER by
  time
LIMIT
  1000

Kueri distribusi PV dan UV untuk akses Tomcat dari waktu ke waktu

Gunakan fungsi time_series untuk memotong nilai waktu di log per dua menit, agregasikan data berdasarkan waktu, lalu hitung jumlah PV dan pengunjung unik (UV). Gunakan fungsi approx_distinct untuk menghitung jumlah PV dan UV setelah deduplikasi dan urutkan hasilnya berdasarkan waktu. Anda dapat mengonfigurasi grafik garis dengan beberapa sumbu y untuk menampilkan hasilnya. Di grafik garis, atur sumbu x ke waktu dan sumbu y ke uv dan pv. Untuk informasi lebih lanjut, lihat Buat Grafik Garis dengan Beberapa Sumbu Y.

* |
select
  time_series(__time__, '2m', '%H:%i', '0') as time,
  COUNT(1) as pv,
  approx_distinct(remote_addr) as uv
GROUP by
  time
ORDER by
  time
LIMIT
  1000

Kueri jumlah permintaan kesalahan Tomcat dan perbandingannya dengan jam sebelumnya

Gunakan pernyataan SQL internal untuk mendapatkan jumlah permintaan yang kode statusnya lebih besar dari atau sama dengan 400 dan gunakan fungsi compare di bagian tengah pernyataan SQL untuk mendapatkan data yang dihasilkan 3.600 detik yang lalu. Di pernyataan SQL eksternal, c1 menentukan jumlah permintaan kesalahan pada waktu saat ini, c2 menentukan jumlah permintaan kesalahan yang dihasilkan 3.600 detik yang lalu, dan c3 menentukan rasio c1 terhadap c2. Anda dapat mengonfigurasi grafik donat untuk menampilkan hasilnya. Di grafik donat, c1 adalah nilai tampilan dan c3 adalah nilai perbandingan. Untuk informasi lebih lanjut, lihat Konfigurasikan Grafik Donat.

status >= 400 |
SELECT
  diff [1] AS c1,
  diff [2] AS c2,
  round(diff [1] * 100.0 / diff [2] - 100.0, 2) AS c3
FROM
  (
    select
      compare(c, 3600) AS diff
    from
      (
        select
          count(1) as c
        from
          log
      )
  )

Kueri 10 URI teratas dalam permintaan Tomcat

Agregasikan dan kelompokkan data berdasarkan bidang request_uri, hitung jumlah PV untuk setiap request_uri, urutkan hasilnya berdasarkan PV secara menurun, dan dapatkan 10 catatan teratas. Anda dapat mengonfigurasi pengukur batang dasar. Di pengukur batang, atur sumbu x ke halaman dan sumbu y ke pv. Untuk informasi lebih lanjut, lihat Konfigurasikan Pengukur Batang Dasar.

* |
SELECT
  request_uri as page,
  COUNT(*) as pv
GROUP by
  page
ORDER by
  pv DESC
LIMIT
  10

Kueri jenis dan distribusi klien Tomcat

Agregasikan dan analisis data berdasarkan bidang user_agent, lalu hitung jumlah klien dari setiap jenis. Anda dapat mengonfigurasi grafik donat untuk menampilkan hasilnya. Di grafik donat, atur tipe ke user_agent dan kolom nilai ke c. Untuk informasi lebih lanjut, lihat Konfigurasikan Grafik Donat.

* |
SELECT
  user_agent,
  COUNT(*) AS c
GROUP BY
  user_agent
ORDER BY
  c DESC

Kumpulkan statistik lalu lintas arah keluar Tomcat

Gunakan fungsi time_series untuk memotong nilai waktu dan mengagregasikan nilai waktu untuk menghitung jumlah nilai bidang body_bytes_sent. Anda dapat mengonfigurasi sumbu x dan sumbu y grafik garis untuk menampilkan hasilnya. Di grafik garis, atur sumbu x ke waktu dan sumbu y ke body-sent. Untuk informasi lebih lanjut, lihat Konfigurasikan Sumbu X dan Sumbu Y Grafik Garis.

* |
select
  time_series(__time__, '10s', '%H:%i:%S', '0') as time,
  sum(body_bytes_sent) as body_sent
GROUP by
  time
ORDER by
  time
LIMIT
  1000

Kueri persentase jumlah permintaan kesalahan Tomcat

Kueri jumlah permintaan kesalahan yang kode statusnya lebih besar dari atau sama dengan 400 dan jumlah total permintaan dalam pernyataan SQL internal, lalu hitung rasio jumlah permintaan kesalahan Tomcat terhadap jumlah total permintaan. Anda dapat mengonfigurasi jarum untuk menampilkan hasilnya. Untuk informasi lebih lanjut, lihat Konfigurasikan Jarum.

* |
select
  round((errorCount * 100.0 / totalCount), 2) as errorRatio
from
  (
    select
      sum(
        case
          when status >= 400 then 1
          else 0
        end
      ) as errorCount,
      count(1) as totalCount
    from
      log
  )