Splunk DB Connect: How to collect data in EPO Database version 4.6.6 with Add-on for McAfee?

[dfigurello]

Hi Splunkers,

I need help with Add-on for McAfee, because I want collect anti-virus information from EPO database. (EPO Version 4.6.6) I am following the documentation in splunk site, but I am having problem to collect information in database. I Believe the "stanza" in dbconnect is not recognizing the tables in my epo database.

In my dbx.log:

2014-09-02 17:22:31.673 monsch1:ERROR:Scheduler - Error while reading stanza=[dbmon-tail://mcafee_epo_4_db/ta_mcafee_epo_4_input]: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://mcafee_epo_4_db/ta_mcafee_epo_4_input: Invalid object name 'EPOProdPropsView_ANTISPYWARE'. with query = SELECT CAST([EPOEvents].[ReceivedUTC] as varchar) as [timestamp], [EPOEvents].[AutoID] as [event_id], [EPOEvents].[ThreatName] as [signature], [EPOEvents].[ThreatType] as [threat_type]
[.....]

/opt/splunk/etc/apps/Splunk_TA_mcafee/local/inputs.conf

[dbmon-tail://mcafee_epo_4_db/ta_mcafee_epo_4_input]
disabled = 0
host = ip_address
index = main
interval = * * * * *
output.format = kv
output.timestamp = 1
output.timestamp.column = timestamp
output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = MMM dd yyyy HH:mmaa
query = SELECT CAST([EPOEvents].[ReceivedUTC] as varchar) as [timestamp], [EPOEvents].[AutoID] as [event_id], [EPOEvents].[ThreatName] as [signature], [EPOEvents].[ThreatType] as [threat_type], [EPOEvents].[ThreatEventID] as [signature_id], [EPOEvents].[ThreatCategory] as [category], [EPOEvents].[ThreatSeverity] as [severity_id], [EPOEventFilterDesc].[Name] as [event_description], [EPOEvents].[DetectedUTC] as [detected_timestamp], [EPOEvents].[TargetFileName] as [file_name], [EPOEvents].[AnalyzerDetectionMethod] as [detection_method], [EPOEvents].[ThreatActionTaken] as [action], [EPOEvents].[ThreatHandled] as [threat_handled], [EPOEvents].[TargetUserName] as [logon_user], [EPOComputerProperties].[UserName] as [user], [EPOComputerProperties].[DomainName] as [dest_nt_domain], [EPOEvents].[TargetHostName] as [dest_dns], [EPOEvents].[TargetHostName] as [dest_nt_host], [EPOComputerProperties].[IPHostName] as [fqdn], [dest_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),4,1))) ), [EPOComputerProperties].[SubnetMask] as [dest_netmask], [EPOComputerProperties].[NetAddress] as [dest_mac], [EPOComputerProperties].[OSType] as [os], [EPOComputerProperties].[OSServicePackVer] as [sp], [EPOComputerProperties].[OSVersion] as [os_version], [EPOComputerProperties].[OSBuildNum] as [os_build], [EPOComputerProperties].[TimeZone] as [timezone], [EPOEvents].[SourceHostName] as [src_dns], [src_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),4,1))) ), [EPOEvents].[SourceMAC] as [src_mac], [EPOEvents].[SourceProcessName] as [process], [EPOEvents].[SourceURL] as [url], [EPOEvents].[SourceUserName] as [logon_user], [EPOComputerProperties].[IsPortable] as [is_laptop], [EPOEvents].[AnalyzerName] as [product], [EPOEvents].[AnalyzerVersion] as [product_version], [EPOEvents].[AnalyzerEngineVersion] as [engine_version], [EPOEvents].[AnalyzerEngineVersion] as [dat_version], [EPOProdPropsView_VIRUSCAN].[datver] as [vse_dat_version], [EPOProdPropsView_VIRUSCAN].[enginever64] as [vse_engine64_version], [EPOProdPropsView_VIRUSCAN].[enginever] as [vse_engine_version], [EPOProdPropsView_VIRUSCAN].[hotfix] as [vse_hotfix], [EPOProdPropsView_VIRUSCAN].[productversion] as [vse_product_version], [EPOProdPropsView_VIRUSCAN].[servicepack] as [vse_sp], [EPOProdPropsView_ANTISPYWARE].[productversion] as [antispyware_version] FROM [EPOEvents] left join [EPOLeafNode] on [EPOEvents].[AgentGUID] = [EPOLeafNode].[AgentGUID] left join [EPOProdPropsView_ANTISPYWARE] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_ANTISPYWARE].[LeafNodeID] left join [EPOProdPropsView_VIRUSCAN] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID] left join [EPOComputerProperties] on [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID] left join [EPOEventFilterDesc] on [EPOEvents].[ThreatEventID] = [EPOEventFilterDesc].[EventId] and (EPOEventFilterDesc.Language='0409') WHERE [EPOEvents].[AutoID] > 0 {{ AND [EPOEvents].$rising_column$ > ? }} ORDER BY [EPOEvents].[AutoID]
sourcetype = mcafee:epo
tail.rising.column = AutoID

/opt/splunk/etc/apps/dbx/local/database.conf
[mcafee_epo_4_db]
database = ePO4_ADC1PEPO01
host = my_ip_address
username = company\svc_eposervice
password = shdisids
port = 1433
isolation_level = DATABASE_SETTING
readonly = 1
type = mssql
disabled = 0

Cheers.

[nkpiquette]

It appears that the Antispyware object is what is throwing off the 4.X input. To solve this I used the 5.x query and was able to get it to accept the input. Give this a shot and let us know if it worked please.

[dshpritz · Accepted solution]

Chances are you have version 5 of EPO (check with your EPO admin). Version 5 changed the DB schema, and as such the EPOProdPropsView_ANTISPYWARE object doesn't exist. There is another stanza included in the Add-on for McAfee which is designed for version 5.

[dfigurello]

Hi Splunkers,

First of all, thanks dshpritz and nkpiquette. I used another stanza included in the Add-on for McAfee to version 5.

That's great!

Cheers!