服務器取證

請根據服務器檢材，回答以下問題：

先把鏡像挨個仿真

因為是集群，虛擬機的網段改成50，看情況要不要改ip，仿真起來如果ip變了就改回去，沒變是最好的

1. node1節點的磁盤設備SHA256值前六位是？(字母全大寫，答案格式：AAAAAA)

node1對應的是第二個鏡像

結果為FC9A34

2. 集群配置了多少個node節點？（答案格式：1）

結果為2

3. 嫌疑人于什么時間修改master節點的root密碼？（使用雙位數格式，答案格式：00:00:00）

history里沒記錄時間，那么就要去找日志

結果為09:35:59

4. Docker的安裝日期是？（使用雙位數格式，答案格式：01月01日）

centos系統，看yum日志

結果為04月08日

5. Docker通過配置守護進程以使用全局代理，該代理地址的端口是？（答案格式：1）

查看守護進程的配置文件

結果為4780

6. 發卡網站使用的Mysql數據庫對外訪問端口是？（答案格式：1）

結果為30627

7. 發卡網站部署使用的鏡像名稱是？（答案格式：root/root）

東西比較多，我們應該進入面板查看，先找到面板端口30001，用https連接

問了下ai怎么找token，用命令創建一個kubectl create token dashboard-admin --namespace kube-system

登進去發現全紅

設置里加一下命名空間，現在看的還沒有終端的多

處理完看pods

重啟一個pod，看事件，發現掛載失敗，83剛好是鏡像4，看一下它的nfs服務出啥問題了

服務正常，關閉防火墻看看

防火墻一關這個pod立馬正常了

剩下兩個都重啟，但是captcha-bot還是起不來

看一下pod的日志，沒法連接上數據庫

這個端口和之前看service的端口一致，但是沒有啟動

在root目錄下能找到很多配置文件，用apply命令添加一下80的那個(因為83那臺機器里是mysql80)，再添加mysql-d（這個里面也配置的80）

完事接著重啟，但是還是起不來，不過這次報錯變了，這個地址和url，我自己主機開代理也上不去，還是先找發卡網站

找到了網站的pvc，但是網站代碼不在

通過搜索找到備份，還原一下

apply一下配置

還需要配置nginx

過了一會啟動失敗，拉不下來鏡像，一看node里根本沒有這個鏡像，master里有。。

只能把這個鏡像傳到node里了

最后再次重啟，正常啟動了

成功訪問

結果為webdevops/php-nginx

8. 當前Telegram群管機器人使用的容器ID的前六位是？（答案格式：123abc）

前面每次重啟時能夠發現id是會變的，所以仿真后的都不可信了，靜態看，這個是跑在node2上的

overlay2里直接搜

結果為8fadf5

9. 發卡網站使用的緩存數據庫是？（答案格式：mysql）

結果為redis

10. 集群中配置的發卡網站代碼運行所在的物理目錄是？（答案格式：/root/root）

結果為/data/k8s_data/default/dujiaoka

11. Telegram群管機器人配置的API代理域名是？（答案格式：www.xxx.com）

這個就是之前導致captcha-bot無法啟動的罪魁禍首

結果為kk.xilika.cc

12. 嫌疑人在Telegram上創建的群名稱是？（答案格式：比武群）

captcha-bot是啟不來了，直接看數據庫，之前配置里有root密碼

結果為西門慶交流群

13. 統計嫌疑人在Telegram上創建的群中2025年6月之后成功入群的人數為？（答案格式：1）

看了一下，status為2時，沒有success_time，所以成功入群是1

select
  count(1)
from
  user_captcha_record
where
  captcha_status = 1
  and captcha_success_time >= "2025-06-01 00:00:00"
-- 2422

結果為2422

14. 據嫌疑人交代曾在發卡網上刪除過一條訂單數據，請找出該刪除訂單的訂單號是？（答案格式：請按實際值填寫）

不是軟刪，那就是硬刪的

開啟了binlog

查一下版本是8.4.3

用對應版本的mysqlbinlog恢復數據

第二個字段是訂單號

結果為4V8XNK8Q02MD5D2R

15. 發卡網站上2025年6月之后訂單交易成功的總金額是？忽略被刪除的數據（答案格式：1）

select
  sum(actual_price)
from
  orders
where
  status = 4
  and updated_at >= "2025-06-01 00:00:00"
-- 295202.00

結果為295202

16. 發卡網站的后臺訪問路徑是？（答案格式：/root）

/admin會自動跳轉

結果為/admin/auth/login

