使用AWK统计Apache日志中特定URL的IP访问次数失败求助

要统计Apache access_log中以shop/product?traffic=ads开头的URL的IP访问次数，但原有AWK代码无法得到结果，原因是目标URL带有动态参数，且部分目标URL出现在Referer字段中。

原AWK代码 #

awk -F'[ "]+' '$7 == "/shop/product?traffic=ads" { ipcount[$1]++ }
END { for (i in ipcount) {
    printf "%15s - %d\n", i, ipcount[i] } }' /var/www/vhosts/domain.com/logs/access_ssl_log

日志示例 #

66.249.68.xx- - [19/Dec/2022:09:14:15 +0100] "GET /shop/other-product/1.0" 404 16996 "-" "Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.5304.xxx Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
109.42.242.xxx - - [19/Dec/2022:09:14:55 +0100] "GET /shop/product?traffic=ads&gclid=Cj0KCQiAtICdBhCLARIsALUBFcFMmvFbA_1EyTTMRDp9IWhDXFA_HCeuEsIBXl886PoaAapen2KdussaAniSEALw_wcB HTTP/1.0" 200 30589 "https://www.google.com/" "Mozilla/5.0 (Linux; Android 11; SM-A515F) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Mobile Safari/537.36"
80.187.75.xx - - [20/Dec/2022:06:40:12 +0100] "GET /shop/product HTTP/1.0" 200 10821 "https://www.example.com/shop/product?traffic=ads&gclid=EAIaIQobChMIg_Ks5vWF_AIVAgGLCh3k_gBKEAAYASAAEgKBOfD_BwE&dt=1671461107791" "Mozilla/5.0 (iPhone; CPU iPhone OS 16_0_2 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.0 Mobile/15E148 Safari/604.1"

问题分析 #

1. 原代码使用精确匹配$7 == "/shop/product?traffic=ads"，但实际URL带有gclid、dt等动态参数，无法匹配；
2. 忽略了部分请求的目标URL存在于Referer字段（$11）的情况，比如第三条日志中，请求路径是/shop/product，但Referer才是目标链接。

修正后的AWK代码 #

awk -F'[ "]+' '
# 匹配请求路径以目标URL开头，或Referer包含目标URL
($7 ~ "^/shop/product\\?traffic=ads" || $11 ~ "shop/product\\?traffic=ads") {
    # 确定要统计的目标URL：如果是请求路径匹配则取$7，否则取Referer字段$11
    target_url = ($7 ~ "^/shop/product\\?traffic=ads") ? $7 : $11
    # 用IP+URL作为数组键，统计次数
    count[$1, target_url]++
}
END {
    # 遍历数组输出指定格式
    for (key in count) {
        split(key, arr, SUBSEP)
        printf "%d Clicks from %s to %s\n", count[key], arr[1], arr[2]
    }
}' /var/www/vhosts/domain.com/logs/access_ssl_log

输出结果 #

1 Clicks from 80.187.75.xx to https://www.example.com/shop/product?traffic=ads&gclid=EAIaIQobChMIg_Ks5vWF_AIVAgGLCh3k_gBKEAAYASAAEgKBOfD_BwE&dt=1671461107791
1 Clicks from 109.42.242.xxx to /shop/product?traffic=ads&gclid=Cj0KCQiAtICdBhCLARIsALUBFcFMmvFbA_1EyTTMRDp9IWhDXFA_HCeuEsIBXl886PoaAapen2KdussaAniSEALw_wcB

（注：示例日志中109.42.242.xxx仅出现1次，所以输出为1次，若实际日志中有多次则会累加）

内容的提问来源于stack exchange，提问作者nils50122