On Wed, Sep 25, 2019 at 1:52 PM Jerry Lowry <michaiah2...@gmail.com> wrote:
> 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.
>
I’ll check my dbd in the morning to see if it’s still running. I’m
wondering if you’re having some kind of timeout issue or something.
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.
>
Check your postfix logs for errors.
> 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
> <https://groups.google.com/d/msgid/ossec-list/CAKP%3DcB63F7ZAFZYmS%3DmC3tdNseDRs%3DAnyEcGbU2E8ooV7%2B_ZPA%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
--
---
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/CAMyQvMocL%2BCFJ%3D%3DhFOOuE-OWUR4awiCEoieLUAaOC%3DSUrYzy7w%40mail.gmail.com.
