ログの正規化と時系列の作成
同じログが二度届いた例を使い、UTCへの変換、イベント単位の重複除去、所属を含む検索、収集期間の確認を行います。発生順と到着順を混同せず、記録がない期間について結論を広げない方法を学びます。
説明
異なる端末から集めたログは、時刻の表記や到着順が一致するとは限りません。アカウント名も、所属するドメインや端末が違えば別の主体です。
同じログが二度届いた例を使い、UTCへの変換、イベント単位の重複除去、所属を含む検索、収集期間の確認を行います。発生順と到着順を混同せず、記録がない期間について結論を広げない方法を学びます。
1元の時刻表記を確認する
local_timeは発生時刻、receivedは収集側への到着時刻です。
from oplab import *
db=connect(1)
show(db, 'SELECT元の時刻表記を確認する * FROM logs')e1には+09:00の時差があり、同じIDの行が二つ届いています。到着時刻が違っても別のログオンとは限りません。
2発生時刻をUTCへ変換する
SQLiteのdatetimeは明示されたUTCオフセットを反映します。
show(db, 'SELECT発生時刻をUTCへ変換する id,host,datetime(local_time) AS utc,received FROM logs ORDER BY utc,received')e1の10時はUTCの01時です。時差を含む元の文字列をそのまま辞書順で並べる方法では、正しい発生順になりません。
3同じイベントの二重配送を数える
イベントIDは端末と起動区間も組み合わせて扱います。この試料のidは起動区間内で一意です。
show(db, 'SELECT同じイベントの二重配送を数える host,boot,id,COUNT(*) AS copies FROM logs GROUP BY host,boot,id')ws7/b1/e1は2行です。別端末の同じ番号まで消さないように、重複を判定する範囲を定めます。
4重複を除いた時系列を作る
発生内容が一致する行をまとめ、最初の到着時刻を残します。
show(db, 'SELECT重複を除いた時系列を作る host,boot,id,datetime(local_time) AS utc,account,action,MIN(received) AS first_seen FROM logs GROUP BY host,boot,id,local_time,account,action ORDER BY utc')観測した活動は3件です。重複除去は失敗記録を減らす操作ではなく、同じ記録の配送回数を整理する処理です。
5所属を含めて主体を絞る
CORP/aliceとLOCAL/aliceを同一人物と仮定せず検索します。
show(db, 'SELECT所属を含めて主体を絞る DISTINCT id,host,account,action FROM logs WHERE account="CORP/alice" ORDER BY id')対象はws7のログオンとプロセスです。ws8の同名アカウントを結び付けるには別の証拠が必要です。
6収集期間を確認する
記録がないことを評価する前に、対象端末とログ種別が収集対象だったか調べます。
show(db, 'SELECT収集期間を確認する * FROM coverage ORDER BY host')対象の収集期間は01:00〜01:10のSecurityです。それ以前の活動や、この資料にない別ログの活動は判断できません。
まとめ
対象の収集期間は01:00〜01:10のSecurityです。それ以前の活動や、この資料にない別ログの活動は判断できません。
表示された証拠IDと検索条件を残すと、調査結果を元の記録までたどれます。
操作履歴と解説を振り返れます。
Python 3.15 / SQLite・固定の調査記録
from oplab import *
db=connect(1)
show(db, 'SELECT * FROM logs')id | host | boot | local_time | account | action | received e1 | ws7 | b1 | 2026-01-07T10:00:00+09:00 | CORP/alice | login | 2026-01-07T01:00:04Z e1 | ws7 | b1 | 2026-01-07T10:00:00+09:00 | CORP/alice | login | 2026-01-07T01:00:06Z e2 | ws7 | b1 | 2026-01-07T01:00:02Z | CORP/alice | process | 2026-01-07T01:00:08Z e3 | ws8 | b2 | 2026-01-07T01:00:01Z | LOCAL/alice | login | 2026-01-07T01:00:05Z
show(db, 'SELECT id,host,datetime(local_time) AS utc,received FROM logs ORDER BY utc,received')id | host | utc | received e1 | ws7 | 2026-01-07 01:00:00 | 2026-01-07T01:00:04Z e1 | ws7 | 2026-01-07 01:00:00 | 2026-01-07T01:00:06Z e3 | ws8 | 2026-01-07 01:00:01 | 2026-01-07T01:00:05Z e2 | ws7 | 2026-01-07 01:00:02 | 2026-01-07T01:00:08Z
show(db, 'SELECT host,boot,id,COUNT(*) AS copies FROM logs GROUP BY host,boot,id')host | boot | id | copies ws7 | b1 | e1 | 2 ws7 | b1 | e2 | 1 ws8 | b2 | e3 | 1
show(db, 'SELECT host,boot,id,datetime(local_time) AS utc,account,action,MIN(received) AS first_seen FROM logs GROUP BY host,boot,id,local_time,account,action ORDER BY utc')host | boot | id | utc | account | action | first_seen ws7 | b1 | e1 | 2026-01-07 01:00:00 | CORP/alice | login | 2026-01-07T01:00:04Z ws8 | b2 | e3 | 2026-01-07 01:00:01 | LOCAL/alice | login | 2026-01-07T01:00:05Z ws7 | b1 | e2 | 2026-01-07 01:00:02 | CORP/alice | process | 2026-01-07T01:00:08Z
show(db, 'SELECT DISTINCT id,host,account,action FROM logs WHERE account="CORP/alice" ORDER BY id')id | host | account | action e1 | ws7 | CORP/alice | login e2 | ws7 | CORP/alice | process
show(db, 'SELECT * FROM coverage ORDER BY host')host | start | end | source ws7 | 2026-01-07T01:00:00Z | 2026-01-07T01:10:00Z | Security ws8 | 2026-01-07T01:00:00Z | 2026-01-07T01:10:00Z | Security
演習完了