search

pucherot / Pi.Alert

WIFI / LAN intruder detector. Check the devices connected and alert you with unknown devices. It also warns of the disconnection of "always connected" devices
GNU General Public License v3.0
1.97k stars 121 forks source link

UNIQUE constraint failed: Devices.dev_MAC #23

Closed roberthawdon closed 3 years ago

roberthawdon commented 3 years ago

I'm getting the following error when running a scan:

Pi.Alert 2.54 (2021-01-13)
---------------------------------------------------------
Scan Devices
    ScanCycle: 1
    Timestamp: 2021-01-13 23:27:00

Scanning...
    arp-scan Method...
    Pi-hole Method...
    DHCP Leases Method...

Procesising scan results...
    Devices Detected.......: 22
        arp-scan Method....: 22
        Pi-hole Method.....: +0
        New Devices........: 0

    Devices in this cycle..: 20
        Down Alerts........: 0
        New Down Alerts....: 0
        New Connections....: 0
        Disconnections.....: 37
        IP Changes.........: 0

Updating DB Info...
    Sessions Events (connect / discconnect) ...
    Creating new devices...
Traceback (most recent call last):
  File "/home/pi/pialert/back/pialert.py", line 1339, in <module>
    sys.exit(main())
  File "/home/pi/pialert/back/pialert.py", line 75, in main
    res = scan_network()
  File "/home/pi/pialert/back/pialert.py", line 381, in scan_network
    create_new_devices ()
  File "/home/pi/pialert/back/pialert.py", line 722, in create_new_devices
    (startTime, startTime) )
sqlite3.IntegrityError: UNIQUE constraint failed: Devices.dev_MAC

This is from a fresh install from the main branch. (v 2.54)

pucherot commented 3 years ago

Ohhh, it's a bit weird, the INSERT SQL statement checks that the MAC doesn't already exist in the list of existing devices.

Please, can you execute this command?

sqlite3 ~/pialert/db/pialert.db "SELECT * FROM Devices"
sqlite3 ~/pialert/db/pialert.db "SELECT * FROM DHCP_Leases"
roberthawdon commented 3 years ago