17. 計算出用戶密碼算法中Salt的值，并進行Base64編碼，結果是？（答案格式：請按實際值填寫）

扣出來放到環境里跑

結果為lAID2ktDeRlGbcg=

18. 發卡網站配置的郵件發送人地址是？（答案格式：abc@abc.com）

繞密

結果為ituzz@qq.com

19. 當前發卡網站首頁儀表盤中顯示的發卡網站版本為？（答案格式：1.1.1）

結果為2.0.5

20. 當前發卡網站中綁定的訂單推送Telegram用戶id為（答案格式：請按實際值填寫）

結果為6213151597

流量包分析

請根據網絡流量包檢材，回答以下問題：

21. 黑客攻擊的目標路由器SSID為 （答案格式：請按實際值填寫）

根據后面的提問可以知道，這邊的ssid是laozhaoWIFI，這里是后面進后臺找到的密碼，否則就要解密握手流量

后面還根據路由器的ip，對應的mac地址去查詢

結果為laozhaoWIFI

22. 黑客成功捕獲了WIFI中WPA協議握手包，其中有效握手包組數為(完整握手為一組)（答案格式：1）

注意中間一段TpLink的不能算，不全

結果為4

23. 黑客爆破得出的WiFi密碼為（提示：密碼由小寫英文字母和數字組成）（答案格式：abcd1234）

結果為password1110

24. 黑客成功連接Wifi后，發現路由器操作系統為？（答案格式：請按實際值填寫）

0流

結果為ImmortalWrt

25. 黑客對路由器后臺進行爆破攻擊，該路由器后臺密碼為（答案格式：請按實際值填寫）

這里有連著的兩個請求

后面第一個返回是403，第二個返回200，所以應該是第二個

結果為password

26. 黑客通過修改路由器設置，將被劫持的域名為（答案格式：www.xxx.com）

得有點耐心找

結果為www.qq.com

27. 黑客在路由器管理后臺發現FTP服務配置，FTP登錄密碼為？（答案格式：請按實際值填寫）

http里沒搜到，直接過濾ftp

結果為mast

28. 黑客通過FTP上傳了一個壓縮包文件，該文件內容為（答案格式：請按實際值填寫）

上傳的是flag.zip，還有一個zhaohong（hydra），但是壓縮包有密碼

暴力跑一下試試

結果為code:123456789

29. 黑客通過路由器執行shell腳本，反彈shell的監聽端口為（答案格式：1）

結果為4445

30. 黑客通過反彈shell成功控制目標路由器后，總共執行了多少條命令（答案格式：1)

tcp.dstport == 4445

結果為4

APK程序分析

請根據APK檢材，回答以下問題：

31. apk 的版本名稱為？ （答案格式：1.1.1）

結果為3.0.12

32. 在該APP中，調用了哪個System的方法用于獲取本地系統的時間戳？（答案格式：MainActivity）

有加固，在線平臺直接脫一下

這邊注意一下是獲取本地的時間戳，而不是apk種assets里的時間戳

結果為currentTimeMillis

33. apk運行后getVer()的返回值是多少？（答案格式：1.0.0）

用了qvmp

跟一下就能知道邏輯是在native層，用的interface11這個函數，參數是9397，可以不跟

frida主動調用梭哈

(() => {
  function getVer() {
    Java.choose("net.net.MainActivity", {
      onMatch: function (instance) {
        console.log(`MainActivity instance found: ${instance}`);
        console.warn(`net.net.MainActivity.getVer():${instance.getVer()}`);
      },
      onComplete: function () {
        console.log("MainActivity instance search completed");
      },
    });
  }

  function main() {
    Java.perform(() => {
      getVer();
    });
  }

  main();
})();

要注意是加固的，所以不要用spawn模式啟動，打開app后再attach

結果為4.56.23

34. apk運行后需要通過一個http get請求才能打開第二個界面，給出該請求URL？ （答案格式：http://www.xxx.com/test?a=1）

就這一部分的formatString，直接frida調用生成一個就可以了

