Well, being as I only have two agents installed to test initially and
neither one is contacting the server due to email issues, I only have 53
alerts in the table. Just be aware that the database connects and
functions fine for ~8 hours. It has failed at 4 in the morning the last
time I sent you email.
As to the email problem this is what I have in my config file. The node
'cascade (10.20.10.6)' is running a postfix smtp server. The ossec server
is in the same subnet.
The user ossec is a valid user on the smtp server.
<email_notification>yes</email_notification>
<email_to>jlo...@domain.com</email_to>
<smtp_server>cascade</smtp_server>
<email_from>os...@domain.com</email_from>
<email_maxperhour>20</email_maxperhour>
I have copied the host file into the /var/ossec directory so it should be
doing dns translation. I still get "Mail from not accepted by server"
errors, postfix is also configured to accept email from any of the subnets
defined.
jerry
On Wed, Sep 25, 2019 at 9:47 AM dan (ddp) <ddp...@gmail.com> wrote:
> On Tue, Sep 24, 2019 at 2:23 PM Jerry Lowry <michaiah2...@gmail.com>
> wrote:
> >
> > Dan,
> >
> > the only entries for today are as follows:
> > 2019-09-24T08:02:49.637423Z 35 [Note] Aborted connection 35 to db:
> 'ossec' user: 'ossecuser' host: 'obed' (Got timeout reading communication
> packets)
> > 2019-09-24T16:31:32.557059Z 36 [Note] Aborted connection 36 to db:
> 'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication
> packets)
> > 2019-09-24T16:32:07.253522Z 38 [Note] Aborted connection 38 to db:
> 'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication
> packets)
> > these errors do not coincide with the error from the dbd process at
> 04:07:17 this morning. but then it looks like zulu time! PST is gmt+7.
> >
> > I have restarted all the ossec processes by hand and setup debugging on
> the dbd and mail processes. I also have a tail -f running on the ossec
> log. Nothing shows up as failing to connect for either the dbd or mail
> process. It just finished the syscheck and rootcheck in the last hour with
> no errors from either process.
> >
> > The mysql process statistics :
> > ps -o etime= -p 12275
> > 11-23:09:07
> > it has been up 11 days +. The only access error in the mysql log are
> when I was resetting the host name for the user in the database, forgot to
> change the permissions, it now has been granted everything.
> >
>
> Ok, I setup mariadb a couple of hours ago and started feeding OSSEC
> alerts into it. I have a bit over 7000 rows in the alert table.
> I haven't seen any issues so far, but my alert volume is pretty small.
> How many alerts are you seeing?
> I won't have the time to look into dbd for a bit, but I'm sure there
> are a lot of improvements that can be made.
>
> > jerry
> >
> >
> > On Tue, Sep 24, 2019 at 9:39 AM dan (ddp) <ddp...@gmail.com> wrote:
> >>
> >> On Tue, Sep 24, 2019 at 12:29 PM Jerry Lowry <michaiah2...@gmail.com>
> wrote:
> >> >
> >> > Dan,
> >> > So I configured the database to use the host name for the ossec user.
> Restarted everything with ossec and it was able to log in initially. It ran
> most of the night and then at 4 am this morning it failed with the same
> error saying:
> >> >
> >> > 2019/09/24 04:07:17 ossec-dbd(5203): ERROR: Error executing query
> 'INSERT INTO
> alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,alertid,user,full_log,tld)
> VALUES ('1', '1002','2','1569323237', '1', '(null)', '0', '(null)', '0',
> '1569323234.0', '(null)', 'Sep 24 04:07:14 obed audispd: node=obed.edt.com
> type=ANOM_RBAC_INTEGRITY_FAIL msg=audit(1569323234.455:87010): pid=28134
> uid=0 auid=0 ses=2001 msg=`added=43772 removed=17 changed=2021
> exe="/usr/sbin/aide" hostname=? addr=? terminal=? res=failed`','')'. Error:
> 'MySQL server has gone away'.
> >> > 2019/09/24 04:07:17 ossec-dbd(5209): INFO: Closing connection to
> database.
> >> > 2019/09/24 04:07:17 ossec-dbd(5210): INFO: Attempting to reconnect to
> database.
> >> > 2019/09/24 04:07:17 ossec-dbd: Connected to database 'ossec' at
> 'obed'.
> >> > 2019/09/24 04:07:17 ossec-dbd(5204): ERROR: Database error. Unable to
> run query.
> >> >
> >> > A list of the mysql daemon process shows that it has been up and
> running since Sep 12.
> >> > UID PID PPID C SZ RSS PSR STIME TTY TIME CMD
> >> > mysql 12275 1 0 378222 204688 3 Sep12 ? 00:12:57
> /usr/sbin/mysqld
> >> >
> >> > So mysql has not gone away. I suspect the ossec-dbd process is
> failing. Is there a way to debug this to a log file? By the way I am
> running version 3.3.0 on centos 7.6.1810
> >> >
> >>
> >> Are there any corresponding messages in your mysql log files?
> >>
> >> > I need this to work soon! How many other users are having this
> problem with mysql?
> >> > Is this version 3.3.0 finished with testing or should I drop back a
> version?
> >> >
> >>
> >> 3.3.0 is finished. 3.4.0 is supposed to be out soon-ish, but I don't
> >> think anything changed in the dbd stuff.
> >>
> >> > thanks,
> >> > jerry
> >> >
> >> > On Fri, Sep 20, 2019 at 4:42 AM dan (ddp) <ddp...@gmail.com> wrote:
> >> >>
> >> >> On Thu, Sep 19, 2019 at 3:24 PM Jerry Lowry <michaiah2...@gmail.com>
> wrote:
> >> >> >
> >> >> > Dan,
> >> >> > Just check the server log again and found this error from the dbd
> process:
> >> >> > 2019/09/19 04:07:04 ossec-dbd(5203): ERROR: Error executing query
> 'INSERT INTO
> alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,alertid,user,full_log,tld)
> VALUES ('1', '1002','2','1568891224', '1', '(null)', '0', '(null)', '0',
> '1568891220.0', '(null)', 'Sep 19 04:06:59 obed audispd: node=obed.edt.com
> type=ANOM_RBAC_INTEGRITY_FAIL msg=audit(1568891219.881:80020): pid=6481
> uid=0 auid=0 ses=1145 msg=`added=39777 removed=272 changed=2021
> exe="/usr/sbin/aide" hostname=? addr=? terminal=? res=failed`','')'. Error:
> 'MySQL server has gone away'.
> >> >> > 2019/09/19 04:07:04 ossec-dbd(5209): INFO: Closing connection to
> database.
> >> >> > 2019/09/19 04:07:04 ossec-dbd(5210): INFO: Attempting to reconnect
> to database.
> >> >> > 2019/09/19 04:07:04 ossec-dbd(5202): ERROR: Error connecting to
> database 'localhost'(ossec): ERROR: Unknown MySQL server host 'localhost'
> (-11).
> >> >>
> >> >> I wouldn't trust localhost for this. I think ossec-dbd chroot()s to
> >> >> /var/ossec, but can't remember for sure.
> >> >> Did you copy /etc/hosts to /var/ossec/etc/hosts? That might be enough
> >> >> to make sure dns resolution works.
> >> >>
> >> >>
> >> >> > 2019/09/19 04:07:06 ossec-dbd(5210): INFO: Attempting to reconnect
> to database.
> >> >> > 2019/09/19 04:07:06 ossec-dbd(5202): ERROR: Error connecting to
> database 'localhost'(ossec): ERROR: Unknown MySQL server host 'localhost'
> (-11).
> >> >> > 2019/09/19 04:07:10 ossec-dbd(5210): INFO: Attempting to reconnect
> to database.
> >> >> > 2019/09/19 04:07:10 ossec-dbd(5202): ERROR: Error connecting to
> database 'localhost'(ossec): ERROR: Unknown MySQL server host 'localhost'
> (-11).
> >> >> > 2019/09/19 04:07:11 INFO: Connected to 10.20.10.6 at address
> 10.20.10.6, port 25
> >> >> > 2019/09/19 04:07:12 os_sendmail(1764): WARN: Mail from not
> accepted by server
> >> >>
> >> >> So the default mail from email address (os...@example.com I think)
> >> >> isn't allowed by your smtp server.
> >> >> You can change this value with the <email_from> option in the
> <global>
> >> >> section of the server's ossec.conf.
> >> >>
> >> >> > 2019/09/19 04:07:12 ossec-maild(1223): ERROR: Error Sending email
> to 10.20.10.6 (smtp server)
> >> >> > 2019/09/19 04:07:18 ossec-dbd(5210): INFO: Attempting to reconnect
> to database.
> >> >> > 2019/09/19 04:07:18 ossec-dbd(5202): ERROR: Error connecting to
> database 'localhost'(ossec): ERROR: Unknown MySQL server host 'localhost'
> (-11).
> >> >> > 2019/09/19 04:07:34 ossec-dbd(5210): INFO: Attempting to reconnect
> to database.
> >> >> > 2019/09/19 04:07:34 ossec-dbd(5202): ERROR: Error connecting to
> database 'localhost'(ossec): ERROR: Unknown MySQL server host 'localhost'
> (-11).
> >> >> > 2019/09/19 04:08:06 ossec-dbd(5210): INFO: Attempting to reconnect
> to database.
> >> >> > 2019/09/19 04:08:06 ossec-dbd(5202): ERROR: Error connecting to
> database 'localhost'(ossec): ERROR: Unknown MySQL server host 'localhost'
> (-11).
> >> >> > 2019/09/19 04:09:10 ossec-dbd(5210): INFO: Attempting to reconnect
> to database.
> >> >> > 2019/09/19 04:09:10 ossec-dbd(5202): ERROR: Error connecting to
> database 'localhost'(ossec): ERROR: Unknown MySQL server host 'localhost'
> (-11).
> >> >> > 2019/09/19 04:11:18 ossec-dbd(5210): INFO: Attempting to reconnect
> to database.
> >> >> > 2019/09/19 04:11:18 ossec-dbd(5202): ERROR: Error connecting to
> database 'localhost'(ossec): ERROR: Unknown MySQL server host 'localhost'
> (-11).
> >> >> > 2019/09/19 04:15:34 ossec-dbd(5210): INFO: Attempting to reconnect
> to database.
> >> >> > 2019/09/19 04:15:34 ossec-dbd(5202): ERROR: Error connecting to
> database 'localhost'(ossec): ERROR: Unknown MySQL server host 'localhost'
> (-11).
> >> >> > 2019/09/19 04:24:06 ossec-dbd(5210): INFO: Attempting to reconnect
> to database.
> >> >> > 2019/09/19 04:24:06 ossec-dbd(5202): ERROR: Error connecting to
> database 'localhost'(ossec): ERROR: Unknown MySQL server host 'localhost'
> (-11).
> >> >> > 2019/09/19 04:41:10 ossec-dbd(5210): INFO: Attempting to reconnect
> to database.
> >> >> > 2019/09/19 04:41:10 ossec-dbd(5202): ERROR: Error connecting to
> database 'localhost'(ossec): ERROR: Unknown MySQL server host 'localhost'
> (-11).
> >> >> > 2019/09/19 05:02:14 INFO: Connected to 10.20.10.6 at address
> 10.20.10.6, port 25
> >> >> > 2019/09/19 05:02:15 os_sendmail(1764): WARN: Mail from not
> accepted by server
> >> >> > 2019/09/19 05:02:15 ossec-maild(1223): ERROR: Error Sending email
> to 10.20.10.6 (smtp server)
> >> >> > 2019/09/19 05:15:18 ossec-dbd(5208): ERROR: Multiple database
> errors. Exiting.
> >> >> > 2019/09/19 11:43:07 INFO: Connected to 10.20.10.6 at address
> 10.20.10.6, port 25
> >> >> > 2019/09/19 11:43:07 os_sendmail(1764): WARN: Mail from not
> accepted by server
> >> >> > 2019/09/19 11:43:07 ossec-maild(1223): ERROR: Error Sending email
> to 10.20.10.6 (smtp server)
> >> >> >
> >> >> > So I just listed all the log from the database error on. There
> are no errors above this that point to the database going away. The log is
> clean other than the email error ( which is baffling). Not sure what it
> missing on this.
> >> >> > Do you have any ideas as to why the database server would just go
> away? Looking in the database (which is still up) just the ossec-dbd
> process went away. I found that the last data that was inserted was in the
> alert table at 19:20 last night.
> >> >> >
> >> >> > jerry
> >> >> >
> >> >> >
> >> >> > On Thursday, September 19, 2019 at 12:12:00 PM UTC-7, Jerry Lowry
> wrote:
> >> >> >>
> >> >> >> No, actually it is not in the mysql schema that is downloaded in
> the tar. I inserted it based on what you showed me in the postgres schema.
> >> >> >> The did the trick. Apparently it works just fine until you add
> agents.
> >> >> >>
> >> >> >>
> >> >> >> On Wednesday, September 18, 2019 at 12:32:22 PM UTC-7, dan
> (ddpbsd) wrote:
> >> >> >>>
> >> >> >>>
> >> >> >>>
> >> >> >>> On Wed, Sep 18, 2019 at 3:16 PM Jerry Lowry <michai...@gmail.com>
> wrote:
> >> >> >>>>
> >> >> >>>> thanks Dan,
> >> >> >>>> So if the master branch hasn't changed in 3 years why are there
> no questions in this list regarding this problem. No one else uses Mysql?
> Why hasn't someone updated the schema for mysql? Many questions like this
> are concerning to me when something this important is being offered for
> production use!
> >> >> >>>
> >> >> >>>
> >> >> >>> I didn’t see mysql mentioned, so I guessed. I think it’s in the
> mysql schema as well.
> >> >> >>> I don’t know how much the database daemon is actually used. It’s
> not really actively developed (probably due to lack of time).
> >> >> >>> Here’s the item in the mysql schema:
> >> >> >>>
> https://github.com/ossec/ossec-hids/blob/master/src/os_dbd/mysql.schema#L71
> >> >> >>>
> >> >> >>>
> >> >> >>>>
> >> >> >>>> jerry
> >> >> >>>>
> >> >> >>>> p.s. by the way sorry for posting three times google said that
> it failed all three times, so I gave up using the list!
> >> >> >>>>
> >> >> >>>> On Wed, Sep 18, 2019 at 4:36 AM dan (ddp) <ddp...@gmail.com>
> wrote:
> >> >> >>>>>
> >> >> >>>>> On Wed, Sep 18, 2019 at 4:21 AM Jerry Lowry <
> michai...@gmail.com> wrote:
> >> >> >>>>> >
> >> >> >>>>> > So last week I started configuring ossec. Finally got it so
> that the database would connect. No problems. Added a couple agents today
> and found that they could not send email to the smtp server that I setup (
> another issue). So I tweak the config and restart the server.
> >> >> >>>>> >
> >> >> >>>>> > Now the server fails to execute a query on the alert table.
> Seems the 'level' column is not found. Loaded the schema from the source
> code. It is not there either. hmmm
> >> >> >>>>> >
> >> >> >>>>> > anyone know where 'level' comes from?
> >> >> >>>>> >
> >> >> >>>>>
> >> >> >>>>> If you're getting the same error as in your other mail, I
> think it's
> >> >> >>>>> in the schema.
> >> >> >>>>> Error message:
> >> >> >>>>> 2019/09/17 15:34:48 ossec-dbd(5203): ERROR: Error executing
> query
> >> >> >>>>> 'INSERT INTO
> alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,alertid,user,full_log,tld)
> >> >> >>>>> VALUES (VALUES_REMOVED')'. Error: 'Unknown column 'level' in
> 'field
> >> >> >>>>> list''.
> >> >> >>>>>
> >> >> >>>>> From the postgresql.schema (master branch from today, although
> the
> >> >> >>>>> file hasn't changed in over 3 years):
> >> >> >>>>> CREATE TABLE alert
> >> >> >>>>> (
> >> >> >>>>> id bigserial NOT NULL,
> >> >> >>>>> server_id INT4 NOT NULL,
> >> >> >>>>> rule_id INT8 NOT NULL,
> >> >> >>>>> level INT2,
> >> >> >>>>> timestamp INT8 NOT NULL,
> >> >> >>>>> location_id INT4 NOT NULL,
> >> >> >>>>> src_ip VARCHAR(46),
> >> >> >>>>> dst_ip VARCHAR(46),
> >> >> >>>>> src_port INT4,
> >> >> >>>>> dst_port INT4,
> >> >> >>>>> alertid TEXT DEFAULT NULL,
> >> >> >>>>> "user" TEXT,
> >> >> >>>>> full_log TEXT NOT NULL,
> >> >> >>>>> is_hidden INT2 NOT NULL DEFAULT '0',
> >> >> >>>>> tld VARCHAR(32) NOT NULL DEFAULT '',
> >> >> >>>>> PRIMARY KEY (id, server_id)
> >> >> >>>>> );
> >> >> >>>>>
> >> >> >>>>> It's the 4th item down in the alert table.
> >> >> >>>>>
> >> >> >>>>> >
> >> >> >>>>> > jerry
> >> >> >>>>> >
> >> >> >>>>> > --
> >> >> >>>>> >
> >> >> >>>>> > ---
> >> >> >>>>> > You received this message because you are subscribed to the
> Google Groups "ossec-list" group.
> >> >> >>>>> > To unsubscribe from this group and stop receiving emails
> from it, send an email to ossec...@googlegroups.com.
> >> >> >>>>> > To view this discussion on the web visit
> https://groups.google.com/d/msgid/ossec-list/a14f9a16-f078-4c87-9103-3f972cc7be79%40googlegroups.com
> .
> >> >> >>>>>
> >> >> >>>>> --
> >> >> >>>>>
> >> >> >>>>> ---
> >> >> >>>>> You received this message because you are subscribed to the
> Google Groups "ossec-list" group.
> >> >> >>>>> To unsubscribe from this group and stop receiving emails from
> it, send an email to ossec...@googlegroups.com.
> >> >> >>>>>
> >> >> >>>>> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ossec-list/CAMyQvMppTWL0MVTH5RkonRcFyGYgNNXccv_ambBLkS5pGO8eTQ%40mail.gmail.com
> .
> >> >> >>>>
> >> >> >>>> --
> >> >> >>>>
> >> >> >>>> ---
> >> >> >>>> You received this message because you are subscribed to the
> Google Groups "ossec-list" group.
> >> >> >>>> To unsubscribe from this group and stop receiving emails from
> it, send an email to ossec...@googlegroups.com.
> >> >> >>>> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ossec-list/CAKP%3DcB6fbR-FELkfi0Kwza7Lyiz8F2GmMM3vXt%2BvVzgO9Ln23g%40mail.gmail.com
> .
> >> >> >
> >> >> > --
> >> >> >
> >> >> > ---
> >> >> > You received this message because you are subscribed to the Google
> Groups "ossec-list" group.
> >> >> > To unsubscribe from this group and stop receiving emails from it,
> send an email to ossec-list+unsubscr...@googlegroups.com.
> >> >> > To view this discussion on the web visit
> https://groups.google.com/d/msgid/ossec-list/300c08dc-73f7-4de6-af3e-a96d114bdf39%40googlegroups.com
> .
> >> >>
> >> >> --
> >> >>
> >> >> ---
> >> >> You received this message because you are subscribed to the Google
> Groups "ossec-list" group.
> >> >> To unsubscribe from this group and stop receiving emails from it,
> send an email to ossec-list+unsubscr...@googlegroups.com.
> >> >> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ossec-list/CAMyQvMpMW2jY9KEzdwD3uYO_%3D2A3LPG44L7GcHo5-O4mxtkEMg%40mail.gmail.com
> .
> >> >
> >> > --
> >> >
> >> > ---
> >> > You received this message because you are subscribed to the Google
> Groups "ossec-list" group.
> >> > To unsubscribe from this group and stop receiving emails from it,
> send an email to ossec-list+unsubscr...@googlegroups.com.
> >> > To view this discussion on the web visit
> https://groups.google.com/d/msgid/ossec-list/CAKP%3DcB79eKQzcRVYSEsG6bGBXr8%2Bvmr_LRfGbHjxYaWpnXUpdA%40mail.gmail.com
> .
> >>
> >> --
> >>
> >> ---
> >> You received this message because you are subscribed to the Google
> Groups "ossec-list" group.
> >> To unsubscribe from this group and stop receiving emails from it, send
> an email to ossec-list+unsubscr...@googlegroups.com.
> >> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ossec-list/CAMyQvMqh79eQJ86Cih-Htk_fA2hP2Xbin4Ldyy8yrAUkfuPq3Q%40mail.gmail.com
> .
> >
> > --
> >
> > ---
> > You received this message because you are subscribed to the Google
> Groups "ossec-list" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an email to ossec-list+unsubscr...@googlegroups.com.
> > To view this discussion on the web visit
> https://groups.google.com/d/msgid/ossec-list/CAKP%3DcB52HPKATRfMoPQGdGnuF8oOZwE6k0hxbTWib0ONcrhrGA%40mail.gmail.com
> .
>
> --
>
> ---
> You received this message because you are subscribed to the Google Groups
> "ossec-list" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to ossec-list+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ossec-list/CAMyQvMpGTQV2csN6V21gveZGCSM7iQA%2BN3bZa6wB4V2KAqpvGw%40mail.gmail.com
> .
>
--
---
You received this message because you are subscribed to the Google Groups
"ossec-list" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to ossec-list+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/ossec-list/CAKP%3DcB63F7ZAFZYmS%3DmC3tdNseDRs%3DAnyEcGbU2E8ooV7%2B_ZPA%40mail.gmail.com.
