Find Conficker Infected Machines with SGUIL

This command line query for mysql will grab for you a list of conficker infected machines for a given date range, their IP address, the count of events in the logs for each machine, and sort them by the biggest offenders. Distribution is SecurityOnion Linux.

# -A turns off "reading table information for completion of table and column names" for faster DB selection
 
mysql -uroot -A
 
use securityonion_db;

# change date range as needed
SELECT 
  INET_NTOA(event.src_ip),
  count(INET_NTOA(event.src_ip)) AS total
FROM event IGNORE INDEX (event_p_key, sid_time) 
WHERE event.timestamp > '2013-04-15' AND event.timestamp < '2013-04-16' 
AND  event.signature like '%Conficker%' 
GROUP BY INET_NTOA(event.src_ip)
ORDER BY INET_NTOA(event.src_ip) ASC

I was unable to get a list of host names of machines, which would have been nice when you have a large list of DHCP clients and aren’t looking at this query until many days after.

Another important query for security purposes is to obtain a list of IP addresses which the Conficker infected machines on your network are trying to contact. In this case I’m going to leave out the date range condition since I’m looking for the IP’s that have had the most activity of all time so I can ban them.  Network wide. For fun. Just ’cause I can.

SELECT 
  INET_NTOA(event.dst_ip),
  count(INET_NTOA(event.dst_ip)) AS total
FROM event IGNORE INDEX (event_p_key, sid_time) 
WHERE event.signature like '%Conficker%' 
GROUP BY INET_NTOA(event.dst_ip)
ORDER BY INET_NTOA(event.dst_ip) ASC