SonicWall Syslog Data Extraction From Viewpoint

IT

SonicWall Syslog Data Extraction From Viewpoint

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”

SonicWall Viewpoint Syslog Table Definition

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.

More About the Author

Daniel Holm

Director of Enterprise Solutions
Quick Q&A: Why We Like Dell-EMC for Just About Everything IT In this edition of “Quick Q&A,” we talk about Dell-EMC with InterWorks Director of Enterprise Solutions Daniel Holm, IT ...
Testing the Beta NVIDIA RTX Audio Driver NVIDIA is always pushing the limit of what their video cards can do. However, they have now started to use the AI abilities they ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072