SQL文を使用してログをクエリおよび分析する方法 - Security Center

Security Centerコンソールの [ログ分析] ページで、複数の複雑なシナリオでカスタムログクエリと分析を実行できます。このトピックでは、クエリおよび分析ステートメントの構文について説明します。

概要

Security Centerコンソールの左側のナビゲーションウィンドウで、[Risk Governance] > [ログ分析] を選択し、[Search & Analyze] にSQL文を入力して、カスタムログクエリと分析を実行できます。ログクエリステートメントは、検索構文と分析構文の2つの部分で構成され、縦棒 (|) で区切られています。

カスタムログクエリと分析を実行する場合、検索構文と分析構文はオプションです。次のリストに、検索構文と分析構文を示します。

検索: キーワード、あいまい一致条件、数値、範囲、または組み合わせを使用して検索条件を生成できます。 Search部分の値がアスタリスク (*) または空の場合、指定された期間のデータはフィルタリングされません。この場合、指定された期間のすべてのデータが分析に使用されます。
分析: 検索結果または完全なデータに関する統計を計算および収集します。 Analytics部分の値が空の場合、クエリの結果は返されますが、統計は計算されません。

検索構文

Simple Log Serviceの検索構文は、フルテキストクエリとフィールドクエリの両方をサポートしています。クエリボックスは、複数行の検索や構文の強調表示などの機能をサポートしています。

