自从我把一台旧笔记本电脑改造成家庭服务器后，我就一直在寻找可以自托管的应用程序。很多年前，我偶然发现了Nextcloud，并立刻被它所承诺的一切及其所代表的理念所吸引。在查阅了一些在线指南后，我成功地在Ubuntu上设置好了它。这些年来，这个过程变得越来越容易和流畅。

我很快就放弃了Google Photos，开始使用本地的Nextcloud实例来备份我所有的照片和视频。在家庭网络内，通过网页界面访问服务器非常简单，但如果你想在外出时访问它，则需要额外几步操作。这对我来说很重要，因为我希望在旅行时也能备份我的媒体文件。所以我设置了端口转发，并允许从公共互联网访问Nextcloud界面。我采取了足够的措施来确保服务器的安全，比如启用了双因素认证，并且只允许从特定的域名访问（这一点非常重要，后面会详细说明！）。然而，我最近了解到，这样的措施可能不足以保护Nextcloud本身存在的零日漏洞。如今推荐的方法是通过自托管的VPN（如WireGuard）来设置访问家庭服务器。

虽然我最终采用了VPN解决方案，但我还是很好奇，有没有人曾经试图黑入我的服务器？不仅如此，还有自动化机器人24/7地进行端口扫描和网络爬虫工作。它们有没有试图访问我公开暴露的端口？什么数据能帮助回答这些问题？幸运的是，答案并不遥远。我们只需要日志！

当我第一次设置服务器时，我启用了级别为1:INFO的日志记录。这意味着用户登录（成功或失败）以及额外的警告、错误等都记录在一个JSON文件中。这个文件可能会变得相当大，在我的情况下，两年的数据量达到了约2GB。由于我想进行快速数据分析，我决定使用DuckDB和Python。相关的Jupyter笔记本（不包含日志）可以在这个仓库中找到。虽然文章接下来的部分将重点介绍Nextcloud日志的具体细节，但同样的思路可以适应任何JSON文件。由于JSON是服务器日志的一种流行格式，因此学习如何高效地查询JSON数据在数据科学和工程领域可能是一项非常有价值的技能。

前提条件

下面的代码示例是在Jupyter笔记本中运行的。DuckDB可以通过pip安装：pip install duckdb

Nextcloud的JSON文件可能包含不完整的行，这会在解析时导致问题。因此，我们首先需要修复这个文件。下面的代码处理有效的JSON对象，然后将它们写回一个新文件。

valid_entries = []
with open("nextcloud.log.json", "r", encoding="utf-8") as f:
    for line in f:
        try:
            # Process each line as a JSON object
            valid_entries.append(json.loads(line))  
        except json.JSONDecodeError:
            print("Skipping malformed line")
# Save the repaired JSON
with open("fixed_nextcloud.log.json", "w", encoding="utf-8") as f:
    json.dump(valid_entries, f, separators=(',', ':'))

摄取和过滤数据

在开始读取日志数据之前，先看一下数据模式会很有帮助。各个字段的具体含义可以在官方文档中找到。message字段记录事件信息。事件可以由各种操作引起。因此，失败的用户登录尝试会被记录为字符串“Login failed”。

让我们解析文件，看看是否有任何未经授权的登录尝试。请注意，我已经排除了我和我的伴侣的用户ID。我们也只读取以下字段：time（时间）、remoteAddr（远程地址）、message（消息）。

json_input = duckdb.read_json("fixed_nextcloud.log.json")
login_errors = duckdb.sql("""
                       SELECT time, remoteAddr, message
                       FROM json_input
                       WHERE message LIKE '%Login failed%'
                       AND message NOT LIKE '%vnegi10%'
                       AND message NOT LIKE '%mdash%'
                       --- LIMIT 10
                           """)

看来有人试图使用“admin”账号登录。使用免费的IP地址查询工具，发现该地址位于台湾。幸好，我没有看到更多此类尝试访问服务器的行为。呼！

记得我一开始提到了受信任域的安全设置吗？那确保了只有来自特定IP地址或域名的登录才被允许。因此，失败的登录尝试会在事件消息中添加“Trusted domain error”（受信任域错误）的文本。此外，userAgent字段会记录客户端（通常是网页浏览器、移动应用或API）在HTTP请求头中发送的数据，以便向服务器标识自己。它提供了关于客户端浏览器、操作系统、设备类型和渲染引擎的详细信息。

让我们尝试下面示例中的查询：

domain_errors = duckdb.sql("""
                       SELECT time, remoteAddr, userAgent
                       FROM json_input
                       WHERE message LIKE '%Trusted domain error%'
                       LIMIT 10
                           """)

我们将查询结果限制为10条，但实际上还有更多。

对远程IP进行分组和计数

让我们尝试了解更多关于攻击者设备的信息。例如，我们可以过滤出包含字符串“Win64”的记录，这表示设备使用的是Windows操作系统。此外，我们可以将IP地址转换为DuckDB支持的INET数据类型。这在以后会有用。

gby_user_agent = duckdb.sql("""
                        SELECT CAST(remoteAddr AS INET) AS remoteAddr_inet,
                        COUNT(*) AS count
                        FROM domain_errors
                        WHERE userAgent LIKE '%Win64%'
                        GROUP BY remoteAddr
                        ORDER BY count DESC
                             """)

地理定位IP地址

