The SonicWall RAM log doesn’t scroll back too far, and if you are utilizing the SonicWall ViewPoint service on your network, you typically wouldn’t have a secondary SysLog receiver to gather log information. ViewPoint provides good visibility into network traffic usage after the summarizer runs, but there are definitely some instances where you need to be able to drill into the detailed records, especially when there are possible virus outbreaks.

Here’s how you do just that with ViewPoint 5 or later, running with a MySQL database.

Open up the command prompt
Run “mysql -uroot -p”
Enter your viewpoint administration password at the Enter password: prompt
Type “show databases”, press Enter

You should see an output similar to:
mysql> show databases;
+———————-+
| Database |
+———————-+
| information_schema |
| mysql |
| rawsyslogdb_20090427 |
| rawsyslogdb_20100220 |
| rawsyslogdb_20100221 |
| rawsyslogdb_20100222 |
| rawsyslogdb_20100223 |
| rawsyslogdb_20100224 |
| rawsyslogdb_20100225 |
| rawsyslogdb_20100226 |
| rawsyslogdb_20100227 |
| rawsyslogdb_20100228 |
| rawsyslogdb_20100301 |
| rawsyslogdb_20100302 |
| rawsyslogdb_20100303 |
| rawsyslogdb_20100304 |
| rawsyslogdb_20100305 |
| sgmsdb |
| sgmsdb_archive |
| test |
+———————-+
20 rows in set (0.00 sec)

 

There you see databases containing the raw syslog data for the last two weeks.

-Type “use “, ex: “use rawsyslogdb_20100305” for the 3/5/2010 database
-Type “show tables;”
You should see an output similar to:
+——————————–+
| Tables_in_rawsyslogdb_20100305 |
+——————————–+
| sw_0017c5xxxxxx |
+——————————–+
1 row in set (0.00 sec)

There you’ll see your SonicWall MAC Address/Serial Number

-Type “desc

“, ex: “desc sw_0017c5xxxxxx”

Here you can see the layout of the raw syslog data table. I used a screenshot to preserve formatting for easy readability.

Some example queries:

-Traffic from a certain source
SELECT TIME, SRCIP, DSTIP, DSTPORT, SENT, RCVD FROM sw_0017c5xxxxxx WHERE SRCIP = ‘1.2.3.4’;

-Traffic to a certain destination
SELECT TIME, SRCIP, DSTIP, DSTPORT, SENT, RCVD FROM sw_0017c5xxxxxx WHERE DSTIP = ‘1.2.3.4’;

-Top 10 users of inbound traffic, by source IP, originating from the LAN interface (X0)
SELECT SRCIP, SUM(RCVD) FROM sw_0017c5xxxxxx WHERE SRCINT = ‘X0’ GROUP BY SRCIP ORDER BY SUM(RCVD) DESC LIMIT 10;

There are a ton of ways to dive into the data, the nice part of Viewpoint is they put the data into a useful database for you. You can latch on with GUI tools such as Microsoft Access or SQLYog. You could even tie in with a data visualization tool such as Tableau.