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