Rsyslog & loganalyzer DB size

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?
LVL 2
stlhostAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SandyCommented:
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
stlhostAuthor Commented:
The data goes into an SQL database.
0
SandyCommented:
OK.. Which firewall it is and what is the log debug level ?

Please share log configuration of firewall

TY/SA
0
Firewall Management 201 with Professor Wool

In this whiteboard video, Professor Wool highlights the challenges, benefits and trade-offs of utilizing zero-touch automation for security policy change management. Watch and Learn!

stlhostAuthor Commented:
Sonicwall 3500. Syslog is set to local 0, syslog format is Webtrends.
0
gheistCommented:
4G/day is not much...
0
stlhostAuthor Commented:
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
gheistCommented:
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
stlhostAuthor Commented:
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
gheistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stlhostAuthor Commented:
mysql> select count(*) from SystemEvents where ReceivedAt > (curdate() - 90);
+----------+
| count(*) |
+----------+
| 43151859 |
+----------+
1 row in set (2 min 9.10 sec)
0
gheistCommented:
I was meaning to delete where ReceivedAt is OLDER than those 3 months...
0
stlhostAuthor Commented:
oh lol. Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.