7 Replies Latest reply on Dec 15, 2016 2:44 PM by sssyyy

    SQL Error Log - SIEM Collector

    bluesatsuma16

      Hello community,

       

      Does anyone have experience of using the SIEM collector agent to harvest SQL Error Logs?  I have managed to get the collector working to collect Windows event logs from the remote machine, however I have not been able to collect SQL Error Logs.

       

      Current configuration of agent/data source is shown in the attached pics.

       

      Can anyone provide any tips or screenshots of a working config?  Or is there a specific permission required on the target machine?  I'm really at a loss with this any any help would be appreciated.

       

      Thanks in advance.

        • 1. Re: SQL Error Log - SIEM Collector
          mehmetemin

          Hi bluesatsuma16

          I think you should change the datasource's configuration to

           

          Data Source Vendor: Generic

          Data Source Model: McAfee Event Format

          Data Format: Default

          Data Retrieval: McAfee Event Format (Default)

           

          After then please write and rollout and stop-start collector.

          BR

          • 2. Re: SQL Error Log - SIEM Collector
            bluesatsuma16

            Hi mehmetemin

             

            Thanks for coming back to me.  I am still unable to harvest the error log successfully using the config you provided.  I have set the SIEM collector log level to diagnostic; I have attached a screen shot.  I am also still seeing a yellow inactivity flag on the ESM dashboard.

             

            I should mention that I have an extract of a SQL error log on my local machine, there is no actual SQL server running on it - I'm trying to test the functionality.  Hopefully this won't stop the file from being harvested.

             

            Do you (or ANYONE) have any ideas as to what the issue could be?

             

            Thanks.

             

            Error Log Not Read.PNG

            • 3. Re: SQL Error Log - SIEM Collector
              mehmetemin

              Hi bluesatsuma16;

              Ok i didnot understand well sorry.

              You want to add a default fileread datasource not SQL.

              You should change your Datasource configuration to Generic , ASP , MEF then stop and start collector after writing and rolling out the datasource's configuration

              When you want to take a real SQL Error log please change the configuration again. Because it's not parsing i think.

              BR

              • 4. Re: SQL Error Log - SIEM Collector
                sssyyy

                It says no data to process. Can you check to make sure there is definitely new logs being generated? I suggest you delete the bookmark file and restart the collector service, make sure the collector account has full access to the modify the bookmark file.

                • 5. Re: SQL Error Log - SIEM Collector
                  bluesatsuma16

                  Hi sssyyy and mehmetemin,

                   

                  Thanks for your replies so far.  In order to troubleshoot more effectively, I have installed SQL server express on the machine that the SIEM collector agent installed on. 

                   

                  I have configured the collector and data sources as per the screenshots attached.  N.B. I have tried setting the 'Default Rule Assignment' to both MSSQL and MSSQL Error Log (ASP).Data source config.PNGSIEM collector config.PNG

                   

                  The account that the collector is using has write access to the bookmark folder (C:\Program Files\McAfee\Windows Event Collector\Plugins) and also has permission to read the SQL logs folder.

                   

                  I have tried deleting the bookmark and restarting the collector - it continues to say 'no data to process' - see attached screenshot.

                   

                  Debug log.PNG

                   

                  The SQL instance is a fresh install, so only has the logs created on install.  Would these still be harvested?

                   

                  I have not been able to remove the  inactivity flag on the ESM, so it looks like the collector is unable to talk to ESM correctly.

                   

                  I'm really at a loss now as to what to try next!  Any help would be greatly appreciated.  Many thanks.

                  • 6. Re: SQL Error Log - SIEM Collector
                    sssyyy

                    I guess there is couple of things you can try:

                    - make sure there is a connection between the siem collector and receiver, which appears you got that. active connection =1

                    - have a look at the actual error log itself, examine the format, as you may need put delimiters, or tail from beginning or end of file, etc.

                    - If you don't have multiple data sources from the same source IP, just use the IP address, not the host name. Or if there is a IP conflict, in the ESM GUI, just use Host ID.

                    - since you are doing SQL Error Log integration, why don't you use Microsoft > MSSQL Error Log (ASP) > Default > MEF. Hopefully McAfee ESM has pre-built parsers, but do use log unknown so you have a chance to create custom parsers.

                     

                    Coming back to your siem collector logs, I tend towards:

                    - error log contains no data

                    - needs future configuration in delimiter to read data

                    - mismatch for encryption, make sure both siem collector and receiver data source are enabled or disabled.

                     

                    Finally, what version of siem collector are you using?

                    • 7. Re: SQL Error Log - SIEM Collector
                      sssyyy

                      The collector will process data from the point to install and configure it, won't go back in time and process old data unless you reset the bookmark value.