(() => {
  function mainActivity() {
    Java.choose("net.net.MainActivity", {
      onMatch: function (instance) {
        console.log(`MainActivity instance found: ${instance}`);
        console.warn(`net.net.MainActivity.getVer():${instance.getVer()}`);
        console.warn(
          `net.net.MainActivity.apkHash.value:${instance.apkHash.value}`,
        );
        console.warn(`net.net.MainActivity.apkHash.ts:${instance.ts.value}`);
        console.warn(
          `url: localhost:60723/${multiLayerEncrypt("check_apk_status")}?hash=${instance.apkHash.value}&ver=${instance.getVer()}&ts=${instance.ts.value}`,
        );
      },
      onComplete: function () {
        console.log("MainActivity instance search completed");
      },
    });
  }

  function stringToBytes(str) {
    var bytes = new Uint8Array(str.length);
    for (var i = 0; i < str.length; i++) {
      bytes[i] = str.charCodeAt(i);
    }
    return Array.prototype.slice.call(bytes);
  }

  function multiLayerEncrypt(arg) {
    let MultiLayerEncryption = Java.use(
      "net.net.crypto.MultiLayerEncryption",
    ).$new();
    return MultiLayerEncryption.multiLayerEncrypt(arg);
  }

  function hookEquals() {
    let JString = Java.use("java.lang.String");
    JString["equals"].implementation = function (obj) {
      if (obj && obj.toString().includes("?hash=")) {
        console.log(this.toString(), stringToBytes(this.toString()));
        console.log(obj.toString(), stringToBytes(obj.toString()));
      }
      let ret = this["equals"](obj);
      return ret;
    };
  }

  function main() {
    Java.perform(() => {
      mainActivity();
      hookEquals();
    });
  }

  main();
})();

這里有一個坑點，生成完的請求，最后一個是換行符，發起請求的時候要注意轉義

拿到請求后進adb shell，curl "localhost:60723/K0pQvBZ38ykL26OzfshqYTHC2f7RUJYIgrmIF6GcORU=?hash=983f8a605d16530190c09b0884d7cef1&ver=4.56.23&ts=1727899789%0A"發個請求就進第二個頁面了

結果為http://localhost:60723/K0pQvBZ38ykL26OzfshqYTHC2f7RUJYIgrmIF6GcORU=?hash=983f8a605d16530190c09b0884d7cef1&ver=4.56.23&ts=1727899789%0A

35. apk第二界面的8位授權碼是什么？ （答案格式：11111111）

這里就是校驗輸入的hashCode，需要爆破，而且APK里沒有提示。不過答案格式是8個數字，可以試一下

// 省略其他

public static String zfill(long number, int width) {
    return String.format("%0" + width + "d", number);
}

public static String checkHashCode() {
    for(long i =0 ;i< 1e9;i++) {
        String text = zfill(i,8);
        if (text.hashCode() == -711638849) {
            return text;
        }
    }
    return "Not Found";
}

public static void main(String[] args) {
    System.out.println(checkHashCode());
}

// 84572399

結果為84572399

二進制程序分析

請在安全的環境下安裝該程序，并回答以下問題：

36. 安裝該程序后，該惡意程序的可執行文件所在的直接父目錄名稱是什么為？（答案格式：root）

結果為proxy

37. 解密文件名為RnRGaWxlcy5lZGIiL的文件時所使用的key是什么？（答案格式：請按實際值填寫）

這個是解密函數

多次模256，丟給ai推測rc4，實測確實是

結果為1Njc2NTQ2Mzc0NTc

38. 解密文件RnRGaWxlcy5lZGIiL成功后，請分析并給出解密后的文件的入口點地址？（答案格式：0x180000000）

加上基址

結果為0x180002730

39. 加密文件名為6c051a72b91a1的文件時所使用的密鑰是多少？（答案格式：請按實際值填寫）

上面的密鑰字符串下面就是這個，跳過來是執行了這個函數,這個函數在上面解密出的文件里

粗看邏輯是讀取文件并加密，加密函數是sub_180001E50

丟給ai秒出aes

上面iv下面key

結果為cgDSb6VOegeF7EuW

*40. 6c051a72b91a1.1文件解密后的md5值后六位是多少？（字母全大寫，答案格式：AAAAAA）

魔改了

可能改的這里，讓ai生成一個沒解密出來

 

計算機取證分析

請根據計算機檢材和內存檢材，回答以下問題：

41. 操作系統的Build版本號是？（答案格式：1）

結果為19044

42. 操作系統設置的賬戶密碼最長存留期為多少天？（答案格式：1）

查admin的密碼，結果是258369

然后仿真不繞密

結果為68

43. 用戶2登陸密碼NT哈希值后六位是？（字母全大寫，答案格式：AAAAAA）

結果為A9C708

44. 藍牙mac地址是多少？（答案格式：AA-AA-AA-AA-AA-AA）

結果為9C-B6-D0-04-C9-CC

45. SafeImager的產品序列號后四位是？（字母全大寫，答案格式：AAAAAA）

看具體情況，一般直接文序列號就是注冊表，如果還提到插拔，就看事件日志

結果為09C4

46. 123.VHD所處的結束扇區是？（答案格式：1 ）

結果為27445255

47. 用戶在BitLocker加密分區最后修改的文件是?（答案格式：abcd.txt）

vhd里

結果為資料1.txt

48. 用戶連接192.168.114.129時用的會話名稱是？（答案格式：按照實際情況填寫）

結果為連接阿里云

49. 用戶創建存儲虛擬幣錢包地址頁面的時間是？（使用雙位數格式，答案格式：01月01日）

桌面上有個軟件，打開就能看到

結果為10月07日

50. 用戶的虛擬幣錢包地址是？（答案格式：按照實際情況填寫）

賽博廚子魔法棒一直點就完了

結果為3HrdpWM8ZrBVw9yu8jx1RoNNK6BZxwsHd9

51. 用戶VC加密容器的密碼是？（答案格式：按照實際情況填寫）

郵箱里有內容，分卷壓縮包

過濾一下找到所有分卷

解壓后有個docx，文末字體是白色，改掉就出來了

結果為SHUZHENGBEIctzy2025

52. 用戶在生活中使用的代號是？（答案格式：按照實際情況填寫）

桌面的代號.wav，又看到Audacity，頻譜圖秒了

結果為小胖

53. 李安東的銀行卡歸屬哪個銀行？（答案格式：農業銀行）

爆破，還能看到有個氣溫加密.zip，是下一題的

結果為交通銀行

54. 請分析某市10月6日最高氣溫是？（答案格式：1）

算法都不一樣，前面打開還頭部數據錯誤

這里文件太多，就不改二進制了，puzzlesolver啟動！

結果為21

55. 用戶的BitLocker密碼是？（答案格式：按照實際情況填寫）

圖片隱寫

結果為SZBJSJTM2025

56. 用戶辦公室的門禁密碼是？（答案格式：按照實際情況填寫）

vhd里

用密碼解密即可，可以找到一個加密文件

之前軟件那個壓縮包里是有一個解密程序的

看文件尾

結果為147963258

57. 用戶使用的以D開頭的解密程序的MD5值后六位是？（字母全大寫，答案格式：AAAAAA）

結果為3A892E

58. 木馬程序運行至系統斷點前加載了幾個動態鏈接庫？（答案格式：1）

直接拖進dbg

結果為5

59. 木馬產生的程序名稱是什么？（答案格式：abcd.txt）

微步跑

結果為Wins.exe

60. 木馬嘗試訪問的域名是什么？（答案格式：按照實際情況填寫）

結果為edu-image.nosdn.127.net

61. 分析計算機內存檢材，此內存鏡像制作時的系統時間是？（使用雙位數格式，答案格式：01月01日）

結果為10月16日

62. 分析計算機內存檢材，用戶Yiyelin的用戶標識后4位是？（答案格式：1111）

結果為1002

63. 分析計算機內存檢材，計算機的CPU型號是什么？（答案格式： i9-1110U）

結果為i7-1165G7

64. 分析計算機內存檢材，wps.exe的PID是？（答案格式：1）

結果為5888

65. 分析計算機內存檢材，此計算機開機自啟動的遠控軟件名稱是？（答案格式：abcd.txt）

導出注冊表查詢也行，直接vol用autorun也行，不喜歡做內存，我就省點事

結果為SunloginClient.exe

物聯網設備取證分析

66. 打印機的主機名稱是什么？(答案格式：root)

結果為print

67. 打印文件存儲在哪個目錄？(答案格式：/root/root)

看root的歷史命令可以找到配置文件/etc/cups/cups-files.conf

結果為/var/spool/cups

68. 同一天，打印兩份文件的用戶是誰？(答案格式：root)

找到打印日志

結果為alice

69. 分析物聯網檢材，木馬運行后，自身產生的進程ID是多少？（答案格式：1）

要結合下一題看，看到C2就可以確認是木馬了

然后看syslog

結果為2177

70. 分析物聯網檢材，系統中存在一個非標定時任務，這個任務每隔多少分鐘執行？（答案格式：1）

結果為10

71. 分析物聯網檢材，木馬程序會竊取文檔暫存在隱藏目錄，這個目錄的絕對路徑？（/root/root/）

倒序base64

結果為/tmp/.cache/

72. 分析物聯網檢材，木馬程序將數據上傳到的服務器的IP地址是多少？（答案格式：1.1.1.1）

