glupteba.sql

-- IOCs complied from https://github.com/sophoslabs/IoCs/blob/master/Trojan-Glupteba
-- Collected 2020-06-26, 4pm US Eastern time

-- Create temp table with necessary columns
CREATE TABLE glupteba (domain text, SHA text, reg text, file text);
INSERT INTO glupteba (domain,SHA,reg,file) VALUES ('domain','SHA','reg','file');

-- Search Sophos DNS journal over the last 90 days for domain IOCs
UPDATE glupteba SET domain = (SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT) as 'Exists' FROM sophos_dns_journal WHERE (
		name like '%1.podcast.best%' OR
		name like '%anotheronedom.com%' OR
		name like '%bestblues.tech%' OR
		name like '%easywbdesign.com%' OR
		name like '%gamedate.xyz%' OR
		name like '%getfixed.xyz%' OR
		name like '%gfixprice.xyz%' OR
		name like '%maxbook.space%' OR
		name like '%robotatten.com%' OR
		name like '%sleepingcontrol.com%' OR
		name like '%sndvoices.com%' OR
		name like '%whitecontroller.com%' OR
		name like '%myonetime.top%' OR
		name like '%venoxcontrol.com%')
		AND time > STRFTIME('%s','NOW','-90 days'));
		
-- Search Sophos file hash journal over the last 90 days for SHA IOCs
UPDATE glupteba SET SHA = (SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT) as 'Exists' FROM sophos_file_hash_journal WHERE (
		sha256 = '73fddd441a764e808ed6d6b8f3d0d13713e61221aa3cfef7da91cdaf112fe061' OR
		sha256 = '414035cc96d8bcc87ed173852a839ffbb45882a98c7a6f7b821e1668891deef0' OR
		sha256 = '04d71e8af8b5cbec912b82b6ebef7c19c5b888873dfd4609b1e38b2a6c398b2e' OR
		sha256 = '0b2a84359501923d1aa6ccd4e03b3f1b619e01d978efae45feea34a4d0ffed04' OR
		sha256 = '20e983e90144c385996eeb2edb584d654d898c34725e149682170f870ee12870' OR
		sha256 = '407c70f0c1a1e34503dae74dd973cf037d607e3c4deb8f063d33f2142f1baf71' OR
		sha256 = '6b0d90a0571ec870fa26372a1c5d83d06e8febca130a8f710e0c389a3054e05c' OR
		sha256 = '83bbe9e7b7967ecbc493f8ea40947184c6c7346c6084431fceea0401a6279d29' OR
		sha256 = '8d19c59db26a3e0a3251c5f05e143558bf009ed0b46fb9b6151f98441407ae8b' OR
		sha256 = '5e541d1ab46ab3d58e4889b08f5f4427d38afe8320582a63d992eda172af6c7f' OR
		sha256 = '9e4f09faee3eba3ae271b241cbaf0cb3621845ef83608a8abb3df8791e6c36e1' OR
		sha256 = 'dec11036bca8384f81c0c1d534e1f37fd2864c974dad020f32b835af3c7c4e28' OR
		sha256 = 'eb35bb221de38f5953f923cd349b4c85a50145329152a8aaa01e4cd8602a560e' OR
		sha256 = '469953521e9b64eac07f02fecf3488406c65ec1f3d5c182363c8ba0664a4b640')
		AND time > STRFTIME('%s','NOW','-90 days'));
		
-- Search Sophos registry journal over the last 1 day for registry IOCs
UPDATE glupteba SET reg = (SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT) as 'Exists' FROM sophos_registry_journal WHERE (
		keyname like '%InstallKey%' OR
		keyname like '%RegisterAppOk%' OR
		keyname like '%RegisterAppProcessing%' OR
		keyname like '%TestApp%')
		AND time > STRFTIME('%s','NOW','-1 days'));
		
-- Search Sophos file journal over the last 4 hours for file IOCs being created
UPDATE glupteba SET file = (SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT) as 'Exists' FROM sophos_file_journal WHERE (
		pathname like '%cloudnet.exe%' OR
		pathname like '%dsefix.exe%' OR
		pathname like '%e7.exe%' OR
		pathname like '%windefender.exe%' OR
		pathname like '%Winmon.sys%' OR
		pathname like '%WinmonFS.sys%' OR
		pathname like '%WinmonFS32.sys%' OR
		pathname like '%WinmonFS64.sys%' OR
		pathname like '%WinmonProcessMonitor32.sys%' OR
		pathname like '%WinmonProcessMonitor64.sys%' OR
		pathname like '%WinmonSystemMonitor-10-64.sys%' OR
		pathname like '%WinmonSystemMonitor-7-10-32.sys%' OR
		pathname like '%WinmonSystemMonitor-7-64.sys%' OR
		pathname like '%deps.zip%')
		AND time > STRFTIME('%s','NOW','-4 hours'));

-- Compile results and make display more friendly
SELECT
	CASE domain
	WHEN '0' THEN 'Domain IOC NOT present'
	WHEN '1' THEN 'Domain IOC IS present'
	ELSE 'Error'
	END AS 'Domain IOC present',
	CASE SHA
	WHEN '0' THEN 'SHA IOC NOT present'
	WHEN '1' THEN 'SHA IOC IS present'
	ELSE 'Error'
	END AS 'SHA IOC present',
	CASE reg
	WHEN '0' THEN 'Registry IOC NOT present'
	WHEN '1' THEN 'Registry IOC IS present'
	ELSE 'Error'
	END AS 'Registry IOC present',
	CASE file
	WHEN '0' THEN 'File IOC NOT present'
	WHEN '1' THEN 'File IOC IS present'
	ELSE 'Error'
	END AS 'File IOC present'
	FROM glupteba where (domain = '1' OR SHA = '1' OR reg = '1' OR file = '1');
	
-- Clean up temp table
DROP TABLE glupteba;