On Thu, Dec 20, 2012 at 12:45 PM, Sergey Fursov <[email protected]> wrote:
> Sorry, my mistake..
> Here is example:
> 2012-12-17 15:01:38 MSK ERROR: column "is_merchant" of relation "users"
> does not exist at character 54
For some reason your date format isn't correct. OSSEC expects it to
be: [\d\d\d\d-\d\d-\d\d \S+ \w+] ([2012-12-17 15:01:38 MSK] - I
guess). Get those brackets in there and it will probably work.
Before brackets:
# /var/ossec/bin/ossec-logtest
2012/12/20 13:05:24 ossec-testrule: INFO: Reading local decoder file.
2012/12/20 13:05:24 ossec-testrule: INFO: Started (pid: 7383).
ossec-testrule: Type one log per line.
2012-12-17 15:01:38 MSK ERROR: column "is_merchant" of relation
"users" does not exist at character 54
**Phase 1: Completed pre-decoding.
full event: '2012-12-17 15:01:38 MSK ERROR: column
"is_merchant" of relation "users" does not exist at character 54'
hostname: 'arrakis'
program_name: '(null)'
log: '2012-12-17 15:01:38 MSK ERROR: column "is_merchant" of
relation "users" does not exist at character 54'
**Phase 2: Completed decoding.
decoder: 'windows-date-format'
**Phase 3: Completed filtering (rules).
Rule id: '2900'
Level: '0'
Description: 'Dpkg (Debian Package) log.'
After brackets:
[2012-12-17 15:01:38 MSK] ERROR: column "is_merchant" of relation
"users" does not exist at character 54
**Phase 1: Completed pre-decoding.
full event: '[2012-12-17 15:01:38 MSK] ERROR: column
"is_merchant" of relation "users" does not exist at character 54'
hostname: 'arrakis'
program_name: '(null)'
log: '[2012-12-17 15:01:38 MSK] ERROR: column "is_merchant" of
relation "users" does not exist at character 54'
**Phase 2: Completed decoding.
decoder: 'postgresql_log'
**Phase 3: Completed filtering (rules).
Rule id: '50500'
Level: '0'
Description: 'PostgreSQL messages grouped.'
Then you'd still have to write a rule to match the log message.
> 2012-12-17 15:01:38 MSK STATEMENT: INSERT INTO "users" ("id", "uuid",
> "email", "phone", "is_merchant", "is_enabl
> ed", "hash", "date_register") VALUES ('2',
> '4bdf6156-edce-411d-a903-424a2368e4ad', '[email protected]', NULL, TRU
> E, TRUE, '6ed4f73b84063fa673154844d3df92ec', '2012-12-17 15:01:38+0400')
> 2012-12-19 11:39:27 MSK FATAL: the database system is starting up
> 2012-12-19 16:06:30 MSK LOG: could not receive data from client: Connection
> reset by peer
>
>
> четверг, 20 декабря 2012 г., 18:36:57 UTC+4 пользователь dan (ddpbsd)
> написал:
>>
>> On Thu, Dec 20, 2012 at 9:29 AM, Sergey Fursov <[email protected]> wrote:
>> > Here is it. Tags <options>alert_by_email</options> are inserted by me
>> > because we need all database activity events.
>> >
>>
>> Unless you made significant changes to the postgresql_rules.xml file,
>> I don't need your copy. What I do need, and what I asked for, is log
>> samples. With those log samples I can find out why you aren't getting
>> alerts. Without those log samples I will not be able to track this
>> down for you.
>>
>> > <!-- PostgreSQL Log messages -->
>> > <group name="postgresql_log,">
>> > <rule id="50500" level="0">
>> > <options>alert_by_email</options>
>> > <decoded_as>postgresql_log</decoded_as>
>> > <description>PostgreSQL messages grouped.</description>
>> > </rule>
>> >
>> > <rule id="50501" level="0">
>> > <if_sid>50500</if_sid>
>> > <status>^LOG</status>
>> > <options>alert_by_email</options>
>> > <description>PostgreSQL log message.</description>
>> > </rule>
>> >
>> > <rule id="50502" level="0">
>> > <if_sid>50500</if_sid>
>> > <options>alert_by_email</options>
>> > <status>^NOTICE|INFO</status>
>> > <description>PostgreSQL informational message.</description>
>> > </rule>
>> >
>> > <rule id="50503" level="4">
>> > <if_sid>50500</if_sid>
>> > <options>alert_by_email</options>
>> > <status>^ERROR</status>
>> > <description>PostgreSQL error message.</description>
>> > </rule>
>> >
>> > <rule id="50504" level="5">
>> > <if_sid>50500</if_sid>
>> > <options>alert_by_email</options>
>> > <status>^FATAL</status>
>> > <description>PostgreSQL error message.</description>
>> > </rule>
>> >
>> > <rule id="50505" level="0">
>> > <if_sid>50500</if_sid>
>> > <options>alert_by_email</options>
>> > <status>^DEBUG</status>
>> > <description>PostgreSQL debug message.</description>
>> > </rule>
>> >
>> > <rule id="50510" level="0">
>> > <if_sid>50501</if_sid>
>> > <options>alert_by_email</options>
>> > <match> duration: | statement: </match>
>> > <description>Database query.</description>
>> > </rule>
>> >
>> > <rule id="50511" level="3">
>> > <if_sid>50501</if_sid>
>> > <options>alert_by_email</options>
>> > <match>connection authorized</match>
>> > <description>Database authentication success.</description>
>> > <group>authentication_success,</group>
>> > </rule>
>> >
>> > <rule id="50512" level="9">
>> > <if_sid>50504</if_sid>
>> > <options>alert_by_email</options>
>> > <match>authentication failed</match>
>> > <description>Database authentication failure.</description>
>> > <group>authentication_failed,</group>
>> > </rule>
>> >
>> > <rule id="50520" level="12">
>> > <if_sid>50504</if_sid>
>> > <options>alert_by_email</options>
>> > <match>terminating connection due</match>
>> > <description>Database shutdown messge.</description>
>> > <group>service_availability,</group>
>> > </rule>
>> >
>> > <rule id="50521" level="12">
>> > <if_sid>50501</if_sid>
>> > <options>alert_by_email</options>
>> > <match>aborting any active transactions|shutting down</match>
>> > <description>Database shutdown messge.</description>
>> > <group>service_availability,</group>
>> > </rule>
>> >
>> > <rule id="50580" level="10" frequency="6" timeframe="120" ignore="60">
>> > <if_matched_sid>50504</if_matched_sid>
>> > <description>Multiple database errors.</description>
>> > <group>service_availability,</group>
>> > </rule>
>> >
>> > <rule id="50581" level="10" frequency="6" timeframe="120" ignore="60">
>> > <if_matched_sid>50503</if_matched_sid>
>> > <description>Multiple database errors.</description>
>> > <group>service_availability,</group>
>> > </rule>
>> >
>> > </group> <!-- POSTGRESQL -->
>> >
>> > <!-- EOF -->
>> >
>> > четверг, 20 декабря 2012 г., 16:41:47 UTC+4 пользователь Sergey Fursov
>> > написал:
>> >>
>> >> Hello. I perform installation of OSSEC server (1) and OSSEC agent (2)
>> >> with
>> >> default configuration, and check that OSSEC agent is working fine (for
>> >> example, I get email alert when OSSEC restarts or somebody take logon
>> >> on
>> >> (2))
>> >> After this I install Postgresql on (2) and add his log to
>> >> /var/ossec/etc/ossec.conf on (2):
>> >>
>> >> <localfile>
>> >> <log_format>postgresql_log</log_format>
>> >> <location>/var/log/postgresql/postgresql-9.2-main.log</location>
>> >> </localfile>
>> >>
>> >> But I didn't see any errors from OSSEC server (1), while I take some
>> >> errors test (like MSK ERROR: column orders.is_canceled does not exist
>> >> at
>> >> char
>> >> acter 164
>> >> or
>> >> MSK FATAL: the database system is starting up
>> >> or
>> >> MSK FATAL: password authentication failed for user "redmine"
>> >>
>> >>
>> >> Could you help in correct installation of this monitoring? Thanks a
>> >> lot!
>> >>
>> >