Hi, see below (I've hashed out part of my public IP)

pi@pihole:~ $ sqlite3 ~/pialert/db/pialert.db "SELECT * FROM Devices"
Internet|Internet|House|Router|Orange / Vodafone / Movistar ....|0|Always on||2021-01-01 00:00:00|2021-01-01 00:00:00|151.231.###.###|0|0|1|0|0|0|2021-01-01 00:00:00.000000|1
d0:58:fc:8e:92:5a|(unknown)|(unknown)||BSkyB Ltd|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.215|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
3c:89:94:25:25:39|(unknown)|(unknown)||BSkyB Ltd|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.1|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
54:e0:19:6e:1a:53|(unknown)|(unknown)||Ring LLC|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.2|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
54:e0:19:5e:4d:fe|(unknown)|(unknown)||Ring LLC|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.14|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
3c:89:94:0d:ed:f1|(unknown)|(unknown)||BSkyB Ltd|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.18|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
48:e1:e9:29:5d:4a|(unknown)|(unknown)||Chengdu Meross Technology Co., Ltd.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.19|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
6c:4a:85:31:87:16|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.56|0|15|1|1|0|0|2021-01-13 23:25:44.032263|1
64:4b:f0:13:b6:30|(unknown)|(unknown)||CalDigit, Inc|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.84|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
f8:0f:f9:93:80:ec|(unknown)|(unknown)||Google, Inc.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.41|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
e8:9e:b4:be:92:4b|(unknown)|(unknown)||Hon Hai Precision Ind. Co.,Ltd.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.92|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
48:e1:e9:2d:d4:af|(unknown)|(unknown)||Chengdu Meross Technology Co., Ltd.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.108|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
14:59:c0:54:de:6a|(unknown)|(unknown)||NETGEAR|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.130|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
ac:63:be:3a:ed:90|(unknown)|(unknown)||Amazon Technologies Inc.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.152|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
d0:58:fc:a6:c1:d2|(unknown)|(unknown)||BSkyB Ltd|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.216|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
48:e1:e9:2d:d1:e9|(unknown)|(unknown)||Chengdu Meross Technology Co., Ltd.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.180|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
0c:96:e6:53:1a:7c|(unknown)|(unknown)||Cloud Network Technology (Samoa) Limited|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.201|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
a8:6b:ad:31:b5:19|(unknown)|(unknown)||Hon Hai Precision Ind. Co.,Ltd.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.211|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
38:f9:d3:64:7d:a4|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.114|0|15|1|1|0|0|2021-01-13 23:25:44.032263|1
86:65:d0:0e:40:50|(unknown)|(unknown)||(Unknown)|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.112|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
5e:3f:a0:d0:16:5b|(unknown)|(unknown)||(Unknown)|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.160|0|1|1|1|0|0||1
c6:31:2d:f3:98:f8|(unknown)|(unknown)||(Unknown)|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.159|0|1|1|1|0|0||1
00:1f:c5:f0:15:57|(unknown)|(unknown)||Nintendo Co., Ltd.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.109|0|1|1|1|0|0||1
ea:c8:01:9b:49:34|(unknown)|(unknown)||(Unknown)|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.168|0|1|1|1|0|0||1
7c:a1:ae:39:ed:0f|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.149|0|1|1|1|0|0||1
9c:b6:d0:de:6d:5d|ian-arch-XPS15-9560.lan|(unknown)||Rivet Networks|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.55|0|1|1|1|0|0||1
3a:b6:ec:e9:05:2e|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.192|0|1|1|1|0|0||1
c0:a5:3e:a2:6f:5c|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.156|0|1|1|1|0|0||1
d0:16:b4:b4:df:6d|(unknown)|(unknown)||Huawei Technologies Co.,Ltd|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.87|0|1|1|1|0|0||1
34:c3:d2:d1:65:e3|(unknown)|(unknown)||Fn-Link Technology Limited|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|-|0|1|1|1|0|0||1
d8:0d:17:74:38:eb|HS100.lan|(unknown)||Tp-Link Technologies Co.,Ltd.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.64|0|1|1|1|0|0||1
d8:0d:17:74:43:cd|HS100.lan|(unknown)||Tp-Link Technologies Co.,Ltd.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.49|0|1|1|1|0|0||1
d8:55:75:81:78:2f|Galaxy-A10.lan|(unknown)||Samsung Electronics Co.,Ltd|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.68|0|1|1|1|0|0||1
68:ff:7b:35:22:ad|HS100.lan|(unknown)||Tp-Link Technologies Co.,Ltd.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.65|0|1|1|1|0|0||1
cc:32:e5:1e:8d:fb|HS100.lan|(unknown)||Tp-Link Technologies Co.,Ltd.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.105|0|1|1|1|0|0||1
70:81:eb:67:bc:f4|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.31|0|1|1|1|0|0||1
40:98:ad:21:24:99|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.42|0|1|1|1|0|0||1
00:e1:51:b0:00:9f|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.155|0|1|1|1|0|0||1
00:17:fa:52:3b:0c|(unknown)|(unknown)||Microsoft Corporation|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.111|0|1|1|1|0|0||1
74:9e:af:40:00:8b|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.26|0|1|1|1|0|0||1
f4:06:16:4a:5e:4a|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.193|0|1|1|1|0|0||1
a8:a1:98:06:9b:09|(unknown)|(unknown)||TCT mobile ltd|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.184|0|1|1|1|0|0||1
c4:84:66:91:a5:6d|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.81|0|1|1|1|0|0||1
b8:27:eb:85:e9:5f|(unknown)|(unknown)||Raspberry Pi Foundation|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.253|0|1|1|1|0|0||1
f8:6f:c1:33:70:2b|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.146|0|1|1|1|0|0||1
d8:96:95:88:1e:d3|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.136|0|1|1|1|0|0||1
68:9c:70:ab:8d:47|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.40|0|1|1|1|0|0||1
d0:3f:aa:2b:22:0a|iPhone.lan|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.78|0|1|1|1|0|0||1
e6:8f:8a:ec:19:79|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|fd58:84d1:7017:0:5070:749f:a05a:f267|0|1|1|1|0|0||1
34:2e:b6:a1:4d:11|(unknown)|(unknown)||Huawei Technologies Co.,Ltd|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|2a02:c7f:98c2:2200:362e:b6ff:fea1:4d11|0|1|1|1|0|0||1
1e:97:ef:84:88:b1|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.34|0|1|1|1|0|0||1
7e:27:2a:f5:9d:78|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|2a02:c7f:98c2:2200:78ba:5007:cce8:f112|0|1|1|1|0|0||1
66:13:48:bd:a5:fd|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|fe80::1cc4:80a2:723:cc63|0|1|1|1|0|0||1
6a:c3:5f:8c:0e:01|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.167|0|1|1|1|0|0||1
ba:64:5d:52:c5:31|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.142|0|1|1|1|0|0||1
54:e0:19:f7:04:af|(unknown)|(unknown)||Ring LLC|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|2a02:c7f:98c2:2200:56e0:19ff:fef7:4af|0|1|1|1|0|0||1
26:7c:91:f0:bf:8c|RobertspleWatch.lan|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.79|0|1|1|1|0|0||1
d4:c9:4b:d4:8c:f0|(unknown)|(unknown)||Motorola Mobility LLC, a Lenovo Company|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.133|0|1|1|1|0|0||1
06:50:52:b4:17:f5|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.176|0|1|1|1|0|0||1
78:e3:de:5d:79:32|iPad.lan|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.38|0|1|1|1|0|0||1
pi@pihole:~ $ sqlite3 ~/pialert/db/pialert.db "SELECT * FROM DHCP_Leases"
1610747993|38:f9:d3:64:7d:a4|192.168.0.114|BGGLOBAL2050|01:38:f9:d3:64:7d:a4
1610742790|9c:b6:d0:de:6d:5d|192.168.0.55|ian-arch-XPS15-9560|01:9c:b6:d0:de:6d:5d
1610747993|64:4b:f0:13:b6:30|192.168.0.84|*|01:64:4b:f0:13:b6:30
1610744702|48:e1:e9:29:5d:4a|192.168.0.19|*|*
1610742524|5e:3f:a0:d0:16:5b|192.168.0.160|RobertHnsiPhone|01:5e:3f:a0:d0:16:5b
1610746057|48:e1:e9:2d:d1:e9|192.168.0.180|*|*
1610708150|48:e1:e9:2d:d4:af|192.168.0.108|*|*
1610715261|54:e0:19:5e:4d:fe|192.168.0.14|ChimePro-fe|00:05:54:e0:19:5e:4d:fe
1610748572|c6:31:2d:f3:98:f8|192.168.0.159|iPhone|01:c6:31:2d:f3:98:f8
1610742661|26:7c:91:f0:bf:8c|192.168.0.79|RobertspleWatch|01:26:7c:91:f0:bf:8c
1610713374|54:e0:19:6e:1a:53|192.168.0.2|54E0196E1A53-mysimplelink|*
1610734972|ea:c8:01:9b:49:34|192.168.0.168|HayleyspleWatch|01:ea:c8:01:9b:49:34
1610731899|14:59:c0:54:de:6a|192.168.0.130|*|*
1610722208|f8:0f:f9:93:80:ec|192.168.0.41|Google-Nest-Mini|*
1610715740|3c:89:94:0d:ed:f1|192.168.0.18|*|01:3c:89:94:0d:ed:f1
1610721375|6c:4a:85:31:87:16|192.168.0.56|Living-Room|01:6c:4a:85:31:87:16
1610734959|e8:9e:b4:be:92:4b|192.168.0.92|*|01:e8:9e:b4:be:92:4b
1610748555|00:1f:c5:f0:15:57|192.168.0.109|Wii|01:00:1f:c5:f0:15:57
1610714224|86:65:d0:0e:40:50|192.168.0.112|iPad|01:86:65:d0:0e:40:50
1610736868|ac:63:be:3a:ed:90|192.168.0.152|*|01:ac:63:be:3a:ed:90
1610747994|2|fd58:84d1:7017::1a8|*|00:01:00:01:24:27:bc:e4:38:f9:d3:64:7d:a4
1610742792|3134327015|2a02:c7f:98c2:2200::1f8|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe
1610742792|3134327015|fd58:84d1:7017::1f8|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe
1610742774|1134290498|2a02:c7f:98c2:2200::1d3|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe
1610742774|1134290498|fd58:84d1:7017::1d3|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe
1610722209|4187193580|fd58:84d1:7017::1cd|Google-Nest-Mini|00:03:00:01:f8:0f:f9:93:80:ec
1610722209|4187193580|2a02:c7f:98c2:2200::1cd|Google-Nest-Mini|00:03:00:01:f8:0f:f9:93:80:ec
pucherot commented 3 years ago

Hi Robert, thank you for your time.

A new version v2.56 is available. This version resolve this problem (one MAC with more than one DHCP_Leases):

1610742792|3134327015|2a02:c7f:98c2:2200::1f8|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe
1610742792|3134327015|fd58:84d1:7017::1f8|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe