如何通过AWS Logs Insights计算各resolverIp的NXDOMAIN查询占比？

完全可以实现，无需分开执行两次查询，单条Logs Insights语句就能直接算出每个resolverIp的NXDOMAIN查询占总查询数的百分比，具体语句如下：

stats 
  count(*) as total_queries,
  count_if(responseCode="NXDOMAIN") as nxdomain_queries
by resolverIp
| calculate nxdomain_percentage = (nxdomain_queries / total_queries) * 100
| sort nxdomain_percentage desc

语句说明： #

• stats count(*) as total_queries：统计每个resolverIp的总DNS查询数，用别名total_queries简化后续计算
• count_if(responseCode="NXDOMAIN") as nxdomain_queries：仅统计返回NXDOMAIN的查询数，别名nxdomain_queries
• calculate nxdomain_percentage = ...：用NXDOMAIN查询数除以总数再乘以100，得到百分比结果
• sort nxdomain_percentage desc：按占比从高到低排序，方便快速定位异常IP

如果需要控制百分比的小数位数，可以用round函数优化：

stats 
  count(*) as total_queries,
  count_if(responseCode="NXDOMAIN") as nxdomain_queries
by resolverIp
| calculate nxdomain_percentage = round((nxdomain_queries / total_queries) * 100, 2)
| sort nxdomain_percentage desc

这里的2表示保留两位小数，可根据需求调整数值。

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