Data Wrangling-数据整理
学习资料
我们经常都会查看日志-log，去查看进程信息，排查错误
Linux 系统都会运行一个常规的日志守护程序，它所有的输出称之为系统日志
journalctl
journalctl command 允许我们查看 system log
$ journalctl # 打印此时的系统日志，这可以 backup 到 1月1日 or further
$ journalctl | grep ssh # 利用 grep 过滤信息，发现不断有人连接与断开与该服务器的 ssh 连接
Aug 29 03:49:42 VM-16-14-ubuntu sshd[23841]: Connection closed by authenticating user root 174.92.44.128 port 57460 [preauth]
Aug 29 03:49:43 VM-16-14-ubuntu sshd[23850]: Failed password for invalid user znc from 120.48.25.206 port 45426 ssh2
Aug 29 03:49:43 VM-16-14-ubuntu sshd[23850]: Received disconnect from 120.48.25.206 port 45426:11: Bye Bye [preauth]
Aug 29 03:49:43 VM-16-14-ubuntu sshd[23850]: Disconnected from invalid user znc 120.48.25.206 port 45426 [preauth]
$ ssh root@xx.xx.xx ls # 从 host machine 发送 ls 命令到 xx.xx.xx 机器上执行 ls 命令并将结果通过 Internet 返回到 host
$ ssh root@xx.xx.xx journalctl | grep ssh | grep "Disconnected from" # 该命令会将整个 systemlog 发送到本地执行两次 grep 过滤
# ssh 到 第一个 pipe 符号之间的内容会将命令送往 remote machine
$ ssh root@xx.xx.xx 'journalctl | grep ssh | grep "Disconnected from"' | less # 效果会是将在 remote machine 的 shell 中执行字符串中的命令，将结果返回，并使用 less 命令查看， less 命令的效果类似 vim、journalctl，它会打开 new page，and navigate the bottom of the page
sed
sed 是一个 stream editor，可以将当前 input stream 放入缓冲区进行处理,处理结果返回给屏幕处理
sed 可以配合正则表达式使用
$ cat xx.log | sed 's/.*Disconnected from //' | less # . 匹配非换行符的所有字符； * 匹配前面的子表达式 0 或 多次；+ 匹配前面的子表达式 1 次或 多次
$ echo 'abc' | sed 's/[ab]//' # [ab]匹配 square bracket 中的 a 或 b 字符
bc
$ echo 'adbce' | sed 's/[ab]//g' # g modifier 将会令 sed 扫描每个字符，判断该字符起始是否符合 replace expression，上面的无 g 修饰的表达式，匹配到一次将不会继续进行匹配
dce
# 注意 -E 表示将后面的()转义掉，不将()作为匹配的一部分，如果不使用 -E 参数，则需要 sed 's/\(ab\)//g'
$ echo 'abcaba' | sed -E 's/(ab)//g' # (ab) pattern 意味着 同时满足 first character is a and 第二个字符是 b，(ab)* 意味着不断匹配 ab 字符串
ca
$ echo 'abcabac' | sed -E 's/(ab|ba)//g'
cc
$ echo 'ababcaba' | sed -E 's/(ab)*//'
(abab)caba -> caba
$ echo 'Discon Discon Discon abc' | sed -E 's/.*Discon//' # 不停匹配以 Discon 结尾的字符串
abc
示例文本：
$ ssh root@42.192.7.119 journalctl | grep 'Disconnected from' > text.txt
Aug 29 03:31:30 VM-16-14-ubuntu sshd[20541]: Disconnected from invalid user oracle 167.99.231.236 port 47480 [preauth]
Aug 29 03:31:31 VM-16-14-ubuntu sshd[20546]: Disconnected from invalid user tiago 128.199.102.212 port 60108 [preauth]
Aug 29 03:32:42 VM-16-14-ubuntu sshd[20759]: Disconnected from invalid user teacher 120.48.25.206 port 48512 [preauth]
Aug 29 03:32:52 VM-16-14-ubuntu sshd[20780]: Disconnected from invalid user virl 101.78.9.237 port 42376# 末尾无空格
Aug 29 03:34:12 VM-16-14-ubuntu sshd[21014]: Disconnected from invalid user virl 111.67.207.25 port 49672 # 末尾有空格
Aug 29 03:35:07 VM-16-14-ubuntu sshd[21177]: Disconnected from invalid user nginx 167.99.231.236 port 59838 [preauth]
$ cat test.txt | sed -E 's/^.*Disconnected from (invalid )?user .* [0-9.]+ port [0-9]+ $//g'
# 匹配到的是
Aug 29 03:34:12 VM-16-14-ubuntu sshd[21014]: Disconnected from invalid user virl 111.67.207.25 port 49672 # 末尾有空格
# ^ 以xxx 字符串为开头, $ 以 xxx 字符串结尾，这里 $ 与 ^ 一起用便是指定了这个表达式为匹配的整行字符串，而不是只匹配一部分就好了；? 表示前面这个字符或者 capture group ()可以有可以无
$ cat test.txt | sed -E 's/.*Disconnected from (invalid )?user .* [0-9.]+ port [0-9]+ //g'
[preauth]
[preauth]
[preauth]
Aug 29 03:32:52 VM-16-14-ubuntu sshd[20780]: Disconnected from invalid user virl 101.78.9.237 port 42376# 末尾无空格
[preauth]
[preauth]
[preauth]
[preauth]
[preauth]
[preauth]
[preauth]
[preauth]
[preauth]
[preauth]
[preauth]
[preauth]
[preauth]
[preauth]
[preauth]
$ cat test.txt | sed -E 's/.*Disconnected from (invalid )?user .* [0-9.]+ port [0-9]+ \[preauth\]$//g' # 什么时候转义字符，真的有点儿拧不清楚了
Aug 29 03:32:52 VM-16-14-ubuntu sshd[20780]: Disconnected from invalid user virl 101.78.9.237 port 42376# 末尾无空格
Aug 29 03:34:12 VM-16-14-ubuntu sshd[21014]: Disconnected from invalid user virl 111.67.207.25 port 49672 #末尾有空格
$ cat test.txt | sed -E 's/.*Disconnected from (invalid )?user .* [0-9.]+ port [0-9]+ (\[preauth\])?$//g' # 谨记 ? 是对前面一组()圆括号里面的内容生效 或者 前面一个字符生效的
Aug 29 03:32:52 VM-16-14-ubuntu sshd[20780]: Disconnected from invalid user virl 101.78.9.237 port 42376
# () 是一个 capture group, 如下所示，当 sed -E 's/。。。/\2/g' 其中 \2 表示将每一行前面匹配到的字符串都替换为前面字符串中的第2个 capture group
$ cat test.txt | sed -E 's/.*Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?$/\2/g'
oracle
tiago
teacher
Aug 29 03:32:52 VM-16-14-ubuntu sshd[20780]: Disconnected from invalid user virl 101.78.9.237 port 42376
virl
nginx
alex
nginx
oracle
origin
Robert
ftpuser
ts3
ts3
minecraft
sinus
botuser
deployer
oracle
pp
注意：
() is a 捕获组，这些 patrenthsis 将不会对 matching 做任何事情
/.*Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?/gm # g - global: don't return after first match; m - multi line: ^ and $ match start/end of the file
# .* 中的 * ：matches the previous token between zero and unlimited times, as many times as possible, giving back as needed (greedy)，* 是贪婪的，尽可能多的匹配
图示：
.*Disconnected from #是匹配到 2 号位置为止
对比
/.*?Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?/gm
# .*? 中的 *? :matches the previous token between zero and unlimited times, as few times as possible, expanding as needed (lazy) - 懒惰的，尽可能少的去匹配
.*?Disconnected from # 匹配到的位置是 1 号位置
wc
wc is to count number of lines
$ cat test.txt | sed -E 's/.*Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?$/\2/g' | wc -l
20
sort
sort 需要一些输入，然后对他们进行排序，随后打印到输出
uniq
可以结合 uniq
cat test.txt | sed -E 's/.*Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?$/\2/g' | sort |uniq -c # 第 1 列，是重复次数； uniq -c 消除重复行
1 Aug 29 03:32:52 VM-16-14-ubuntu sshd[20780]: Disconnected from invalid user virl 101.78.9.237 port 42376
1 Robert
1 alex
1 botuser
1 deployer
1 ftpuser
1 minecraft
2 nginx
3 oracle
1 origin
1 pp
1 sinus
1 teacher
1 tiago
2 ts3
1 virl
# sort -nk1,1 - n表示按 numeric 数字排序，k 表示以空格分开的列(a white space sperated column)分为1-多列 and 1，1 表示从第一列排序后结束，不再根据第二列进行排序
cat test.txt | sed -E 's/.*Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?$/\2/g' | sort | uniq -c | sort -nk1,1
1 Aug 29 03:32:52 VM-16-14-ubuntu sshd[20780]: Disconnected from invalid user virl 101.78.9.237 port 42376
1 Robert
1 alex
1 botuser
1 deployer
1 ftpuser
1 minecraft
1 origin
1 pp
1 sinus
1 teacher
1 tiago
1 virl
2 nginx
2 ts3
3 oracle
cat test.txt | sed -E 's/.*Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?$/\2/g' | sort | uniq -c | sort -nk1 | tail -n10 # tail -n10 只展示最后 10 行
1 minecraft
1 origin
1 pp
1 sinus
1 teacher
1 tiago
1 virl
2 nginx
2 ts3
3 oracle
awk
awk is a column based stream processor ,它也是一种 stream editor
它更专注于这个 columnar data
$ cat test.txt | sed -E 's/.*Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?$/\2/g' | sort | uniq -c | sort -nk1 | tail -n20 | awk '{print $2}' | paste -s -d, -
# '{print $2}' 打印第二列；paste -s -d, - 其中 - 表示不输出到 file中，就以默认文件描述符 1 输出，-s 连接输入的所有行，也就是把它们变为一行，默认以 tab 分隔，可以通过 -d<list>替换
Aug,Robert,alex,botuser,deployer,ftpuser,minecraft,origin,pp,sinus,teacher,tiago,virl,nginx,ts3,oracle
$ cat test.txt | sed -E 's/.*Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?$/\2/g' | sort | uniq -c | awk '$1 == 1 && $2 ~ /^.*g$/ {print $0}'
# $1 == 1 && $2 ~ /^.*g$/ {print $0} ## $1 == 1:第一列 值为1 && $2 ~ 正则表达式，以 g 结尾，最后打印所有列
1 Aug 29 03:32:52 VM-16-14-ubuntu sshd[20780]: Disconnected from invalid user virl 101.78.9.237 port 42376
# awk 是一种编程语言
$ cat test.txt | sed -E 's/.*Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?$/\2/g' | sort | uniq -c | awk 'BEGIN {rows = 0} $1 == 1 && $2 ~ /^.*g$/ {rows += 1} END{print rows}'
# 针对每一匹配 $1 == 1 && $2 ~ /^.*g$/ {rows += 1} 模式的行，都 rows += 1，直到所有行遍历结束，类似 for ;; {}
$ cat test.txt | sed -E 's/.*Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?$/\2/g' | sort | uniq -c | awk '$1 != 1 {print $1}' | paste -s -d+ -
2+3+2
# bc program 用来计算 +-乘除
$ cat test.txt | sed -E 's/.*Disconnected from (invalid )?user (.*) [0-9.]+ port [0-9]+ (\[preauth\])?$/\2/g' | sort | uniq -c | awk '$1 != 1 {print $1}' | paste -s -d+ -| bc -l
7
