求shell按列排序去重代码

zbs168

原始文件为一日志文件，用cat 和grep过滤指定关键字后数据格式为：
Mar 20 09:00:20 LOG xxxx: GOT INFO: ip 192.168.1.91 to client with mac 00:01:00:27:b1:a7 ab = 12 valid 100 ms
Mar 20 09:02:07 LOG xxxx: GOT INFO: ip 192.168.1.12 to client with mac 00:01:00:f5:41:ba ab = 14 bad
Mar 20 09:13:20 LOG xxxx: GOT INFO: ip 192.168.1.19 to client with mac 00:01:00:e1:78:38 ab = 15 valid 120 ms

Mar 20 09:24:39 LOG xxxx: GOT INFO: ip 192.168.1.91 to client with mac 00:01:00:27:b1:a7 ab = 12 valid 90 ms
Mar 20 09:33:37 LOG xxxx: GOT INFO: ip 192.168.1.11 to client with mac 00:01:00:f5:41:ba ab = 143 bad
Mar 20 09:35:38 LOG xxxx: GOT INFO: ip 192.168.1.12 to client with mac 00:01:00:f5:41:ba ab = 15 valid 220 ms
Mar 20 09:36:37 LOG xxxx: GOT INFO: ip 192.168.1.11 to client with mac 00:01:00:f5:41:bb ab = 13 bad



需要实现的目标是：
1.首先根据mac地址和时间排序去重，即相同mac地址不同时间的日志，只留下最后一个时间的


2.对前面3列自动补充本年的年份后转为unix时间戳
3.对最后面的状态位如果是bad的，输出bad，如果是valid的，只输出ms数
4.最后按照时间的unix时间戳进行升序排列


以以上日志为例，应该输出：
（2018/3/20 09:24:39的unix时间戳） 192.168.1.91 00:01:00:27:b1:a7 valid 90
（2018/3/20 09:35:38的unix时间戳） 192.168.1.12 00:01:00:f5:41:ba valid 220
（2018/3/20 09:36:37的unix时间戳） 192.168.1.11 00:01:00:f5:41:bb bad

wh7211

1. awk '$0!~/^$/{y=strftime("%Y",systime());m=$1=="Jan"?1:$1=="Feb"?2:$1=="Mar"?3:$1=="Apr"?4:$1=="May"?5:$1=="Jun"?6:$1=="Jul"?7:$1=="Aug"?8:$1=="Sep"?9:$1=="Oct"?10:$1=="Nov"?11:$1=="Dec"?12:"";split($3,a,":");b=mktime(""y" "m" "$2" "a[1]" "a[2]" "a[3]"");c=$18=="valid"?$18" "$19:$18;d[$14]="("b") "$9" "$14" "c}END{PROCINFO["sorted_in"]="@val_num_asc";for(i in d){print d[i]}}' 1
   
2. 输出：
   
3. (1521508400) 192.168.1.19 00:01:00:e1:78:38 valid 120
   
4. (1521509079) 192.168.1.91 00:01:00:27:b1:a7 valid 90
   
5. (1521509738) 192.168.1.12 00:01:00:f5:41:ba valid 220
   
6. (1521509797) 192.168.1.11 00:01:00:f5:41:bb bad

复制代码

reyleon

1. [root[url=home.php?mod=space&uid=1880512]@HK[/url] tmp]# cat urfile
   
2. Mar 20 09:00:20 LOG xxxx: GOT INFO: ip 192.168.1.91 to client with mac 00:01:00:27:b1:a7 ab = 12 valid 100 ms
   
3. Mar 20 09:02:07 LOG xxxx: GOT INFO: ip 192.168.1.12 to client with mac 00:01:00:f5:41:ba ab = 14 bad
   
4. Mar 20 09:13:20 LOG xxxx: GOT INFO: ip 192.168.1.19 to client with mac 00:01:00:e1:78:38 ab = 15 valid 120 ms
   
5. Mar 20 09:24:39 LOG xxxx: GOT INFO: ip 192.168.1.91 to client with mac 00:01:00:27:b1:a7 ab = 12 valid 90 ms
   
6. Mar 20 09:33:37 LOG xxxx: GOT INFO: ip 192.168.1.11 to client with mac 00:01:00:f5:41:ba ab = 143 bad
   
7. Mar 20 09:35:38 LOG xxxx: GOT INFO: ip 192.168.1.12 to client with mac 00:01:00:f5:41:ba ab = 15 valid 220 ms
   
8. Mar 20 09:36:37 LOG xxxx: GOT INFO: ip 192.168.1.11 to client with mac 00:01:00:f5:41:bb ab = 13 bad
   
9. [root@hk tmp]#
   
10. [root@hk tmp]# awk '{
    
11. >     cmd=sprintf("date -d \"%s %s %s\" \"+%%s\"",$1,$2,$3);
    
12. >     cmd|getline t;
    
13. >     close(cmd);
    
14. >     if(!a[$14]++)b[++n]=$14;
    
15. >     v=($18=="valid")?$18" "$19:$18;
    
16. >     c[$14]=t" "$9" "$14" "v
    
