Extract logs from Oracle View

[Lucio Emanuel Soldo]

Hi everyone, how are you doing?

Well, this is my scenario:

1) App named "PRUEBA" that is using an Oracle Database.

2) I´ve created a VIEW into the Oracle database named "SECURITY_LOG" which has every security logs (por example, login. logout, reset password, and so on)

3) This view is executed every five minutes.

My question is: is it possible to read logs from SECURITY_LOG with Wazuh?, if it is possible , how do I do to manage frequency to read every five minutes the security logs from that VIEW to be able to avoid the whole reading of that view every five minutes?

Thank you very much!

[Juan Nicolás Asselle]

Hi Lucio!

Please correct me if i’m wrong: your app PRUEBA use your DB and you need to audit what’s doing with it by running periodically (five minutes) and querying (db trigger? external app/script?) the SECURITY_LOG view. If this statement is right, could you please tell me where the db query output is going to?
By other hand, there’s a interesting option check_diff that allow to trigger an alert if the content of it was different from the previous one. This is really useful if the whole query is treated as a whole and no line-by-line.

Thank you and I wait for this information to move forward.
Nico

​

[Lucio Emanuel Soldo]

Hi Nico, thank you very much for your response.

Well. I´m gonna try to be as clear as possible.

1) Yes, app PRUEBA save security logs at several internal Oracle tables. We created a view named SECURITY_LOG which take information for those tables. The SQL query used by SECURITY_LOG view at Oracle level is configured to run every five minutes, for that reason every five minutes the SECURITY_LOG view has new information if event happened. 

Suppose the first query take only one event (10.10hs) , then after five minutes the second query take two event (10.12hs and 10.14hs):

              Timestamp                Username      Name             Action

               02/03/2021 10.10    A                 PRUEBA_A     Login

               02/03/2021 10.12    B                 PRUEBA_B     Login

               02/03/2021 10.14    A                 PRUEBA_A     Logout

2) I have two possibilities:

    2.1) Query to SECURITY_LOG view direct from Wazuh enviroment: 

 

             2.1.1) Create an script named querywazuh.sh located at /usr/bin/script/ folder in the local server (SRV_PRUEBA) where the DB Oracle are                            mounted.

             2.1.2)  Over ossec.conf in the server SRV_PRUEBA agent I should define the following:

                        <localfile>

                               <log_format>full_command</log_format>

                               <command>/usr/bin/script/querywazuh.sh</command> 

                              <frequency>300</frequency>

                        </localfile>

 

                          Note: querywazuh.sh contains the SQL statement and the correct credential to access to SECURITY_LOG view

              In this scenario, how do I do to avoid a whole SECURITY_LOG view reading from Wazuh every five minutes according to defined with                              frequency parameter (300 seconds)? I mean, suppose the first query invoke by Wazuh through querywazuh.sh script read the first register (10.10hs) from SECURITY_LOG view. Then after five minutes the second query read not only 10.12hs and 10.14hs but also the 10.10hs again. Do you understand me? I wish the second time that querywazuh is executed it reads only the 10.12hs and 10.14hs register...

     2.2) I have the possibility that the output from SECURITY_LOG view is saved to plain file text at  /var/log/massages/PRUEBA_SecurityLog_ddmmaaaa every time SECURITY_LOG view is executed at Oracle level. Then I should define the following at ossec.conf in the SRV_PRUEBA server:

                       <localfile>

                               <location> /var/log/massages/ *</location>                              

                               <log_format>syslog</log_format>                                                  

                        </localfile>

But again, what about the reading secuence. Suppose tha the first query from SECURITY_LOG view create a PRUEBA_SecurityLog_02032021 containing the first 10.10hs register. After five minutes, the second output query from SECURITY_LOG view appends the following two register (10.12hs and 10.14hs), but how can I do to avoid the whole reading PRUEBA_SecurityLog_02032021 every time....

Sorry about the large of this post!

Thank you very much!!!

[Lucio Emanuel Soldo]

Nico,

Sorry, maybe following your explanation and my scenario, these issue could be resolved by doing the following:

1) Installing agent on SRV_PRUEBA.

