Here’s a quick way to get a list of the top URLs used in a system that is monitoring traffic with SGUIL. Login to the MySQL database and query the “event” table for signatures that have “URL” in them. The example below is for SecurityOnion.
# In mysql, gets the top urls # -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 in the WHERE clause and number in LIMIT # This query below retrieves the top 100 URLS after the date specified SELECT event.signature, count(*) FROM event WHERE event.timestamp > '2013-03-01' AND event.signature LIKE '%URL%' GROUP BY event.signature ORDER By count(*) desc LIMIT 100;
This is a good quick way to find out what people are requesting. However, because so many CDN’s, ad servers and trackers use multiple hostnames, you don’t get the big picture of what is coming from just the domain itself. This is great if you are retrieving statistics to help you tune performance on your ad blocker or web proxy cache.