仅仅有一列IP地址并不能告诉我们太多信息。如果能够将这些地址映射到它们的地理来源，那就太好了。显然，手动通过之前展示的查询工具逐个查找每个地址是不可行的。相反，我们在这里有两个选择：

1. 使用API为每个IP地址获取地理位置数据
2. 使用IP地址数据库

API服务通常会限制每分钟的调用次数，除非我们订阅付费计划。因此，我们将使用第二个选项。我找到了一个提供免费IP-城市映射数据库的服务，尽管准确性有限。但对于本文的目的来说已经足够了。如果必要，你可以随时注册付费计划以获取完整的数据库。

映射CSV文件的格式在此概述。我们将摄取该文件，然后将其与我们之前生成的分组IP地址列表进行连接。重要的是要注意，映射文件包含一系列IP地址（在ip_start和ip_end之间）。因此，我们的连接条件应该检查目标IP是否在给定的范围内。此外，本文仅关注IPv4地址。所以，我们需要确保仅过滤出ip_start为IPv4类型的那些行。对于地理定位，我们将仅使用country（国家）、stateprov（州/省）、city（城市）列。

ipv4_city = duckdb.sql("""
                SELECT * 
                FROM read_csv('dbip-city-lite-2025-02.csv',
                               columns = {
                               'ip_start': 'VARCHAR(15)',
                               'ip_end': 'VARCHAR(15)', 
                               'continent': 'VARCHAR(2)',
                               'country': 'VARCHAR(2)',
                               'stateprov': 'TEXT',
                               'city': 'TEXT',
                               'latitude': 'FLOAT',
                               'longitude': 'FLOAT'
                               }, 
                               header = False,
                               ignore_errors = true)
                WHERE ip_start LIKE '%.%'
                      """)
ipv4_city_subset = duckdb.sql("""
                        SELECT CAST(ip_start as INET) AS ip_start_inet, 
                               CAST(ip_end as INET) AS ip_end_inet,
                               country,
                               stateprov,
                               city
                        FROM ipv4_city
                        -- LIMIT 10
                            """)
gby_user_agent_city_join = duckdb.sql("""
                                SELECT gua.remoteAddr_inet,
                                       gua.count,
                                       ics.country,
                                       ics.stateprov,
                                       ics.city
                                FROM gby_user_agent gua
                                JOIN ipv4_city_subset ics
                                ON gua.remoteAddr_inet
                                BETWEEN ics.ip_start_inet AND ics.ip_end_inet
                                ORDER BY count DESC
                                """)

DuckDB能够在令人印象深刻的约6秒内执行整个查询。结果显示，大多数使用Windows操作系统的客户端似乎都位于美国。

让我们看看有多少客户端使用了python-requests库。我们运行与之前相同的查询，只是这次搜索的是%python-requests%。

与IP-城市数据库进行连接后得到以下结果。大多数基于Python的客户端的IP地址位于中国。

识别ISP（互联网服务提供商）

DB-IP.com也提供有限的免费IP-ISP数据。使用与之前展示的相同策略，我们可以解析CSV文件，并将其与之前的IP-城市结果连接，以获得一个包含ISP信息的额外列。同样地，我们只过滤出IPv4地址。

asn = duckdb.sql("""
                SELECT * 
                FROM read_csv('dbip-asn-lite-2025-02.csv',
                               columns = {
                               'ip_start': 'VARCHAR(15)',
                               'ip_end': 'VARCHAR(15)',
                               'as_number': 'INT',
                               'as_org': 'TEXT'                              
                               }, 
                               header = False,
                               ignore_errors = true)
                WHERE ip_start LIKE '%.%'
                      """)
asn_subset = duckdb.sql("""
                        SELECT CAST(ip_start as INET) AS ip_start_inet, 
                               CAST(ip_end as INET) AS ip_end_inet,
                               as_org,
                        FROM asn
                        -- LIMIT 10
                            """)
gby_user_agent_city_asn_join = duckdb.sql("""
                                SELECT gua.remoteAddr_inet,
                                       gua.count,
                                       gua.country,
                                       gua.stateprov,
                                       gua.city,
                                       asn.as_org
                                FROM gby_user_agent_city_join gua
                                JOIN asn_subset asn
                                ON gua.remoteAddr_inet
                                BETWEEN asn.ip_start_inet AND asn.ip_end_inet
                                ORDER BY count DESC
                                """)
print(gby_user_agent_city_asn_join)

DuckDB的性能

DuckDB因其极快的查询性能而近日备受欢迎。本次练习的结果也不例外。从JSON数据摄入、CSV文件解析，到最后连接IP-城市数据的完整查询过程，平均耗时8.4秒。加入IP-ISP数据连接后，时间略微延长，平均达到9.1秒。这非常令人印象深刻！

需要注意的是，这些结果是在一台配备AMD Ryzen 9 5900X处理器和32 GB内存的台式机上生成的。DuckDB由于其多线程查询执行机制，能够利用多个CPU核心。这从下面的htop工具视图中可以明显看出：

结论

在本文中，我们学习了如何使用DuckDB对Nextcloud服务器日志执行快速查询。在理解了数据架构后，我们能够提取出相关记录，展示了尝试访问我们服务器的客户端设备的有趣属性。利用免费可用的IP-城市和IP-ISP数据，我们能够将IP地址映射到其地理来源，并识别出拥有这些IP的ISP。