cat – variable.jp [データベース,パフォーマンス,運用]

egrep,awk,cut,sedコマンドによる文字列操作の見直し。
仕事でよく使うコマンドなので、改めてレビューしてみる。

セキュリティログを確認
■AND検索

AWS$cat secure-20120826 | egrep -i "Invalid user.*from" | head
Aug 19 12:56:05 aws sshd[31397]: Invalid user db2inst1 from 123.15.36.218
Aug 19 12:56:08 aws sshd[31401]: Invalid user prueba from 123.15.36.218
Aug 19 12:56:12 aws sshd[31405]: Invalid user postgres from 123.15.36.218
Aug 19 12:56:19 aws sshd[31413]: Invalid user mythtv from 123.15.36.218
Aug 19 12:56:26 aws sshd[31421]: Invalid user mmroot from 123.15.36.218
Aug 19 12:56:33 aws sshd[31429]: Invalid user x from 123.15.36.218
Aug 19 12:56:38 aws sshd[31433]: Invalid user rob from 123.15.36.218
Aug 19 12:56:47 aws sshd[31443]: Invalid user tommy from 123.15.36.218
Aug 19 12:57:12 aws sshd[31467]: Invalid user www from 123.15.36.218
Aug 19 12:57:19 aws sshd[31475]: Invalid user nagios from 123.15.36.218
AWS$

■OR検索

AWS$cat secure-20120826 | egrep -i "postgres|nagios" | head
Aug 19 12:56:12 aws sshd[31405]: Invalid user postgres from 123.15.36.218
Aug 19 12:56:12 aws sshd[31406]: input_userauth_request: invalid user postgres
Aug 19 12:57:19 aws sshd[31475]: Invalid user nagios from 123.15.36.218
Aug 19 12:57:19 aws sshd[31476]: input_userauth_request: invalid user nagios
Aug 19 13:01:23 aws sshd[31714]: Invalid user nagios from 123.15.36.218
Aug 19 13:01:23 aws sshd[31715]: input_userauth_request: invalid user nagios
Aug 19 13:10:16 aws sshd[32250]: Invalid user nagios from 123.15.36.218
Aug 19 13:10:16 aws sshd[32251]: input_userauth_request: invalid user nagios
Aug 25 06:11:34 aws sshd[4726]: Invalid user nagios from 221.13.104.162
Aug 25 06:11:34 aws sshd[4727]: input_userauth_request: invalid user nagios
AWS$

■awkにて特定列抽出

AWS$cat secure-20120826 | egrep -i "Invalid user.*from" | awk '{print $8}' | head
db2inst1
prueba
postgres
mythtv
mmroot
x
rob
tommy
www
nagios
AWS$

■awkで抽出したデータから同じ値を纏めて合計を表示して多い順に表示する。

AWS$cat secure-20120826 | egrep -i "Invalid user.*from" | awk '{print $8}' | sort | uniq -c | sort -nr
     19 oracle
     12 nagios
     10 mythtv
      7 test
      6 user0
      6 info
      5 test1
      5 backup
      4 testuser
      4 jack
      4 i-heart
[省略...]

AWS$

■awkにてIPのみ抽出

AWS$cat secure-20120826 | egrep -i "Invalid user.*from" | awk '{print $10}' | head
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218

■awkにてIPのみ抽出して合計が多い順に並べる

AWS$ cat secure-20120826 | egrep -i "Invalid user.*from" | awk '{print $10}' | sort | uniq -c | sort -nr
    174 123.15.36.218
     51 141.89.97.171
     49 221.13.104.162
      7 101.44.1.134
      4 140.120.90.196
      1 187.16.247.187
      1 183.59.9.150
AWS$

※何処からログインを試みているか念の為確認。

■awkにてwebログから日付を抽出

AWS$cat access_log-20120826 |  awk '{print $4,$5}' | head
[19/Aug/2012:03:41:49 +0900]
[19/Aug/2012:03:41:49 +0900]
[19/Aug/2012:03:41:50 +0900]
[19/Aug/2012:06:23:22 +0900]
[19/Aug/2012:06:23:22 +0900]
[19/Aug/2012:06:56:52 +0900]
[19/Aug/2012:06:56:52 +0900]
[19/Aug/2012:08:07:44 +0900]
[19/Aug/2012:08:07:44 +0900]
[19/Aug/2012:12:52:29 +0900]
AWS$

■awkにてwebログから特定の列を抽出してから、cutコマンドで特定文字のみを抜き出す。

AWS$cat access_log-20120826 |  awk '{print $4}' | head
[19/Aug/2012:03:41:49
[19/Aug/2012:03:41:49
[19/Aug/2012:03:41:50
[19/Aug/2012:06:23:22
[19/Aug/2012:06:23:22
[19/Aug/2012:06:56:52
[19/Aug/2012:06:56:52
[19/Aug/2012:08:07:44
[19/Aug/2012:08:07:44
[19/Aug/2012:12:52:29
AWS$

AWS$cat access_log-20120826 |  awk '{print $4}' | cut -c 2-12 | head
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
AWS$

■awkにてwebログから特定の列を抽出してから、cutコマンドで時間のみを抜き出す。

AWS$cat access_log-20120826 |  awk '{print $4,$5}' | cut -d: -f 2-3 | head
03:41
03:41
03:41
06:23
06:23
06:56
06:56
08:07
08:07
12:52
AWS$

■awkにて特定列の抽出後にcutにて特定範囲のみを抜き出しsed特定文字を入れ替える。

AWS$cat access_log-20120826 |  awk '{print $4}' | cut -c 2-12 | head
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
AWS$

■sedにて/をtabにて入れ替え

AWS$cat access_log-20120826 |  awk '{print $4}' | cut -c 2-12 | sed -e 's/\//\t/g' | head
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
AWS$

■awkその他集計等

[root@HOME001 log]# cat number_cout | awk '{ print $1}'
1
2
3
4
5
6
7
8
9
10
[root@HOME001 log]# cat number_cout | awk '{ sum += $1 } END { print sum }'
55
[root@HOME001 log]# cat number_cout | awk '{ sum += $1; num++ } END { print "sum = " sum; print "average = " sum/num }'         
sum = 55
average = 5.5
[root@HOME001 log]#

参考サイト：
【 sed 】文字列の置換，行の削除を行う

Tag Archives: Cat

文字列操作(egrep,awk,cut,sed)コマンドレビュー