Solved

Rsyslog & loganalyzer DB size

Posted on 2014-10-14
12
288 Views
Last Modified: 2014-10-17
This is eating up the disk space at 4 gigs per day. The system runs very slow due to being old hardware so I would like to trim this down. The server is receiving syslog information from one firewall and I have 27 firewalls that I want to setup for this so just imagine how much disk space this would eat up.  Is there a setting somewhere that I can change to not log so much, or truncate the database every 8-10 hours?
0
Comment
Question by:stlhost
  • 6
  • 4
  • 2
12 Comments
 
LVL 13

Expert Comment

by:Sandy
ID: 40379877
why don't you put compress parameter in logrotate.d/<log_conf> file ?

or you can use gzip on the files as-well.

TY/SA
0
 
LVL 2

Author Comment

by:stlhost
ID: 40380577
The data goes into an SQL database.
0
 
LVL 13

Expert Comment

by:Sandy
ID: 40380724
OK.. Which firewall it is and what is the log debug level ?

Please share log configuration of firewall

TY/SA
0
 
LVL 2

Author Comment

by:stlhost
ID: 40380842
Sonicwall 3500. Syslog is set to local 0, syslog format is Webtrends.
0
 
LVL 61

Expert Comment

by:gheist
ID: 40380993
4G/day is not much...
0
 
LVL 2

Author Comment

by:stlhost
ID: 40382004
Not really I guess when you are sending all info over I suppose but was wondering if there was a way to lighten the load or trim the database on daily basis.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 61

Expert Comment

by:gheist
ID: 40382542
Would be nice to know what type pf "SQL database" data is using, so I can suggest optimal either trigger or schedule script.
Do you record now() when log records arrive?
0
 
LVL 2

Author Comment

by:stlhost
ID: 40384276
That I have no idea. I just used what came with the program and created a database based on http://nolabnoparty.com/en/install-rsyslog-loganalyzer/

CREATE DATABASE Syslog;
USE Syslog;
CREATE TABLE SystemEvents
(
        ID int unsigned not null auto_increment primary key,
        CustomerID bigint,
        ReceivedAt datetime NULL,
        DeviceReportedTime datetime NULL,
        Facility smallint NULL,
        Priority smallint NULL,
        FromHost varchar(60) NULL,
        Message text,
        NTSeverity int NULL,
        Importance int NULL,
        EventSource varchar(60),
        EventUser varchar(60) NULL,
        EventCategory int NULL,
        EventID int NULL,
        EventBinaryData text NULL,
        MaxAvailable int NULL,
        CurrUsage int NULL,
        MinUsage int NULL,
        MaxUsage int NULL,
        InfoUnitID int NULL ,
        SysLogTag varchar(60),
        EventLogType varchar(60),
        GenericFileName VarChar(60),
        SystemID int NULL
);

CREATE TABLE SystemEventsProperties
(
        ID int unsigned not null auto_increment primary key,
        SystemEventID int NULL ,
        ParamName varchar(255) NULL ,
        ParamValue text NULL
);

Open in new window


Thanks
0
 
LVL 61

Accepted Solution

by:
gheist earned 500 total points
ID: 40385338
It is mysql :)
select count(*) from syslog where ReceivedAt > (curdate() - 3 months)

if it seems what you want to delete just replace select count with delete
0
 
LVL 2

Author Comment

by:stlhost
ID: 40385519
mysql> select count(*) from SystemEvents where ReceivedAt > (curdate() - 90);
+----------+
| count(*) |
+----------+
| 43151859 |
+----------+
1 row in set (2 min 9.10 sec)
0
 
LVL 61

Expert Comment

by:gheist
ID: 40386025
I was meaning to delete where ReceivedAt is OLDER than those 3 months...
0
 
LVL 2

Author Comment

by:stlhost
ID: 40386557
oh lol. Thanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Linux Mint 18 31 77
Kali Linux store / persist wireless password 3 48
Linux/Apache File Ownership/Permissions 1 53
is my large folder zipped corrupted 4 50
BIND is the most widely used Name Server. A Name Server is the one that translates a site name to it's IP address. There is a new bug in BIND (https://kb.isc.org/article/AA-01272), affecting all versions of BIND 9 from BIND 9.1.0 (inclusive) thro…
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now