2024-11-17

【AWS】S3に保存されたVPCフローログをSQL（ATHENA）を使用してデータ分析してみる

AWS

仕事でＶＰＣフローログを解析する必要があったのでATHENAを使用してみました。

手動でエクセルにまとめて解析するよりずっと早く便利です。

最初は戸惑いますがあとはやり方がわかれば簡単にログ解析が可能です。

ATHENA設定

データベース作成

まずデータベースの作成が必要です。

CREATE DATABASE VPCFlowLogsDB

テーブル作成

次にテーブルを作成します。

CREATE EXTERNAL TABLE IF NOT EXISTS VPCFlowLogsTB (
  version int,
  account string,
  interfaceid string,
  sourceaddress string,
  destinationaddress string,
  sourceport int,
  destinationport int,
  protocol int,
  numpackets int,
  numbytes bigint,
  starttime int,
  endtime int,
  action string,
  logstatus string
)
PARTITIONED BY (date string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
LOCATION 's3://{S3バケット名}/AWSLogs/{AWSアカウント番号}/vpcflowlogs/ap-northeast-1/'
TBLPROPERTIES ("skip.header.line.count"="1");

LOCATIONのS3パスはVPCフローログ保管場所を指定します。

パーティション作成

ATHENAはSELECT分を実行毎にコストがかかります。

実行結果のデータ量ではなく、スキャンしたテーブル全体のデータ量がコストにかかわってきます。

（2024年時点、1TBあたり5USドル）

そのため、対象データをパーティションで設定することでスキャンする範囲を絞ります。絞ることで料金を抑えることが可能です。

ALTER TABLE VPCFlowLogsTB
ADD PARTITION (date='2024-11-1')
location 's3://{S3バケット名}/AWSLogs/{AWSアカウント番号}/vpcflowlogs/ap-northeast-1/2024/11/01';

作成したテーブルにパーティションを追加します。

【注意点１】

ADD PARTITION (date='2024-11-01')に関して間は「/」ではなく「-」を使用する

〇　ADD PARTITION (date='2024/11/01')

×　ADD PARTITION (date='2024-11-01')

　※date=YYYY/MM/DDと指定するとパーティションが生成されない

【注意点２】

日付ごとに実行する必要があります。

〇　ADD PARTITION (date='2024-11-01')

×　ADD PARTITION (date='2024-11')

SQLを実行してみる

準備は整ったのでSELECT文を実行してみます。

SELECT
  from_unixtime(starttime, 9, 0) AS starttime_jst,
  from_unixtime(endtime, 9, 0) AS endtime_jst,
  interfaceid,
  sourceaddress,
  destinationaddress,
  sourceport,
  destinationport,
  protocol,
  numpackets,
  numbytes,
  action,
  logstatus
FROM vpc_flow_logs
WHERE from_unixtime(starttime, 9, 0) >= cast('2024-11-01 12:00:00 +09:00' as timestamp with time zone)
  AND from_unixtime(starttime, 9, 0) <  cast('2020-11-01 13:00:00 +09:00' as timestamp with time zone)
ORDER BY starttime;

ATHENAを使うにあたっての注意点

・SELECT文で実行した結果CSVファイルは、「s3://{S3バケット名}/」配下に保存されます。

SELECTを実行するごとに結果CSVデータがたまっていくので主導で削除が必要です。

・ダウンロードした結果CSVファイルはUTF-8のため、エクセルで開くと日本語が文字化けします。

参考URL

docs.aws.amazon.com

参考書籍

AWSの基本・仕組み・重要用語が全部わかる教科書 (見るだけ図解)

作者:川畑光平,菊地貴彰,真中俊輝
SBクリエイティブ

Amazon

2024-11-10

書籍「ネットワーク超入門(著 GENE) 」演習⑤　ルーティング

Cisco Packet Tracer ネットワーク超入門

ネットワーク技術力向上のために購入した書籍「ネットワーク超入門」内の演習をCisco Packet Tracer で実際に構築してみました。

今回の演習で扱ったルーティングはネットワークの基本的かつ非常に重要な概念です。

ルーティングがなければ、異なるネットワーク間でデータを適切に転送することができません。

特に複雑なネットワーク環境において、ルーティングは効率的かつ安定的なデータ通信のために必要不可欠な仕組みとなります。

ルーティングには大きく分けて「静的ルーティング」と「動的ルーティング」がありますが、「静的ルーティング」を実際に構築してみます。

演習

演習概要

演習タイトル：ルーティング（スタティックルート）

掲載ページ：P.185-200

演習概要：3台のルータを間に挟んだPC2台を通信させる。各ルータには静的ルーティングを設定する。

各機器の配置と配線

【ネットワーク図】

【PC1】から【PC4】

【Router1】から【Router3】

【ネットワークIPアドレス表】

ネットワーク設定

各機器のネットワーク設定を行っていきます。PCの設定はGUIで行いました。ルータに関しては、CCNA勉強のため、CLIからコマンドにて設定しています。

PC1とPC2に関しては、別セグメント（10.3.3.0/24、192.168.10.0/24、192.168.20.0/24）への通信が必要なため、デフォルトゲートウェイの設定が必要です。

【PC1】

IPアドレス　10.1.1.100

サブネットマスク　255.255.255.0

デフォルトゲートウェイ　10.1.1.1

【PC1】

IPアドレス　10.1.1.100

サブネットマスク　255.255.255.0

デフォルトゲートウェイ　10.3.3.1

【Router1】

Router(config)#hostname Router1

Router1(config)#interface fastEthernet 0/0
Router1(config-if)#ip address 10.1.1.1 255.255.255.0
Router1(config-if)#no shutdown
Router1(config-if)#exit
Router1(config)#interface fastEthernet 0/1
Router1(config-if)#ip address 192.168.10.1 255.255.255.0
Router1(config-if)#no shutdown
Router1(config-if)#exit

#スタティックルートの設定
Router1(config)#ip route 10.3.3.0 255.255.255.0 192.168.10.2

【Router2】

Router(config)#hostname Router2
Router2(config)#interface fastEthernet 0/0
Router2(config-if)#ip address 192.168.10.2 255.255.255.0
Router2(config-if)#no shutdown
Router2(config-if)#exit
Router2(config)#interface fastEthernet 0/1
Router2(config-if)#ip address 192.168.20.1 255.255.255.0
Router2(config-if)#no shutdown
Router2(config-if)#exit

#スタティックルートの設定
Router2(config)#ip route 10.1.1.0 255.255.255.0 192.168.10.1
Router2(config)#ip route 10.3.3.0 255.255.255.0 192.168.20.2

【Router3】

Router(config)#hostname Router3
Router3(config)#interface fastEthernet 0/0
Router3(config-if)#ip address 192.168.20.2 255.255.255.0
Router3(config-if)#no shutdown
Router3(config-if)#exit
Router3(config)#interface fastEthernet 0/1
Router3(config-if)#ip address 10.3.3.1 255.255.255.0
Router3(config-if)#no shutdown
Router3(config-if)#exit

#スタティックルートの設定
Router3(config)#ip route 10.1.1.0 255.255.255.0 192.168.20.1

ルータ設定確認

ルータの設定がどうなっているかの確認を各ルータでコマンドを実行して確認します。

【Router1】

Router1#show ip route
Codes: L - local, C - connected, S - static, R - RIP, M - mobile, B - BGP
D - EIGRP, EX - EIGRP external, O - OSPF, IA - OSPF inter area
N1 - OSPF NSSA external type 1, N2 - OSPF NSSA external type 2
E1 - OSPF external type 1, E2 - OSPF external type 2, E - EGP
i - IS-IS, L1 - IS-IS level-1, L2 - IS-IS level-2, ia - IS-IS inter area
* - candidate default, U - per-user static route, o - ODR
P - periodic downloaded static route

Gateway of last resort is not set

10.0.0.0/8 is variably subnetted, 3 subnets, 2 masks
C 10.1.1.0/24 is directly connected, FastEthernet0/0
L 10.1.1.1/32 is directly connected, FastEthernet0/0
S 10.3.3.0/24 [1/0] via 192.168.10.2
192.168.10.0/24 is variably subnetted, 2 subnets, 2 masks
C 192.168.10.0/24 is directly connected, FastEthernet0/1
L 192.168.10.1/32 is directly connected, FastEthernet0/1

Router1#show ip interface brief

Interface IP-Address OK? Method Status Protocol

FastEthernet0/0 10.1.1.1 YES manual up up

FastEthernet0/1 192.168.10.1 YES manual up up

Vlan1 unassigned YES unset administratively down down

【Router2】

Router2#show ip route

Codes: L - local, C - connected, S - static, R - RIP, M - mobile, B - BGP

D - EIGRP, EX - EIGRP external, O - OSPF, IA - OSPF inter area

N1 - OSPF NSSA external type 1, N2 - OSPF NSSA external type 2

E1 - OSPF external type 1, E2 - OSPF external type 2, E - EGP

i - IS-IS, L1 - IS-IS level-1, L2 - IS-IS level-2, ia - IS-IS inter area

* - candidate default, U - per-user static route, o - ODR

P - periodic downloaded static route

Gateway of last resort is not set

10.0.0.0/24 is subnetted, 2 subnets

S 10.1.1.0/24 [1/0] via 192.168.10.1

S 10.3.3.0/24 [1/0] via 192.168.20.2

192.168.10.0/24 is variably subnetted, 2 subnets, 2 masks

C 192.168.10.0/24 is directly connected, FastEthernet0/0

L 192.168.10.2/32 is directly connected, FastEthernet0/0

192.168.20.0/24 is variably subnetted, 2 subnets, 2 masks

C 192.168.20.0/24 is directly connected, FastEthernet0/1

L 192.168.20.1/32 is directly connected, FastEthernet0/1

Router2#show ip interface brief

Interface IP-Address OK? Method Status Protocol

FastEthernet0/0 192.168.10.2 YES manual up up

FastEthernet0/1 192.168.20.1 YES manual up up

Vlan1 unassigned YES unset administratively down down

【Router3】

Router3#show ip route
Codes: L - local, C - connected, S - static, R - RIP, M - mobile, B - BGP
D - EIGRP, EX - EIGRP external, O - OSPF, IA - OSPF inter area
N1 - OSPF NSSA external type 1, N2 - OSPF NSSA external type 2
E1 - OSPF external type 1, E2 - OSPF external type 2, E - EGP
i - IS-IS, L1 - IS-IS level-1, L2 - IS-IS level-2, ia - IS-IS inter area
* - candidate default, U - per-user static route, o - ODR
P - periodic downloaded static route

Gateway of last resort is not set

10.0.0.0/8 is variably subnetted, 3 subnets, 2 masks
S 10.1.1.0/24 [1/0] via 192.168.20.1
C 10.3.3.0/24 is directly connected, FastEthernet0/1
L 10.3.3.1/32 is directly connected, FastEthernet0/1
192.168.20.0/24 is variably subnetted, 2 subnets, 2 masks
C 192.168.20.0/24 is directly connected, FastEthernet0/0
L 192.168.20.2/32 is directly connected, FastEthernet0/0

Router3#show ip interface brief
Interface IP-Address OK? Method Status Protocol
FastEthernet0/0 192.168.20.2 YES manual up up
FastEthernet0/1 10.3.3.1 YES manual up up
Vlan1 unassigned YES unset administratively down down

疎通確認

PC1からPC2へPING疎通を確認します。

PC1からPC2へTracertコマンドを確認します。

参考書籍

書籍名：ネットワーク超入門　手を動かしながら学ぶIPネットワーク
著者：GENE
出版社：技術評論社

［ネットワーク超入門］手を動かしながら学ぶIPネットワーク [ Gene ]

価格: 2860 円
楽天で詳細を見る

2024-10-27

書籍「ネットワーク超入門(著 GENE) 」演習④ VLAN

Cisco Packet Tracer ネットワーク超入門

ネットワーク技術力向上のために購入した書籍「ネットワーク超入門」内の演習をCisco Packet Tracer で実際に構築してみました。

今回の演習で扱ったVLANは、

1つの物理的なイーサネットネットワークの中に複数の論理的なネットワークを構成する技術です。

VLAN（Virtual LAN）を使用することで、

ネットワーク内のブロードキャスト範囲を分割でき、

セキュリティやネットワーク効率を向上させることが可能です。

この演習で各デバイスが所属するネットワークを論理的に分割することで、

異なるネットワーク間での通信制御を行うプロセスを体験しました。

実際に手を動かすことで机上での知識が現実的な理解に結びつき、

確かな技術として定着していくのを感じています。

演習

演習概要

演習タイトル：VLAN

掲載ページ：P.108-118

演習概要：L2SWを3台使用して、２つの独立したネットワークを構築する

各機器の配置と配線

【ネットワーク図】

【PC1】から【PC4】

【Switch1】から【Switch3】

【ネットワークIPアドレス表】

ネットワーク設定

【PC1】から【PC4】

全PCに同じセグメント（192.168.1.0 / 24）のIPアドレスを設定します。

VLANを設定しなければすべての各PC間の通信は可能です。

【Switch1】

Switch>enable
Switch#configure terminal
Switch(config)#vlan 10
Switch(config-vlan)#exit
Switch(config)#interface fastEthernet 0/1
Switch(config-if)#switchport mode access
Switch(config-if)#switchport access vlan 10
Switch(config-if)#exit

Switch(config)#vlan 20
Switch(config-vlan)#exit
Switch(config)#interface fastEthernet 0/2
Switch(config-if)#switchport mode access
Switch(config-if)#switchport access vlan 20
Switch(config-if)#exit

Switch(config)#interface fastEthernet 0/3
Switch(config-if)#switchport mode trunk

【Switch2】

Switch>enable
Switch#configure terminal
Switch(config)#vlan 10
Switch(config-vlan)#exit
Switch(config)#interface fastEthernet 0/1
Switch(config-if)#switchport mode trunk

Switch(config)#vlan 20
Switch(config-vlan)#exit
Switch(config)#interface fastEthernet 0/2
Switch(config-if)#switchport mode trunk

【Switch3】

Switch(config)#vlan 20
Switch(config-vlan)#exit
Switch(config)#interface fastEthernet 0/2
Switch(config-if)#switchport mode access
Switch(config-if)#switchport access vlan 20
Switch(config-if)#exit

Switch(config)#interface fastEthernet 0/3
Switch(config-if)#switchport mode trunk

疎通確認

PC0から各端末へのPING疎通を確認する

　①PC0（192.168.1.10）からPC1（192.168.1.20）へPING
　②PC0（192.168.1.10）からPC2（192.168.1.30）へPING
　③PC0（192.168.1.10）からPC3（192.168.1.40）へPING

VLAN10を設定しているPC2(192.168.1.30)のみPING疎通ができています！

参考書籍

書籍名：ネットワーク超入門　手を動かしながら学ぶIPネットワーク
著者：GENE
出版社：技術評論社

［ネットワーク超入門］手を動かしながら学ぶIPネットワーク [ Gene ]

価格: 2860 円
楽天で詳細を見る

2024-10-20

【ネットワーク解決】ネットワークトラブルのフローチャート（Windows版）

トラブル解決

ネットワークトラブルで問題解決のフローチャート（文章のみ）を作成しました。

如何に迅速に解決するかがみそですからね。

きれいにレイヤ順にしたかったのですが、いまいち順番になっていませんが。

フローチャート
詳細

フローチャート

①ネットワーク設定が無効になっていないか

　【確認方法】
ifconfig 　⇒　ネットワーク情報が表示されない場合は無効になっている

②PING /t をルータ（ハブ）に実行してスイッチのポートが点滅するか

③宛先MACアドレスが取得できているか（ICMP応答を拒否しているPCの場合）
　ARPキャッシュ　メモリ上に一定時間保持される。WindowsOSは2分間保持
　以下コマンドにて、PING応答がなくともARPテーブルに残っていたら宛先NICまで到達はできている
　【確認方法】
　　arp -d *　⇒　ping IPアドレス　⇒　arp -a
　【備考】レイヤ2スイッチはMACアドレステーブルは3分間情報保持

④レイヤー3　PING応答
　//ネットワーク設定ができているか
　PING 自分のIPアドレス

　//DNSサーバへの通信ができるか
　PING DNSのIPアドレス

　//ゲートウェイへの通信ができるか
　PING デフォルトゲートウェイのIPアドレス
⑤レイヤー4　宛先のアプリケーションポートに到達できるか
⑥レイヤー5以降　IPアドレス、ポート、実行プログラムの調査

詳細

各レイヤ毎の確認方法を記載します。

レイヤー3　PING

・WindowsのPING　　ICMPのPING
・psping.exe 　　ICMPではないPING
　PsPing - Sysinternals | Microsoft Learn

　ICMP がブロックされている場合に有効

・PINGエラー一覧
「要求がタイムアウトしました」
　相手から応答がない場合に表示される。
　割当されていないIPアドレスにPINGしたときに表示される。
「宛先ネットワークに到達できません」
　・タイプ3/コード0　Network Unreachable
　　ルーティングエントリがない場合
　・タイプ3/コード13　Communication administratively prohibited by filtering
　　ルータやファイヤーウォールのフィルタリングルールによるパケット拒否の場合　
「宛先ホストに到達できません」
　・タイプ3/コード1　Host Unreachable
　　ルーティングエントリは存在するが、ネクストホップが動作していない場合

■Ping Sweep 指定アドレス範囲にPING実行、実行端末が分かる
nmap -sn -PE 192.168.0.0/24

レイヤー3　Tracert

PINGと同じICMPプロトコルを使用しているTracertコマンドについてまとめます。

【Windows】
・コマンド名　tracert
・使用プロトコル　ICMP

途中からずっとアスタリスクが表示され、30番目まで到達してしまう場合は、途中からTTL expired が戻ってきてないことを意味

レイヤー4以上

接続元から確認

■Powershell
Test-NetConnection -ComputerName [コンピュータ名] -Port [ポート番号] 　　
Test-NetConnection -ComputerName 192.168.24.20 -Port 22

Test-NetConnection -ComputerName [コンピュータ名] -CommonTCPPort [HTTP,RDP,SMB,WINRM]
例　Test-NetConnection -ComputerName 192.168.24.20 -CommonTCPPort http

■コマンドプロンプト
　telnet IPアドレスポート番号

　　'文字が表示されるようになる
　　set localecho
　　・画面がクリアされたら接続OK

■portqry
　portqry -n IPアドレス -e ポート番号 -p UDP
　「TCP」「UDP」のポート確認可能
　https://www.microsoft.com/en-us/download/confirmation.aspx?id=17148

■nmap
　ZenmapはGUIでコマンドを打つことが可能

受け手（接続先）から確認

■コマンドプロンプト

IPアドレスがどのポートを使用しているか特定
　netstat -ano | findstr 該当IPアドレス

　　a-> すべての接続を表示する。

　　o-> PIDを表示する。

　　n-> アドレスとポート番号を数値形式で表示する。

　netstat -anbp tcp | findstr 該当IPアドレス

■Powershell

　netstat -ano | Select-String 該当IPアドレス

レイヤー5

プロセス確認

・PID（プロセスID）からexeを特定
　①PIDを特定
　　netstat -nao | find "port番号"
　②PIDよりEXE
　　tasklist /svc | findstr PIDナンバー
　　tasklist /fi "pid eq PIDナンバー"
　③PIDよりEXE
　　taskkill /F /PID PIDナンバー
・接続に使用されたEXEを特定
　netstat -anb

・IPアドレスとポートと使用プログラム表示
　Ping Sweep　指定アドレス範囲にPING実行、実行端末表示
　　nmap -sn -PE 192.168.0.0/24
　Port Sweep　ポートスキャン
　　nmap -sV -O IPアドレス
　　「OPEN」・・・そのポートがLISTEN中
　　「CLOSE」・・・そのポートが開いているが待ち受けしているPGがない状態
　　Not shown: 998 filtered ports・・・998ポートが閉じられているという意味