フルテキストクエリ
フィールドを指定しなくても、キーワードを入力してログを検索できます。複数のキーワードを使用するには、各キーワードを引用符 (") のペアで囲み、複数のキーワードをスペースまたは演算子とで区切ります。例：
- 複数のキーワードを指定したクエリ
  www.aliyundoc.comと404を含むログを検索します。例：
```
www.aliyundoc.com 404
```
  または:
```
www.aliyundoc.comと404
```
- 条件付きクエリ
  www.aliyundoc.comとエラーを含むログ、またはn www.aliyundoc.comと404を含むログを検索します。例：
```
www.aliyundoc.com and (error or 404)
```
- ファジー検索
  www.aliyundoc.comを含むログを検索し、failed_ で開始します。例：
```
www.aliyundoc.com and failed_*
```
  説明
  フルテキストクエリは、アスタリスク (*) の付いたサフィックスのみをサポートします。アスタリスク (*) の付いたプレフィックスはサポートされていません。
フィールド固有の検索
数値フィールドのデータは、Field: ValueまたはField >= Valueを使用してフィルタリングできます。 とまたはまたはを使用して、2つの形式を組み合わせることができます。 andおよびまたはで指定された組み合わせを使用して、フルテキストクエリで使用することもできます。
Simple Log Serviceは、フィールドに基づいたより正確なクエリをサポートします。
- マルチフィールドクエリ
  重大度レベルが深刻なアラートログを検索します。例：
```
__topic__ : sas-security-log and level: serious
```
  IPアドレスが1.2.XX.XXのクライアントでSSHログオンを検索します。例：
```
__topic__:aegis-log-login and ip:1.2.XX.XX and warn_type:SSHLOGIN
```
  説明
  各ログには、ログトピックを示す __topic__ フィールドが含まれます。ログはこのフィールドによって区別されます。これらの例では、level、warn_type、ipなどのフィールドは、特定の種類のログのフィールドです。
- 数値フィールドクエリ
  応答時間が1秒を超える内部DNSクエリログを検索します。例：
```
__topic __: local-dns and time_usecond > 1000000
```
  範囲ベースのクエリもサポートされています。たとえば、範囲ベースのクエリを使用して、応答時間が1秒を超え10秒以下の内部DNSログを検索できます。例：
```
__topic __: local-dnsとtime_usecond in [1000000,10000000]
```
構文の詳細については、「ログ検索の概要」をご参照ください。

Analyticsの構文

SQL-92文を使用して、ログの統計を分析および収集できます。 Simple Log Serviceでサポートされている構文と機能の詳細については、「ログクエリと分析の概要」をご参照ください。

分析ステートメントでは、from log部分は標準SQLステートメントのfrom <table name> 部分と似ており、省略できます。

デフォルトでは、Simple Log Serviceは最初の100ログエントリを返します。 LIMIT構文を使用して、返すログエントリの数を変更できます。詳細は、「LIMIT句」をご参照ください。

時間ベースのログクエリと分析

各ログエントリには、このログエントリが生成された時刻を示す組み込みフィールド __time__ があります。このフィールドは、時間ベースの統計分析を容易にする。このフィールドの値は、1970年1月1日00:00:00 UTCのエポック時刻から経過した秒数を表すUNIXタイムスタンプです。したがって、タイムスタンプを表示する前に、サポートされている形式に変換する必要があります。

時間を選択して表示
この例では、特定の時間範囲内のIPアドレスが1.2.XX.XXの最新の10個のログオンログを照会します。戻り値には、時刻、送信元IPアドレス、およびログオンタイプが含まれます。例：
```
__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
```
時間の計算
__time__ を使用して、ログイン後の日数を計算します。例：
```
__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) を使用して計算を実行します。まず、関数はto_unixtimeを使用して、now() によって返された時刻をUNIXタイムスタンプに変換します。次に、計算値から組み込みの __time__ フィールドを減算して、経過した秒数を取得します。次に、計算された値を1日の合計秒数である86400で除算します。最後に、round(data, 1) 関数は、得られた値を小数点以下1桁に丸めて、各攻撃ログが生成されてから経過した日数を計算します。
特定の時間に基づくグループ統計
特定の時間範囲内のデバイスのログオン傾向を知りたい場合は、次のSQL文を実行します。
```
__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
```
この例では、組み込みの __time__ フィールドがdate_trunc('day', ..) 関数に渡され、時刻を日ごとに揃えます。各ログエントリは、総数 (count(1)) の計算を容易にするために、それが属する日のパーティションにグループ化される。ログエントリは、パーティションタイムブロックによってソートされます。 date_trunc関数の最初のパラメーターに他の値を使用して、second、minute、hour、week、month、yearなどの他の時間単位に基づいてログエントリをグループ化できます。関数の詳細については、「日付と時刻の関数」をご参照ください。
グループ統計に基づく柔軟な時間
アカウントのデバイスの5分あたりのログオン傾向など、時間グループ化のより柔軟なルールを知りたい場合は、次のSQLステートメントを実行します。
```
__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を計算し、フォーマットにfrom_unixtime関数を使用します。各ログエントリは5分 (300秒) のパーティションにグループ化され、総数 (count(1)) の計算を容易にします。ログエントリは、最初の1,000ログエントリを取得するためにパーティション時間ブロックごとにソートされます。これは、最初の83時間のデータを選択することに相当します。
時間関連関数の詳細については、「日付と時刻の関数」をご参照ください。たとえば、date_parseおよびdate_format関数は、時間形式を別の形式に変換できます。

クライアントIPアドレスベースのログクエリと分析

ログオンログエントリのwarn_ipフィールドは、ログオンの送信元ipアドレスを示します。

ログオンのソース国分布
ユーザーがサーバーにログオンする元の国の分布を照会します。例：
```
__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関数を使用して、ログオンの送信元ipアドレスを指定するwarn_ipに対応する国を取得します。
ログオンのアイデンティティ分布
ip_to_province関数を使用して、州に基づくログオンのより詳細な分布を取得できます。例：
```
__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関数を使用して、IPアドレスが属するソース州を取得します。 IPアドレスが中国からのものでない場合、システムはIPアドレスの国の位置に基づいて、それを州または州に変換しようとします。ただし、中国マップを選択した場合、州または州は表示されません。
ログオンの地熱分布
ip_to_geo関数を使用して、ログオンの地熱分布を取得できます。
```
__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関数を使用して、IPアドレスの緯度と経度を取得します。最初の10,000ログエントリを取得するには、LIMITを10000に設定します。
説明
IPアドレスベースの機能の詳細については、「IP機能」をご参照ください。たとえば、ip_to_provider関数を使用してIPアドレスのプロバイダを取得し、ip_to_domain関数を使用してIPアドレスがパブリックかプライベートかを判断できます。