結果為185.199.108.153

73. 根據木馬程序，它監視的關鍵字是什么？（答案格式：按照實際情況填寫）

結果為Project Dragonfire

移動終端取證分析

請根據手機檢材，回答以下問題：

74. 分析檢材中微信ID:wxid_f4s0jmpvrc522對應的手機號后四位為（答案格式：1111）

提取uin計算密鑰并解密數據庫

結果為8390

75. 分析檢材中"華為應用市場"第一次安裝日期為（使用雙位數格式，答案格式：01月01日）

結果為09月24日

76. 找出檢材中錢包APP，請列出該APP中ETH地址后六位是（字母全大寫，答案格式：AAAAAA）

可以發現安裝了imtoken，找到錢包地址0x304fEd2927f47692E50158A1148a1B65503FE61F

結果為3FE61F

77. 分析出檢材中包含"南昌西站"的圖片，計算該圖片的MD5后六位？（字母全大寫，答案格式：AAAAAA）

結果為85A51D

78. 手機相冊中有張"imtoken助記詞1.PNG"圖片被破壞，請修復該圖片，列出該圖片中第三個單詞。（答案格式：按照實際情況填寫）

寬度被改，通過crc算回寬度

結果為boost

79. 找出一張PNG圖片，該圖片上顯示"助記詞2"，請列出該圖片上顯示的第二個單詞。（答案格式：按照實際情況填寫）

結果為delay

80. 找出檢材中顯示"助記詞3"的文檔，列出該文檔中記錄的第三個助記詞單詞。（答案格式：按照實際情況填寫）

文檔，暴力搜索

結果為quarter

81. 分析出該組助記詞正常順序中最后一個單詞（已知助記詞1、助記詞2、助記詞3中的單詞順序有被調整）。（答案格式：按照實際情況填寫）

知道助記詞和地址，可以還原助記詞順序

結果為segment

82. 分析出郵箱中收件人QQ號為"850563586"的姓名（答案格式：按照實際情況填寫）

這東西搞了個虛擬機

ntqq，先讀取QQ的uid u_mq61uhp6gZE49O_lq9Hvtg

然后解密數據庫

沒有protobuf的的代碼，只能靠猜字段含義

結果為劉佳雨

83. 得知機主通過某個應用給HHshAL發送了一個文檔，該應用的數據包名是什么？（答案格式：com.test）

結合下題

結果為dingtong.saichuang

84. 接上題，該應用聊天記錄數據庫的打開密碼是什么？（答案格式：按照實際情況填寫）

搜索跳過去就有，或者仿真起來用apk_analyzer或者easyFrida直接梭哈

結果為@1@#!aajsk1*JKJ

85. 接上題，機主發送的這個加密文檔，打開密碼是什么？（答案格式：按照實際情況填寫）

上面一條是下載鏈接，下載下來用密碼打開

結果為QWERT666

86. 厲明的身份證地址登記的門牌號是多少？？（答案格式：1）

在這個應用的數據庫里找到解壓密碼

結果為722

87. 分析出"important1.xlsx"文件中體現的"金達欣"銀行卡后六位？（答案格式：111111）

結果為935629

88. 接上題，保存"important1.xlsx"打開密碼數據的應用，該應用的啟動密碼是什么？（答案格式：按照實際情況填寫）

老套殼軟件了

結果為1596

數據分析

請根據數據分析檢材，回答以下問題：

兩個sql先還原

89. 通過對檢材"01-lott.sql"文件進行分析，統計莊家"188"在2021-05-10當日的電子投注總筆數（答案格式：1）

看ddl可以知道t_trade_betrecord是投注記錄表

select
  count(1)
from
  t_trade_betrecord
where
  Keeper = "188"
  and DATE(CreateTime) = "2021-05-10";
// 2299

結果為2299

90. 通過對檢材"01-lott.sql"文件進行分析，統計t_trade_betrecord中莊家"188"記錄中彩票類型為"jnd28"且期號在t_lottery_jnd表中存在的記錄數。（答案格式：1）

select
  count(1)
from
  t_trade_betrecord
where
  Keeper = "188"
  and LotteryType = "jnd28"
  and Issue in (select Issue from t_lottery_jnd);	
// 92842

結果為92842

91. 通過對檢材"01-lott.sql"文件進行分析，統計莊家"188"的玩家在2021-05-10當日：電子投注內容出現頻率最高的電子投注內容是什么？（答案格式：按照實際情況填寫）

select
  Content,
  count(Content) AS "次數"