17. > }
    
18. >
    
19. > END{
    
20. >     for(i=1;i<=n;i++)print c[b[i]]
    
21. > }' urfile | sort -k1,1n
    
22. 1521508400 192.168.1.19 00:01:00:e1:78:38 valid 120
    
23. 1521509079 192.168.1.91 00:01:00:27:b1:a7 valid 90
    
24. 1521509738 192.168.1.12 00:01:00:f5:41:ba valid 220
    
25. 1521509797 192.168.1.11 00:01:00:f5:41:bb bad
    
26. [root@hk tmp]#
    
27. [root@hk tmp]#

复制代码

reyleon

1. awk '{
   
2.     cmd=sprintf("date -d \"%s %s %s\" \"+%%s\"",$1,$2,$3);
   
3.     cmd|getline t;
   
4.     close(cmd);
   
5.     if(!a[$14]++)b[++n]=$14;
   
6.     v=($18=="valid")?$18" "$19:$18;
   
7.     c[$14]=t" "$9" "$14" "v
   
8. }
   
9. 
   
10. END{
    
11.     for(i=1;i<=n;i++)print c[b[i]]
    
12. }' urfile | sort -k1,1n

复制代码

zbs168

结贴，感谢楼上两位大牛实地跑数据测试得知：

2楼的wh7211
real        0m0.023s
user        0m0.013s
sys        0m0.010s


3楼的reyleon：
real        0m5.825s
user        0m0.023s
sys        0m0.163s

zbs168

谢谢

wh7211

回复 6# zbs168


<<< awk4.0
优化了一下，只对去重之后的行做处理：

1. awk '$0!~/^$/{a[$14]=$0}END{for(i in a){split(a[i],b,"[: ]+");y=strftime("%Y",systime());m=b[1]=="Jan"?1:b[1]=="Feb"?2:b[1]=="Mar"?3:b[1]=="Apr"?4:b[1]=="May"?5:b[1]=="Jun"?6:b[1]=="Jul"?7:b[1]=="Aug"?8:b[1]=="Sep"?9:b[1]=="Oct"?10:b[1]=="Nov"?11:b[1]=="Dec"?12:"";c=mktime(""y" "m" "b[2]" "b[3]" "b[4]" "b[5]"");d=b[25]=="valid"?c+b[26]:b[25];e[i]="("c") "b[11]" "b[16]":"b[17]":"b[18]":"b[19]":"b[20]":"b[21]" "d};PROCINFO["sorted_in"]="@val_num_asc";for(j in e){print e[j]}}' 1
   
2. 输出：
   
3. (1521508400) 192.168.1.19 00:01:00:e1:78:38 1521508520
   
4. (1521509079) 192.168.1.91 00:01:00:27:b1:a7 1521509169
   
5. (1521509738) 192.168.1.12 00:01:00:f5:41:ba 1521509958
   
6. (1521509797) 192.168.1.11 00:01:00:f5:41:bb bad

复制代码

zbs168

论坛秀逗了，请看第10楼

zbs168

论坛秀逗了，请看第10楼

zbs168

回复 7# wh7211

谢谢优化，但是有个问题Mar 20 09:24:39 LOG xxxx: GOT INFO: ip 192.168.1.91 to client with mac 00:01:00:27:b1:a7 ab = 12 valid 90 ms
例如这行中的mac：00:01:00:27:b1:a7，实际为可变长度，可能是6位，也可能是12位，也可能是任意位
我用

1. awk '$0!~/^$/{y=strftime("%Y",systime());m=$1=="Jan"?1:$1=="Feb"?2:$1=="Mar"?3:$1=="Apr"?4:$1=="May"?5:$1=="Jun"?6:$1=="Jul"?7:$1=="Aug"?8:$1=="Sep"?9:$1=="Oct"?10:$1=="Nov"?11:$1=="Dec"?12:"";split($3,a,":");b=mktime(""y" "m" "$2" "a[1]" "a[2]" "a[3]"");c=$18=="valid"?$18" "$19+b:$18;d[$14]=b" "$9" "$14" "c}END{PROCINFO["sorted_in"]="@val_num_asc";for(i in d){print d[i]}}' 1
   

复制代码

得到的数据是：
1521508400 192.168.1.19 00:01:00:e1:78:38 valid 1521508520
1521509079 192.168.1.91 00:01:00:27:b1:a7 valid 1521509169
1521509738 192.168.1.12 00:01:00:f5:41:ba valid 1521509958
1521509797 192.168.1.11 00:01:00:f5:41:bb bad



同时，新问题是：
我每次处理完后会把结果用>>汇总到文件a.txt里面，每隔5分钟跑一次，我想每次汇总完后对a.txt进行二次过滤，过滤要求是：
1.根据mac进行排序，相同mac重复的数据只保留日期最近的一条数据，不同mac无重复的数据保留
2.获取当前的unix时间戳
3.将最后一列的数值与第2步得到的unix时间戳对比，如果小于当前unix时间戳，就抛弃该行，表示过期
4.最后还是按照第一列进行升序排列