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
# Change date in the WHERE clause and number in LIMIT
# This query below retrieves the top 100 URLS after the date specified
WHERE event.timestamp > '2013-03-01'
AND event.signature LIKE '%URL%'
GROUP BY event.signature
ORDER By count(*) desc
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.
Did you ever need to examine one line from a MySQL table and were unable to line up the column headers with the data because the line was too long?
Well no fears, it turns out the MySQL client has a switch (\G) you can use to arrange output vertically for easier reading. This is very handy when you are looking at only one line. Here’s an example:
mysql> SELECT * FROM emp_vw WHERE id = 10110 LIMIT 1 \G
*************************** 1. row ***************************
address: 277 142nd Ave
city: New York
timestamp: 2011-11-30 18:58:50
1 row in set (0.01 sec)
This is a great way to examine the content of the fields of any table for the purposes of investigation. It works very well for views and tables with many columns that form long lines when displayed horizontally. This can also be applied to your my.cnf file, but I prefer to leave it off so it can be enable with the MySQL client \G switch only when needed.