2) Configuring ossec.conf as follow:

       <localfile>

             <location>\var\log\messages\PRUEBA_SecurityLog_%Y-%m-%d.txt</location>

        <log_format>syslog</log_format> </localfile>

Then, when I create a rule I have to use <check_diff /> parameter to be able to avoid the whole reading from PRUEBA_SecurityLog_%Y-%m-%d.txt

What do you think?

Obviously, I need to create specific decoders before create the rule.

Thank you very much!

[Juan Nicolás Asselle]

Lucio,
First of all, sorry for the delay in the response.

I thinks that, despite there’s multiple solutions to solve this, one of them is the best in terms of amount of disk space needed and processing time for Wazuh agent, but this option relies on customizing your SECURITY_LOG view in order to query/filter only the results for the last 5 minutes. This way we can append every output to a single log and avoid creating one file every 5 minutes and sending duplicate information to the manager (despite this could be ignored in the manager).

Both trigger mechanism are fine, but IMHO i prefer your proposal 2.2 but appending to the same file i.e \var\log\messages\PRUEBA_SecurityLog.txt. Then a simple localfile as you mention will do the job

<localfile>
  <location>\var\log\messages\PRUEBA_SecurityLog.txt</location>
  <log_format>syslog</log_format>
 </localfile>

I hope this works for you. Please let me know if I can help you with anything else.
Regards,
Nico

​

[Lucio Emanuel Soldo]

Nico, thank you very much for your response and time!

No problem about your delay on the contrary, thank you very much again!

Well, I understood you. You say I should use only one file, in this case \var\log\messages\PRUEBA_SecurityLog.txt and no rotate it. I suppose the Iview from Oracle DB should do an appending over  PRUEBA_SecurityLog.txt  file  every 5 minutes. In this case, how do the agent read information from the last point where it did the last reading, do you understand me?

Following you explanation, suppose the first saving from view generates ten registers over PRUEBA_SecurityLog.txt, ok?, then the agent read ten registers and send de information to manager. Five minutes later, the view from Oracle DB generate new five registers and appends those to the file PRUEBA_SecurityLog.txt. At tha point the PRUEBA_SecurityLog.txt should have fifteen registers, ok?, well, what happend when the agent read the PRUEBA_SecurityLog.txt?, does it read from the first register or from register number eleven?

Thank you very much!!

[Juan Nicolás Asselle]

Lucio,

Agent’s wazuh-logcollector process, responsable of fetching logs over all localfile sections defined in ossec.conf, move the reading pointer to the end of the log file (for EventChannel depends on only-future-events option) during startup and start reading in realtime from that point.
Remember that is important that the results of the DB query are filtered to get only the new DB entries in the last 5 minues.This way we can avoid duplicates.

As an example, the next scenario will explain how this will work

• time=0
  • wazuh-agent start, open \var\log\messages\PRUEBA_SecurityLog.txt and set reading pointer to the end of it. No logs are sent to Wazuh Manager.
• time=5min
  • Oracle DB append 10 new events (of the last 5 minutes) to \var\log\messages\PRUEBA_SecurityLog.txt
  • wazuh-agent read in realtime the new 10 lines in \var\log\messages\PRUEBA_SecurityLog.txt and send 10 events to Wazuh Manager
• time=10min
  • Oracle DB append 5 new events (of the last 5 minutes) to \var\log\messages\PRUEBA_SecurityLog.txt
  • wazuh-agent read in realtime the new 5 lines in \var\log\messages\PRUEBA_SecurityLog.txt and send 5 events to Wazuh Manager

BTW, there’s a already developed feature (coming in a future version) that will allow to use only-future-events for any location. This is really helpful in case the agent stops/restarts for some reason.

Please don’t hesitate to contact me if you have further questions.

Regards,

Nico

​

[Lucio Emanuel Soldo]

Nico,  Thank you very much! 

It is perfect!, I mean, the logcollector has the ability to read from the last reading point!! Great! I didn't know about it. I`m new in Wazuh, for that reason it is a good feature because it's not necessary for the agent reading whole content from  PRUEBA_SecurityLog.txt file every five minutes. 

Your explanation was perfect! and so clear for me.

 Thank you very much!!!