av.mcafee

Events generated by McAfee EPO Console are stored in its SQL database in a table named EPOEventsMT or EPOEvents. We can use Logstash to extract the events from the database using JDBC, apply the av.mcafee.epo.events tag to each event, then send them in syslog format to port 13000 on the Devo Relay. 

Because Logstash saves the ID of the last record read in a dedicated file, each time the query is run only new records are retrieved and sent to Devo.

Prerequisites

• Logstash requires a database user with read permissions on the EPO tables.

• Some database fields need to be converted from signed int to a varchar IP address. The following function can do this for you.

  CREATE FUNCTION [dbo].[IntegerToIPAddress] (@ipin int)
  RETURNS CHAR(15)
  AS
  BEGIN
  declare @o1 bigint, @o2 bigint, @o3 bigint, @o4 bigint;
  declare @ip bigint;
  -- This is the magic epo conversion size...
  set @ip = (CAST(@ipin as bigint) + 2147483647) + 1;
  SET @o1 = @ip / 16777216;
  SET @ip = @ip % 16777216;
  SET @o2 = @ip / 65536;
  SET @ip = @ip % 65536;
  SET @o3 = @ip / 256;
  SET @ip = @ip % 256;
  SET @o4 = @ip;
  RETURN
  CONVERT(VARCHAR(4), @o1) + '.' +
  CONVERT(VARCHAR(4), @o2) + '.' +
  CONVERT(VARCHAR(4), @o3) + '.' +
  CONVERT(VARCHAR(4), @o4)
  END

• Install Logstash on the same machine as the Devo Relay and download the logstash-output-syslog plugin.

• Download and decompress the Microsoft JDBC Driver 4.1 for SQL Server into the /var/lib/logstash directory on the Devo Relay machine.

Configure Logstash

Create the configuration file that will direct the extraction and sending of events in /etc/logstash/conf.d. For example, /etc/logstash/conf.d/epo-DevoRelay.conf.

The following configuration file is a model which will extract new events from the database using the JDBC driver every one minute and send them using the syslog plugin to port 1300 on the Devo Relay. The parameters enclosed in angled brackets (<>) should be modified for your environment.

input {
	jdbc {
        jdbc_driver_library => "/var/lib/logstash/sqljdbc_4.1/enu/sqljdbc41.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_connection_string => "jdbc:sqlserver://<ip_epo_server>\EPOSERVER:<port>;databaseName=<db_instance_name>"
        jdbc_user => "<username>"
        jdbc_password => "<password>"
        schedule => "* * * * *"   #each 1 minute
        use_column_value => true
        tracking_column => "autoid"
        last_run_metadata_path => "/var/lib/logstash/.logstash_jdbc_last_run"
        statement => "select autoid,AutoGUID,ServerID,CONVERT(varchar(24),DetectedUTC,120) as detectedUTC,dbo.IntegerToIPAddress(SourceIPV4) as SourceIP,dbo.IntegerToIPAddress(TargetIPV4) as TargetIP,TargetUserName,TargetFileName,SourceHostName,TargetHostName,ThreatCategory,ThreatEventID,ThreatSeverity,ThreatName,ThreatActionTaken,ThreatHandled from dbo.EPOEventsMT where autoid > :sql_last_value"
    }
}
# filter {
#
# }

output {
	syslog {
        facility => "local7"
        severity => "informational"
    	host => "localhost"
    	port => 13000 
        sourcehost => "<epo_server_name>"
    	appname => "av.mcafee.epo.events"
        protocol => "tcp"
        codec => line {
            format => "mcafeeEPO,%{autoid},%{autoguid},%{serverid},%{detectedutc},%{sourceip},%{targetip},%{targetusername},%{targetfilename},%{sourcehostname},%{targethostname},%{threatcategory},%{threateventid},%{threatseverity},%{threatname},%{threatactiontaken},%{threathandled}"
        }
	}
}

• Add the Logstash service to be started at bootup using this command:

  update-rc.d - f logstash defaults 50

• Finally, start the Logstash service:

  /etc/init.d/logstash start
  

Now you can confirm that the events are being correctly extracted and sent to Devo.

• Log into the Devo web application and domain to which you are sending the EPO events.
• Open the Finder in the Data Search area and locate the av.mcafee.epo.events table.

Related articles

• Logstash