Notepad
Task 1
sudo mkdir /mnt/task1
sudo mount -t ext2 image.ext2 /mnt/task1/
sudo cp /mnt/task1/root/.bash_history
vim>:sort u
sudo sha1sum /mnt/task1/etc/local.d/xmvcszwoar
27c1d05a428732d3c337738d3cf3c0bf56fb7f6a /mnt/task1/etc/local.d/xmvcszwoar
Task 2
tshark -r traffic.pcap -q -z ip_hosts,tree
=================================================================================================================================
IPv4 Statistics/All Addresses:
Topic / Item Count Average Min Val Max Val Rate (ms) Percent Burst Rate Burst Start
---------------------------------------------------------------------------------------------------------------------------------
All Addresses 1981 0.0131 100% 0.3200 88.763
172.19.1.230 1144 0.0076 57.75% 0.1400 124.740
192.168.2.50 415 0.0027 20.95% 0.1700 27.000
192.168.1.254 406 0.0027 20.49% 0.1200 109.812
192.168.2.254 386 0.0025 19.49% 0.1000 137.922
192.168.3.254 376 0.0025 18.98% 0.1400 124.740
172.19.1.5 316 0.0021 15.95% 0.2800 88.763
192.168.1.140 146 0.0010 7.37% 0.2800 88.763
255.255.255.255 117 0.0008 5.91% 0.0200 99.520
0.0.0.0 117 0.0008 5.91% 0.0200 99.520
192.168.4.1 73 0.0005 3.69% 0.0700 117.993
192.168.5.1 70 0.0005 3.53% 0.0900 131.567
172.19.1.254 50 0.0003 2.52% 0.0700 148.411
192.168.1.139 36 0.0002 1.82% 0.0800 134.274
91.189.91.83 32 0.0002 1.62% 0.0800 134.274
192.168.46.133 32 0.0002 1.62% 0.0200 99.643
52.123.251.5 30 0.0002 1.51% 0.0600 99.701
52.123.251.47 30 0.0002 1.51% 0.0700 101.815
52.123.251.45 30 0.0002 1.51% 0.0700 99.504
52.123.251.50 27 0.0002 1.36% 0.0500 107.153
192.168.46.2 24 0.0002 1.21% 0.0200 100.728
135.234.160.244 16 0.0001 0.81% 0.0400 136.323
135.233.45.221 16 0.0001 0.81% 0.0300 141.546
135.234.160.245 15 0.0001 0.76% 0.0400 135.204
52.123.251.72 12 0.0001 0.61% 0.0300 96.890
203.0.113.108 10 0.0001 0.50% 0.0300 151.344
192.168.3.89 10 0.0001 0.50% 0.0300 151.344
192.168.46.254 4 0.0000 0.20% 0.0200 99.643
192.168.3.241 4 0.0000 0.20% 0.0100 132.026
192.168.2.183 4 0.0000 0.20% 0.0100 74.928
172.178.240.162 4 0.0000 0.20% 0.0300 151.286
52.123.129.14 2 0.0000 0.10% 0.0100 79.307
52.123.128.14 2 0.0000 0.10% 0.0100 58.276
45.83.234.123 2 0.0000 0.10% 0.0100 55.350
216.31.17.12 2 0.0000 0.10% 0.0200 122.610
135.233.45.223 1 0.0000 0.05% 0.0100 127.084
104.208.16.88 1 0.0000 0.05% 0.0100 128.073
---------------------------------------------------------------------------------------------------------------------------------
Private IPs:
- 172
- 172.19.1.230 (ssh out (192.168.1.254, 192.168.2.254, 192.168.3.254))
- 172.19.1.5 (ftp)
- 172.19.1.254 (router2 backup)
- 192.168
- 192.168.2.50 (stor rfc, nbstat, ping, and 443)
- 192.168.1.254 (archive dns and ssh)
- 192.168.2.254 (archive dns and ssh)
- 192.168.3.254 (archive dns and ssh)
- 192.168.1.140 (enum FTP and retr rfc file)
- 192.168.4.1 (router1 conf)
- 192.168.5.1 (router3 conf)
- 192.168.1.139 (recv ubuntu debs) ppp
- 192.168.46.133 (microsoft DNS, DHCP, NTP)
- 192.168.46.2 (microsoft DNS)
- 192.168.3.89 (recv pubkey) ppp
- 192.168.46.254 (dhcp for 192.168.46.133)
- 192.168.3.241 (archive ubuntu dns) ppp
- 192.168.2.183 (archive ubuntu dns) ppp
192.168.46.133 dns corresponds to 192.168.2.50 https
172.19.1.230 > 192.168.1.254 (ssh): 192.168.4.1 > 172.19.1.5 (ftp)
172.19.1.230 > 192.168.3.254 (ssh): 192.168.5.1 > 172.19.1.5 (ftp)
172.19.1.230 > 192.168.2.254 (ssh): 172.19.1.254 > 172.19.1.5 (ftp)
ppp: sll.hatype == 512
Router1 conf:
config interface 'loopback'
option device 'lo'
option proto 'static'
option ipaddr '127.3.1.1'
option netmask '255.0.0.0'
config globals 'globals'
option ula_prefix 'fd10:95e0:f1b::/48'
option packet_steering '1'
config device
option name 'br-lan'
option type 'bridge'
list ports 'eth0'
config interface 'lan'
option device 'br-lan'
option proto 'static'
option ipaddr '192.168.1.254'
option netmask '255.255.255.0'
option ip6assign '60'
config interface 'to_devnet'
option proto 'static'
option device 'eth1'
option ipaddr '192.168.4.1'
option netmask '255.255.255.240'
config route
option interface 'lan'
option target '192.168.1.0/24'
option gateway '192.168.1.254'
config route 'to_lan2'
option target '0.0.0.0/0'
option gateway '192.168.4.2'
option interface 'to_devnet'
Router2 conf
config interface 'loopback'
option device 'lo'
option proto 'static'
option ipaddr '127.8.1.2'
option netmask '255.0.0.0'
config globals 'globals'
option ula_prefix 'fd45:7f60:3ad5::/48'
option packet_steering '1'
config device
option name 'br-lan'
option type 'bridge'
list ports 'eth0'
config interface 'lan'
option device 'br-lan'
option proto 'static'
option ipaddr '192.168.2.254'
option netmask '255.255.255.0'
config interface 'wan'
option device 'eth1'
option proto 'dhcp'
config interface 'to_devnet'
option proto 'static'
option device 'eth1'
list ipaddr '172.16.1.254/24'
config interface 'to_lan1'
option proto 'static'
option device 'eth2'
list ipaddr '192.168.4.2/28'
config interface 'to_lan3'
option proto 'static'
option device 'eth3'
list ipaddr '192.168.5.2/28'
config interface 'to_host_nat'
option proto 'dhcp'
option device 'eth4'
config route 'route_to_devnet'
option interface 'to_devnet'
option target '172.16.1.0/24'
option gateway '172.16.1.254'
config route
option interface 'lan'
option target '192.168.2.0/24'
option gateway '192.168.2.254'
config route 'route_to_lan1'
option target '192.168.1.0/24'
option gateway '192.168.4.1'
option interface 'to_lan1'
config route
option target '192.168.3.0/24'
option gateway '192.168.5.1'
option interface 'to_lan3'
Router3 conf
config interface 'loopback'
option device 'lo'
option proto 'static'
option ipaddr '127.9.5.3'
option netmask '255.0.0.0'
config globals 'globals'
option ula_prefix 'fdf2:87c7:eb73::/48'
option packet_steering '1'
config device
option name 'br-lan'
option type 'bridge'
list ports 'eth0'
config interface 'lan'
option device 'br-lan'
option proto 'static'
option ipaddr '192.168.3.254'
option netmask '255.255.255.0'
option ip6assign '60'
config interface 'to_openwrt2'
option device 'eth1'
option proto 'static'
list ipaddr '192.168.5.1/28'
config interface 'host_nat'
option proto 'dhcp'
option device 'eth2'
config route
option target '192.168.3.0/24'
option gateway '192.168.3.254'
option interface 'lan'
config route
option target '0.0.0.0/0'
option gateway '192.168.5.2'
option interface 'to_openwrt2'
Guesses
192.168.2.50
192.168.2.50
192.168.2.50
192.168.1.140
192.168.46.254
192.168.46.2
192.168.46.133
192.168.3.89
203.0.113.108
192.168.46.133
192.168.1.139
192.168.3.89
192.168.3.241
192.168.2.183
172.19.1.230
192.168.4.1
192.168.5.1
172.19.1.254
172.19.1.230
172.19.1.5
192.168.4.1
192.168.5.1
172.19.1.254
172.16.1.5
172.16.1.230
192.168.46.133
192.168.2.50
53.123.129.14
53.123.128.14
172.19.1.230
172.19.1.5
172.19.1.254
192.168.46.133
192.168.1.139
192.168.3.89
192.168.3.241
192.168.2.183
192.168.2.50
192.168.1.140
192.168.46.2
216.31.17.12
45.83.234.123
172.19.1.230
192.168.1.254
192.168.3.254
192.168.2.254
192.168.4.1
192.168.5.1
172.19.1.254
172.19.1.5
172.19.1.230
192.168.4.1
192.168.5.1
172.19.1.254
172.19.1.5
172.19.1.230
192.168.4.1
192.168.5.1
172.19.1.5
192.168.4.1
192.168.5.1
172.19.1.254
172.19.1.5
192.168.2.50
192.168.1.139
192.168.46.133
192.168.3.89
192.168.3.241
192.168.2.183
172.19.1.230
192.168.3.89
192.168.2.50
172.16.1.254
192.168.4.2
192.168.5.2
192.168.46.133
# Right track! More IPs associated with it
192.168.3.254
192.168.5.1
192.168.3.254
192.168.5.1
192.168.5.2
127.9.5.3
192.168.3.254
192.168.5.1
192.168.5.2
127.9.5.3
# Correct! 192.168.5.2 was the router it bridges to
192.168.3.254
192.168.5.1
127.9.5.3
172.16.1.5 mac == 172.19.1.5 mac
172.19.1.230
172.19.1.254
172.19.1.5
192.168.1.139
192.168.1.140
192.168.1.254
192.168.2.183
192.168.2.254
192.168.2.50
192.168.3.241
192.168.3.254
192.168.3.89
192.168.4.1
192.168.46.133
192.168.46.2
192.168.46.254
192.168.5.1
The debs do enum and RMM
Task 3
memory.dump.gz: gzip compressed data, was ".tmp_r2pnyfai", max compression, original size modulo 2^32 151193723
System.map.br: data
vmlinux.xz: XZ compressed data, checksum CRC64
sudo apt install crash
ps
set <pid>
vm
rd <start_addr> -e <end_addr> [-r <filename>]
void FUN_001012bf(void)
{
int local_18;
int local_14;
int local_10;
int local_c;
for (local_18 = 0; local_18 < 0x100; local_18 = local_18 + 1) {
*(undefined *)((long)local_18 + 0x104040) = 0xff;
}
for (local_14 = 0x41; local_14 < 0x5b; local_14 = local_14 + 1) {
*(char *)((long)local_14 + 0x104040) = (char)local_14 + -0x41;
}
for (local_10 = 0x61; local_10 < 0x7b; local_10 = local_10 + 1) {
*(char *)((long)local_10 + 0x104040) = (char)local_10 + -0x47;
}
for (local_c = 0x30; local_c < 0x3a; local_c = local_c + 1) {
*(char *)((long)local_c + 0x104040) = (char)local_c + '\x04';
}
DAT_0010406b = 0x3e;
DAT_0010406f = 0x3f;
DAT_0010407d = 0xfe;
return;
}
Result (0x104040 -> 0x557fab56c040):
557fab56c040: ffffffffffffffff ffffffffffffffff ................
557fab56c050: ffffffffffffffff ffffffffffffffff ................
557fab56c060: ffffffffffffffff 3fffffff3effffff ...........>...?
557fab56c070: 3b3a393837363534 fffffeffffff3d3c 456789:;<=......
557fab56c080: 06050403020100ff 0e0d0c0b0a090807 ................
557fab56c090: 161514131211100f ffffffffff191817 ................
557fab56c0a0: 201f1e1d1c1b1aff 2827262524232221 ....... !"#$%&'(
557fab56c0b0: 302f2e2d2c2b2a29 ffffffffff333231 )*+,-./0123.....
557fab56c0c0: ffffffffffffffff ffffffffffffffff ................
557fab56c0d0: ffffffffffffffff ffffffffffffffff ................
557fab56c0e0: ffffffffffffffff ffffffffffffffff ................
557fab56c0f0: ffffffffffffffff ffffffffffffffff ................
557fab56c100: ffffffffffffffff ffffffffffffffff ................
557fab56c110: ffffffffffffffff ffffffffffffffff ................
557fab56c120: ffffffffffffffff ffffffffffffffff ................
557fab56c130: ffffffffffffffff ffffffffffffffff ................
203.0.113.202 ports.ubuntu.org
203.0.113.202 http.kali.org
203.0.113.228 mirrors.rockylinux.org
203.0.113.228 mirrors.opensuse.org
203.0.113.202 archive.archlinux.org
203.0.113.202 distfiles.gentoo.org
203.0.113.202 deb.debian.org
203.0.113.202 ftp.us.debian.org
203.0.113.229 pypi.io
203.0.113.228 xmirror.voidlinux.org
203.0.113.202 archive.ubuntu.com
203.0.113.202 dl-cdn.alpinelinux.org
203.0.113.229 pypi.org
203.0.113.229 files.pythonhosted.org
203.0.113.202 repo.almalinux.org
203.0.113.229 pypi.python.org
203.0.113.202 security.debian.org
203.0.113.202 cache.nixos.org
203.0.113.202 dl.rockylinux.org
203.0.113.202 ports.ubuntu.com
203.0.113.202 repos.opensuse.org
203.0.113.202 packages.linuxmint.com
203.0.113.228 mirror.rackspace.com
203.0.113.228 mirror.stream.centos.org
203.0.113.228 geo.mirror.pkgbuild.com
203.0.113.228 mirrors.kernel.org
203.0.113.202 download.opensuse.org
203.0.113.228 mirrors.rpmfusion.org
203.0.113.202 us.archive.ubuntu.com
203.0.113.202 repo-default.voidlinux.org
203.0.113.202 security.ubuntu.org
203.0.113.202 archive.ubuntu.org
203.0.113.228 mirrors.alpinelinux.org
203.0.113.202 download1.rpmfusion.org
203.0.113.202 security.ubuntu.com
203.0.113.202 dl.fedoraproject.org
203.0.113.228 mirrors.fedoraproject.org
Task 4
local_128 = 5a0c5e4f5a0717175b00420d0c40151a5b574d05560b085c0c
Checks TracerPid in /proc/self/status for debugging
NOTE FOR LATER: Mark all of the places you need to get around antidebugging
set breakpoint pending on
ip route add 203.0.113.42/255.255.255.255 via 127.0.0.1
Task 5
generate_key only gives 26 bit key
r ../task4/payload _ZN5Comms12send_messageEPKhmPi
0x4552eeffc0dec0de 0x0000004e4e4f4351
ciphertext: 3352e725402660dac150b655ddba2d8a0ccee403f9c6819167a8386bbe96cb22
ciphertext first block: 3352E725402660DAC150B655DDBA2D8A
plaintext: DEC0DEC0FFEE524551434F4E4E030303
python MITM_memory.py --ciphertext 3352E725402660DAC150B655DDBA2D8A --plaintext-hex DEC0DEC0FFEE524551434F4E4E000000
2000000
test key1: 0x2e76a2
test key2: 0x3b88a39
test enc:
0x55555558cee0: 0xad10ea18af9d8229 0x94945e2012235913
0x55555558cef0: 0x8364ac42a1129557 0xc3fb207d83da4fad
https://cyberchef.org/#recipe=From_Hex(‘Auto’)Reverse(‘Byte’)AES_Decrypt(%7B’option’:’Hex’,’string’:’398ab803000000000000000000000000’%7D,%7B’option’:’Hex’,’string’:’‘%7D,’ECB/NoPadding’,’Raw’,’Raw’,%7B’option’:’Hex’,’string’:’‘%7D,%7B’option’:’Hex’,’string’:’‘%7D)AES_Decrypt(%7B’option’:’Hex’,’string’:’a2762e00000000000000000000000000’%7D,%7B’option’:’Hex’,’string’:’‘%7D,’ECB/NoPadding’,’Raw’,’Hex’,%7B’option’:’Hex’,’string’:’‘%7D,%7B’option’:’Hex’,’string’:’‘%7D)&input=OTQ5NDVlMjAxMjIzNTkxM2FkMTBlYTE4YWY5ZDgyMjk
https://www.ibm.com/docs/en/zos/2.4.0?topic=rules-pkcs-padding-method
https://linux.die.net/man/3/evp_encryptinit_ex
Real K1: 4d7ab103000000000000000000000000
Real K2: c575e802000000000000000000000000
https://cyberchef.org/#recipe=AES_Decrypt(%7B'option':'Hex','string':'c575e802000000000000000000000000'%7D,%7B'option':'Hex','string':''%7D,'ECB/NoPadding','Hex','Raw',%7B'option':'Hex','string':''%7D,%7B'option':'Hex','string':''%7D)AES_Decrypt(%7B'option':'Hex','string':'4d7ab103000000000000000000000000'%7D,%7B'option':'Hex','string':''%7D,'ECB/NoPadding','Raw','Raw',%7B'option':'Hex','string':''%7D,%7B'option':'Hex','string':''%7D)&input=ZGRmZTljN2UxMDQ3YmMzNGNhYWI2ZWQ2NTJhMTg1YTY2NDk4NmNjYzYyOTQ4NjUyYTc0ZTk1MDc5ZDcwMTRiNzMzODkwYTdiM2MwNjQ4Nzc5NTJkYjI5YzBjMmMwZGNmYTMzZGNhNjc4NDcwYmQyMzE2YTkyM2QxY2I0OGRiOTMwY2NlZTQwM2Y5YzY4MTkxNjdhODM4NmJiZTk2Y2IyMg
https://198.51.100.166/mattermost/4KkZ7UcHVheeX
Task 6
sudo su postgres
strings volumes/db/var/lib/postgresql/data/global/*
psql -U mmuser -d mattermost
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
------------+--------+----------+------------+------------+------------+-----------------+-------------------
mattermost | mmuser | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
postgres | mmuser | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
template0 | mmuser | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/mmuser +
| | | | | | | mmuser=CTc/mmuser
template1 | mmuser | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/mmuser +
| | | | | | | mmuser=CTc/mmuser
mattermost=# SELECT id, username, password, email FROM users;
id | username | password | email
----------------------------+------------------------+--------------------------------------------------------------+------------------------------------
pfygf98wribzjmbphye811kdio | ardentllama44 | $2a$10$kQhPi0QNRzXc7a.cDWX.yubn4LOOWqQu5tea4ruH4ksXn7hFQJ/7m | ardentllama44@malware.xyz
mizz7h1izff1zgo6bdkznsmbxo | system-bot | | system-bot@localhost
4318ziioutrfzygrfn7o6nnhya | selfishquiche76 | $2a$10$VdlsT4bcSViY5spxfNdRLOi9CpbW2jPCWE5YYu8H4y7NAUrMkbsNm | selfishquiche76@malware.xyz
unms75xys3djx8edzp9jrjcj8r | troubledpudding23 | $2a$10$FHaq3bOCfoQH43mfLE3Wj.uG/OgWt/FENp.vcHTLDOwq1DnFUlWr. | troubledpudding23@malware.xyz
dpkzdafmubdjdbyuujtshdx9uy | annoyedlard10 | $2a$10$hZims11Pfkf1UBDM2d8v5eGpEnXo6f1MT6EiUJ4VNOcNW6ZNYl.8O | annoyedlard10@malware.xyz
5qnkp6anopg9pgepmiz75qcp1r | decimallapwing94 | $2a$10$diTMc1VQNse/01HPvxQ30OXKXEC32hp2fOT6usWwcfgWeYFyVQh.u | decimallapwing94@malware.xyz
qf1dn81aoiyn8fbu8hw9wbdq5e | boredpaella14 | $2a$10$da5uurHiXEOs0N6lMQdzO.KraF2h11w20aeWl.Bh./RFwwk4AUpEq | boredpaella14@malware.xyz
48g9eji4x3gbxyjohfcscrgkfa | worriedunicorn63 | $2a$10$yanss3oblYQSO00b.oJm0eobGAqC/xLAu.UvLTNlQC6GicV/xPhPa | worriedunicorn63@malware.xyz
qhrghwwdkfdat8rq6x31rbtzcw | mod_wrathfulmandrill27 | $2a$10$4s6muwKIdl407jqkIgRSfOoWRjRMB58MdeSyc8ePr9HCevmW5mL/m | mod_wrathfulmandrill27@malware.xyz
rics9ysu1pbjfpijxug37kas7e | obsessedtortoise46 | $2a$10$cFpuECxyMVJvQVB1lezBSe5DtDxAZpymZX6lH4CL8R5aCclPhK55e | obsessedtortoise46@malware.xyz
ouqnuo8ywfbx8mnzed97qg6tha | drearyiguana70 | $2a$10$F1wYCN9VLS/9nLlkAKOIfe3lvgNZ7Z66fqMJZy5qTUOcEUG9XIP8C | drearyiguana70@malware.xyz
t9fg5njtjtbn7jed7jwwiw8tbe | excludedpup84 | $2a$10$CN9SLyFx496T1u0TCkRUvu1zQFfhA63vBUAyy5phbpskYzu9Nllbe | excludedpup84@malware.xyz
fora3hh7zbnmdgxyc8xsywg9rr | mod_jealouscheese66 | $2a$10$YDYSd0klTIMgmC3f1H31o.6/tqbaatZNTpCpCP9y9sUyPNTcQpMze | mod_jealouscheese66@malware.xyz
54umzkxnu38xjd8i941fn6kxrr | mod_betrayedwildfowl53 | $2a$10$szJlBFPKejZ/Ru6FEHOU.e/xeE74UmxkqP4HBrzlzvlEM2tEzJjMG | mod_betrayedwildfowl53@malware.xyz
3juzgoax4jy7tyqbtrkbdxs53o | mod_crummyunicorn95 | $2a$10$CGzGPaHiy.1a1xUOymDEauFVPOU.6o6w82jsIEF9DnmutabtOdhci | mod_crummyunicorn95@malware.xyz
ojeds8ai9fn3xqfqig1txnrqzo | mod_exactingrelish3 | $2a$10$Lx8nAHmaP3s3CPhxd4MtDO8f/hucioQwqUrwm2Z72U032bMvCLMgC | mod_exactingrelish3@malware.xyz
66sd3h9jkbr1pe3rk9wh3hmj4o | mod_selfishleopard67 | $2a$10$2jTIeXZOncUECbOjAEAh.O9izzdCBEnuxrI5SxlgtHHkolOnIJRf. | mod_selfishleopard67@malware.xyz
4ebi6ioaqjf7xkz3tk8orbcgay | admin_resolvedcaviar11 | $2a$10$95eaoS66C.uA1RHFw4wnUelm4jca1d8x1isvTiQPD6Jnk.x1fXQ4. | admin_resolvedcaviar11@malware.xyz
ajx5fwnh9jnadxrnzs45tnbsnh | malbot | | malbot@localhost
e1kcdjx64fgqjpjtt1w6i8zq7h | thrilledviper68 | $2a$10$cvs8yjZPsYzVaRRVQ153vOHlwg4zDPwOuDiQW9As88h1Yx/86hs/C | thrilledviper68@malware.xyz
teams: malwarecentral
threads: N/A
channels: 1ryjq95fgjnpmefdw7njhmpquh : channel80970 (active)
commands: N/A
drafts: N/A
groupchannels: N/A
groupmembers: N/A
posts:
public | roles | table | mmuser
public | scheduledposts | table | mmuser
public | schemes | table | mmuser
public | sessions | table | mmuser
public | sharedchannelattachments | table | mmuser
public | sharedchannelremotes | table | mmuser
public | sharedchannels | table | mmuser
public | sharedchannelusers | table | mmuser
public | sidebarcategories | table | mmuser
public | sidebarchannels | table | mmuser
public | status | table | mmuser
public | systems | table | mmuser
public | teammembers | table | mmuser
public | teams | table | mmuser
public | termsofservice | table | mmuser
public | threadmemberships | table | mmuser
public | threads | table | mmuser
public | tokens | table | mmuser
public | uploadsessions | table | mmuser
public | useraccesstokens | table | mmuser
public | usergroups | table | mmuser
public | users | table | mmuser
public | usertermsofservice | table | mmuser
mattermost=# SELECT id, name, totalmsgcount FROM channels;
id | name | totalmsgcount
----------------------------+--------------------------------------------------------+---------------
t63zfdcc6tbxmddr385ix8faso | channel59087 | 3
w5s1q84j1t8kfpkx3wgd5azehr | 4ebi6ioaqjf7xkz3tk8orbcgay__ajx5fwnh9jnadxrnzs45tnbsnh | 1
8fkydnsj87gzdeub4x357ykmyo | public | 39
inmiqju8midodd4j1u58hixidw | channel2951 | 3
sjw8t1woyffi9b68ttsuz4mc3e | channel72204 | 3
yb9p51ujcfrtxrtq3o3on5xk8o | channel34388 | 3
teot7n4kgjngjjrpqd3rmnm58r | channel18129 | 3
rq1pjjwhqiyw5kmedeby71nt4w | channel48858 | 3
eqbdz9bzt3gnjccn4fs498f1ye | channel92689 | 3
win83i8d8tg88fejdgb7idppac | channel67298 | 3
idbbj6rc8f88mcexixftrbzo5o | channel51200 | 3
dp8eoqaww3f6ufpcujb9gcq19r | channel3615 | 3
abjs11bgjjnqpern9jd9rrnaxw | channel17882 | 3
iwpoqyb93pgk5gr8oa7x43umjh | channel93925 | 3
6e4qidms13gyidkrsnbwaffmxe | channel61235 | 3
gh3g715br7d97mdfagk9jtnway | channel78613 | 3
3k88jyrzjtrx7pcnfwe8b5gx7o | channel28964 | 3
5n8w1bigsfryinjccesa5yyqfa | channel39076 | 3
3xc1ow4ywibymqz6iixeg98d8c | channel47272 | 3
1ryjq95fgjnpmefdw7njhmpquh | channel80970 | 15
fonezgi6xjg8tc1fm1yrjrwekh | channel90912 | 3
wa9uaymm6jdu9ydcyeunmy9xba | channel98785 | 3
y595n7xzz3grbbz78s4b9tfwmo | channel92060 | 3
zuneyr7q4in65rgwgs5mczqaae | channel99945 | 3
5i6do9di7jd5tqrpqtydsycgsc | channel43821 | 3
mattermost=# SELECT userid, channelid FROM channelmembers WHERE channelid='1ryjq95fgjnpmefdw7njhmpquh';
userid | channelid
----------------------------+----------------------------
dpkzdafmubdjdbyuujtshdx9uy | 1ryjq95fgjnpmefdw7njhmpquh
ojeds8ai9fn3xqfqig1txnrqzo | 1ryjq95fgjnpmefdw7njhmpquh
qhrghwwdkfdat8rq6x31rbtzcw | 1ryjq95fgjnpmefdw7njhmpquh
4318ziioutrfzygrfn7o6nnhya | 1ryjq95fgjnpmefdw7njhmpquh
fora3hh7zbnmdgxyc8xsywg9rr | 1ryjq95fgjnpmefdw7njhmpquh
3juzgoax4jy7tyqbtrkbdxs53o | 1ryjq95fgjnpmefdw7njhmpquh
66sd3h9jkbr1pe3rk9wh3hmj4o | 1ryjq95fgjnpmefdw7njhmpquh
rics9ysu1pbjfpijxug37kas7e | 1ryjq95fgjnpmefdw7njhmpquh
qf1dn81aoiyn8fbu8hw9wbdq5e | 1ryjq95fgjnpmefdw7njhmpquh
ouqnuo8ywfbx8mnzed97qg6tha | 1ryjq95fgjnpmefdw7njhmpquh
unms75xys3djx8edzp9jrjcj8r | 1ryjq95fgjnpmefdw7njhmpquh
48g9eji4x3gbxyjohfcscrgkfa | 1ryjq95fgjnpmefdw7njhmpquh
4ebi6ioaqjf7xkz3tk8orbcgay | 1ryjq95fgjnpmefdw7njhmpquh
ajx5fwnh9jnadxrnzs45tnbsnh | 1ryjq95fgjnpmefdw7njhmpquh
pfygf98wribzjmbphye811kdio | 1ryjq95fgjnpmefdw7njhmpquh
mattermost=# SELECT id, username FROM users WHERE NOT (id='dpkzdafmubdjdbyuujtshdx9uy' OR id='ojeds8ai9fn3xqfqig1txnrqzo' OR id='qhrghwwdkfdat8rq6x31rbtzcw' OR id='4318ziioutrfzygrfn7o6nnhya' OR id='fora3hh7zbnmdgxyc8xsywg9rr' OR id='3juzgoax4jy7tyqbtrkbdxs53o' OR id='66sd3h9jkbr1pe3rk9wh3hmj4o' OR id='rics9ysu1pbjfpijxug37kas7e' OR id='qf1dn81aoiyn8fbu8hw9wbdq5e' OR id='ouqnuo8ywfbx8mnzed97qg6tha' OR id='unms75xys3djx8edzp9jrjcj8r' OR id='48g9eji4x3gbxyjohfcscrgkfa' OR id='4ebi6ioaqjf7xkz3tk8orbcgay' OR id='ajx5fwnh9jnadxrnzs45tnbsnh' OR id='pfygf98wribzjmbphye811kdio');
id | username
----------------------------+------------------------
mizz7h1izff1zgo6bdkznsmbxo | system-bot
5qnkp6anopg9pgepmiz75qcp1r | decimallapwing94
t9fg5njtjtbn7jed7jwwiw8tbe | excludedpup84
54umzkxnu38xjd8i941fn6kxrr | mod_betrayedwildfowl53
e1kcdjx64fgqjpjtt1w6i8zq7h | thrilledviper68
Get users in both channels
SELECT u.username
FROM users u
JOIN channelmembers cm ON u.id = cm.userid
JOIN channels c ON cm.channelid = c.id
WHERE c.name IN ('public', 'channel80970')
GROUP BY u.id, u.username
HAVING COUNT(DISTINCT c.name) = 2;
Get channels with all 3 users
SELECT c.name
FROM channels c
JOIN channelmembers cm ON c.id = cm.channelid
JOIN users u ON cm.userid = u.id
WHERE u.username IN ('decimallapwing94', 'excludedpup84', 'mod_betrayedwildfowl53')
GROUP BY c.id, c.name
HAVING COUNT(DISTINCT u.username) = 3;
Get users in channel
SELECT u.username
FROM users u
JOIN channelmembers cm ON u.id = cm.userid
JOIN channels c ON cm.channelid = c.id
WHERE c.name = 'your_channel_name';
Get users not in channel
SELECT u.username
FROM users u
WHERE u.id NOT IN (
SELECT cm.userid
FROM channelmembers cm
JOIN channels c ON cm.channelid = c.id
WHERE c.name = 'channel80970'
);
In A, but not B
SELECT u.username
FROM users u
JOIN channelmembers cm ON u.id = cm.userid
JOIN channels c ON cm.channelid = c.id
WHERE c.name = 'ChannelA'
AND u.id NOT IN (
SELECT u2.id
FROM users u2
JOIN channelmembers cm2 ON u2.id = cm2.userid
JOIN channels c2 ON cm2.channelid = c2.id
WHERE c2.name = 'ChannelB'
);
First pivot possible:
4ebi6ioaqjf7xkz3tk8orbcgay__ajx5fwnh9jnadxrnzs45tnbsnh (?)
channel39076 ardentllama44 worriedunicorn63 mod_selfishleopard67
Second pivot possible
channel59087 worriedunicorn63 drearyiguana70 mod_jealouscheese66
Third pivot possible
channel28964 drearyiguana70 decimallapwing94 mod_crummyunicorn95
Fourth pivot possible
channel80970 decimallapwing94 excludedpup84 mod_betrayedwildfowl53
(goal. Need to find a channel with all 3 and join it. Command before needs to find a channel without 3 users in public where at least one is a mod)
!nego channel39076 ardentllama44 worriedunicorn63 mod_selfishleopard67
/join ~channel39076
!nego channel59087 worriedunicorn63 drearyiguana70 mod_jealouscheese66
/join ~channel59087
!nego channel28964 drearyiguana70 decimallapwing94 mod_crummyunicorn95
/join ~channel28964
!nego channel80970 decimallapwing94 excludedpup84 mod_betrayedwildfowl53
/join ~channel80970