from
  t_trade_betrecord
where
  Keeper = "188"
  and DATE(CreateTime) = "2021-05-10"
group by Content
order by 次數 desc
limit 1;
-- 500單 39

結果為500單

92. 通過對檢材"01-lott.sql"文件進行分析，關聯t_trade_betrecord與t_lottery_jnd表，分析莊家"188"在2021-05-10投注"jnd28"時：當開獎結果為"大"時，玩家投注包含"小"的筆數占比（使用雙位數格式，答案格式：11.11%）

select 玩家小/總數 from (
  select
  count(Content) AS 總數,
  SUM(if(INSTR(Content,"小"),1,0)) AS 玩家小
from
  t_trade_betrecord ttb
  left join t_lottery_jnd tlj on ttb.Issue = tlj.Issue
where
  ttb.Keeper = "188"
  and DATE(ttb.CreateTime) = "2021-05-10"
  and ttb.LotteryType = "jnd28"
  and tlj.R_B_M = "大"
) a
-- 0.4488

結果為44.88%

93. 通過對檢材"02-crime_records.sql"分析，統計相鄰兩次作案時間間隔在1天之內的城市和該城市兩次作案時間間隔在1天之內的案件總數量，找出案件總數最多的城市名。（答案格式：按照實際情況填寫）

WITH city_intervals AS (
    SELECT
        city,
        STR_TO_DATE(crime_time, '%Y/%m/%d %H:%i') AS crime_dt,
        LAG(STR_TO_DATE(crime_time, '%Y/%m/%d %H:%i')) OVER (PARTITION BY city ORDER BY STR_TO_DATE(crime_time, '%Y/%m/%d %H:%i')) AS prev_dt
    FROM crime_records
)
SELECT
    city,
    COUNT(*) AS close_case_count
FROM city_intervals
WHERE
    prev_dt IS NOT NULL
    AND TIMESTAMPDIFF(HOUR, prev_dt, crime_dt) <= 24
GROUP BY city
ORDER BY close_case_count DESC
LIMIT 1;
-- 福州	2076

結果為福州

94. 通過對檢材"02-crime_records.sql"分析，根據案件的損失金額和傷情等級，將案件分為 "輕微案件""一般案件""重大案件""其他"四類（分類規則如下），并統計 2023 年各類型案件的數量。輕微案件：損失金額≤10000 元且無人員受傷（injury_level 為空或未提及）；一般案件：損失金額 10001-50000 元，或有輕微傷；重大案件：損失金額 > 50000 元，或有輕傷或有重傷；其他：非上述情況。（按照案件數量的降序輸出答案，答案格式為：40/30/20/10）

我這里以crime_time為基準統計年份，這里要注意順序，先判斷重大案件，再判斷一般案件，最后判斷輕微案件，剩下的是其他。不然可能出現損失金額幾十萬，但是輕微傷被判斷成一般案件的情況

select 案件分類,count(案件分類) AS 案件數量 from (select
  case
  when (loss_amount > 50000) or (injury_level = "輕傷" or injury_level = "重傷") then "重大案件"
  when (loss_amount >= 10001 and loss_amount <= 50000) or (injury_level="輕微傷") then "一般案件"
  when loss_amount <= 10000 and injury_level is NULL then "輕微案件"
  else "其他"
  end as "案件分類"
from
  crime_records
where
  YEAR(crime_time) = "2023") a
group by 案件分類
order by 案件數量 desc
-- 重大案件	15712
-- 其他	2058
-- 一般案件	1985
-- 輕微案件	222

結果為15712/2058/1985/222

95. 通過對檢材"02-crime_records.sql"分析，統計 2021-2023 年期間（含2021年和2023年），每年處理結果為 "移送起訴" 的案件里，每一年中損失總額最高的案件類型對應的損失總額為？（按 2021 - 2023 年順序連接損失總額，連接符號使用/,小數點保留2位，答案格式為 ：1.37/2.21/3.45）

還是以crime_time為基準統計年份

select
  年份,
  max(money) AS 損失金額
from
  (
    select
      YEAR(crime_time) AS 年份,
      crime_type,
      sum(loss_amount) money
    from
      crime_records
    where
      handling_result = "移送起訴"
    group by
      年份,
      crime_type
  ) t
where
  年份 in ("2021", "2022", "2023")
group by
  年份
order by
  年份
-- 2021	325806042.90999955
-- 2022	344804883.9800002
-- 2023	352132431.3699997

結果為325806042.91/344804883.98/352132431.37

96. 通過對檢材"03-案件卡串號數據"表分析，該表每條數據的"卡串號(IMSI)"字段值存在問題，不可信。真實可信的卡串號值在"溯源"字段中（溯源字段的值格式均為"{手機號=[待獲取的卡串號->手機卡串號(IMSI)使用過的手機號->當前]}"），請統計分析出該表中哪個真實卡串號出現過的次數最多？（答案格式：按照實際情況填寫）

import polars as pl


def parse(file_path: str) -> None:
    df = pl.read_excel(file_path, sheet_name="sheet1", has_header=True)
    tmp = df.with_columns(pl.col("溯源").str.extract(r"=\[(\d+)->", 1).alias("IMSI"))
    print(
        tmp.group_by("IMSI")
        .agg(pl.col("IMSI").count().alias("count"))
        .sort("count", descending=True)
        .limit(1)
    )


def main():
    parse("03-案件卡串號數據.xlsx")


if __name__ == "__main__":
    main()
# 460017709683511 3

結果為460017709683511

97. 通過對檢材"04-涉詐案件信息表"分析，統計每個分局2024-2025年每月被騙總額環比大于30%的月份個數（環比定義：（這個月的數據-上個月的數據）/上個月數據。特殊情況，例如某分局2025年1月被騙金額總和為100，若該分局2024年12月沒有被騙金額，則該分局2025年1月也符合題目要求，應增加一個月份。2024年1月不需要計算與上個月的環比情況），請寫出環比大于30%的月份個數最多的分局ID名稱為？（答案格式：按照實際情況填寫）

import polars as pl


def get_last_month(month: str) -> str:
    year = int(month[:4])
    m = int(month[-2:])
    if m == 1:
        return f"{year - 1}-12"
    else:
        return f"{year}-{m - 1:02d}"


def cal_QOQ(data: pl.DataFrame) -> None:
    ids = list(set(data.get_column("分局ID").to_list()))
    for pid in ids:
        df = data.filter(pl.col("分局ID") == pid).sort("date", descending=True)
        if len(df) > 1:  # 只有一個月數據的沒必要計算，值為1
            cnt = 0
            months = list(set(df.get_column("date").to_list()))
            for month in months:
                month = str(month)
                current_money = float(
                    df.filter(pl.col("date") == month).get_column("被騙總額")[0]
                )
                last_money_series = df.filter(
                    pl.col("date") == get_last_month(month)
                ).get_column("被騙總額")
                last_money: float = 0
                if last_money_series.shape[0] != 0:
                    last_money = float(last_money_series[0])
                    if (current_money - last_money) / last_money > 0.3:
                        cnt += 1
                elif month == "2024-01":
                    continue
                else:  # 上個月沒有數據，但是按照規則要計入
                    cnt += 1
            print(pid, cnt)


def parse(file_path: str) -> None:
    df = pl.read_excel(file_path, sheet_name="Sheet1", has_header=True)
    df = df.with_columns(pl.col("案件時間").str.head(7).alias("date"))
    df = df.filter(pl.col("案件時間").str.head(4).is_in(["2024", "2025"]))

    tmp = df.group_by(["分局ID", "date"]).agg(
        pl.col("被騙金額").sum().alias("被騙總額")
    )
    cal_QOQ(tmp)


def main():
    parse("04-涉詐案件信息表.xlsx")


if __name__ == "__main__":
    main()

# A675 4
# A001 3

結果為A675

98. 通過對檢材"05-人像卡口信息表"和"06-涉毒前科人員信息表"（兩表均無重復數據，直接要求答題即可。感知時間字段格式均為yyyy-MM-dd HH:mm:ss；傳感器ID（人像卡口點位）值不同則代表不同的攝像點位），為摸排疑似涉毒的窩點，請分析出在00:00:00~06:00:00(含0點跟6點)人像記錄中，哪個傳感器點位ID抓拍到最多的不同涉毒前科人員？（答案格式：按照實際情況填寫）

import polars as pl


def get_drug_people(file_path: str) -> list[str]:
    df = pl.read_excel(file_path, sheet_name="0", has_header=True)
    return list(set(df.get_column("證件號碼").to_list()))


def parse(file_path: str, drug_people: list[str]) -> None:
    df = pl.read_excel(file_path, sheet_name="0", has_header=True)
    df = df.with_columns(pl.col("感知時間").str.slice(-8, 2).alias("時間段"))
    df = df.filter(
        pl.col("時間段").is_in([str(x).zfill(2) for x in range(6)]),
        pl.col("證件號碼").is_in(drug_people),
    )
    print(
        df.group_by("傳感器ID")
        .agg(pl.col("證件號碼").unique().len().alias("人數"))
        .sort("人數", descending=True)
        .limit(1)
    )


def main():
    parse("05-人像卡口信息表.xlsx", get_drug_people("06-涉毒前科人員信息表.xlsx"))


if __name__ == "__main__":
    main()
# 350203103 10

結果為350203103

99. 接上題，為摸排潛在的涉毒人員，請分析出有多少個非涉毒前科人員至少跟3個不同的涉毒前科人員同行過？（本題的"同行"指：兩人在同一個人像卡口點位感知時間差在10（含）秒內）（答案格式：1）

import polars as pl
from datetime import timedelta


def get_drug_people(file_path: str) -> list[str]:
    df = pl.read_excel(file_path, sheet_name="0", has_header=True)
    return list(set(df.get_column("證件號碼").to_list()))


def get_around_people(df: pl.DataFrame) -> dict[str, list[str]]:
    dic = {}
    for row in df.iter_rows():
        cam_time = row[0]
        self_idcard = row[1]
        cam_id = row[2]
        start_time = cam_time - timedelta(seconds=10)
        end_time = cam_time + timedelta(seconds=10)
        df3 = df.filter(
            pl.col("傳感器ID") == cam_id,
            pl.col("感知時間").is_between(start_time, end_time),
            pl.col("證件號碼") != self_idcard,
        )
        if df3.shape[0] != 0:
            if self_idcard in dic.keys():
                dic[self_idcard].extend(df3.get_column("證件號碼").to_list())
            else:
                dic[self_idcard] = df3.get_column("證件號碼").to_list()
    return dic


def parse(file_path: str, drug_peoples: list[str]) -> None:
    df = pl.read_excel(
        file_path,
        sheet_name="0",
        has_header=True,
        schema_overrides={"感知時間": pl.Datetime},
    )
    diff: dict[str, int] = {}
    d_people = []
    # 獲取每個人相隔10秒的所有人
    around_people = get_around_people(df)
    for key, value in around_people.items():
        value = set(value)
        cnt = 0
        # 去掉有前科的
        if key in drug_peoples:
            continue
        for v in value:
            # 統計數量
            if v in drug_peoples:
                cnt += 1
        if cnt >= 3:
            d_people.append(key)
    print(d_people)


def main():
    parse("05-人像卡口信息表.xlsx", get_drug_people("06-涉毒前科人員信息表.xlsx"))


if __name__ == "__main__":
    main()

# ['350624198712105014', '35062219961102123X', '350622191508303798', '350622190901124347']

結果為4

100. 近幾年架設簡易GOIP設備進行群呼詐騙的案件屢見不鮮。架設和維護該設備的人員通常會頻繁更換酒店【即只住一天然后更換酒店】以此躲避公安的偵察打擊。請根據"07-旅店住宿信息表"（該表無重復數據，直接要求答題即可。時間相關的字段格式均為yyyy-MM-dd HH:mm:ss），篩選出2024和2025年的住宿記錄（以"入住時間"為準），頻繁更換酒店的人員有幾個？（答案格式：1）

from datetime import timedelta
import polars as pl


def parse(file_path: str) -> None:
    df = pl.read_excel(
        file_path,
        sheet_name="0",
        has_header=True,
        schema_overrides={"入住時間": pl.Datetime},
    )
    df = df.filter(
        pl.col("入住時間").is_between(
            pl.datetime(2024, 1, 1),
            pl.datetime(2025, 12, 31),
        )
    )
    people = []
    for row in df.iter_rows():
        idcard = row[0]
        hotel = row[1]
        livein_time = row[3]
        tmp = df.filter(
            pl.col("證件號碼") == idcard, pl.col("旅店編碼").unique().len() > 1
        )
        if tmp.shape[0] > 1:  # 住過至少2個酒店
            start_date = livein_time - timedelta(days=1)
            end_date = livein_time + timedelta(days=1)
            tmp = tmp.filter(  # 間隔1天的其他酒店
                pl.col("旅店編碼") != hotel,
                pl.col("入住時間").is_between(start_date, end_date),
            )
            if tmp.shape[0] > 0:
                people.extend(tmp.get_column("證件號碼").to_list())
    print(set(people))


def main():
    parse("07-旅店住宿信息表.xlsx")


if __name__ == "__main__":
    main()
# {'157444197409115890', '112932197212208014', '342674191112293875', '628529194608207132'}

結果為4