Splunk Add-on for McAfee: Why did the Technology Add-on DB Input stopped working mid Jan 2018?

[cameronjust]

Hi All,

I tried to "file a case" for this but the link from splunkbase for this TA is broken (http://www.splunk.com/index.php/submit_issue)

Just an update to let people know that it appears an update to McAfee has removed an SQL view (EPOProdPropsView_VIRUSCAN) that this TA uses. This caused the DB input to fail.

I searched around and found what I think to be the replacement view EPOProdPropsView_EPOAGENT.

Please find the full updated SQL below

SELECT [EPOEvents].[ReceivedUTC] as [timestamp],
        [EPOEvents].[AutoID],
        [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 [vendor_action], 
        CAST([EPOEvents].[ThreatHandled] as int) 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 [source_logon_user], 
        [EPOComputerProperties].[IsPortable] as [is_laptop], 
        [EPOEvents].[AnalyzerName] as [product], 
        [EPOEvents].[AnalyzerVersion] as [product_version], 
        [EPOEvents].[AnalyzerEngineVersion] as [engine_version], 
        [EPOEvents].[AnalyzerDATVersion] as [dat_version], 
        [vse_dat_version] = convert(varchar(5),[EPOProdPropsView_EPOAGENT].[verDAT32Major]) + '.' + convert(varchar(5),[EPOProdPropsView_EPOAGENT].[verDAT32Minor]), 
        [vse_engine64_version] = convert(varchar(5),[EPOProdPropsView_EPOAGENT].[verEngine64Major]) + '.' + convert(varchar(5),[EPOProdPropsView_EPOAGENT].[verEngine64Minor]), 
        [vse_engine_version] = convert(varchar(5),[EPOProdPropsView_EPOAGENT].[verEngine32Major]) + '.' + convert(varchar(5),[EPOProdPropsView_EPOAGENT].[verEngine32Minor]), 
        [EPOProdPropsView_EPOAGENT].[verHotfix] as [vse_hotfix], 
        [EPOProdPropsView_EPOAGENT].[ProductVersion] as [vse_product_version] 

FROM [EPOEvents]

LEFT JOIN [EPOLeafNode] ON [EPOEvents].[AgentGUID] = [EPOLeafNode].[AgentGUID] 
LEFT JOIN [EPOProdPropsView_EPOAGENT] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_EPOAGENT].[LeafNodeID] 
LEFT JOIN [EPOComputerProperties] ON [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID] 
LEFT JOIN [EPOEventFilterDesc] ON [EPOEvents].[ThreatEventID] = [EPOEventFilterDesc].[EventId] 
AND ([EPOEventFilterDesc].[Language]='0409') 
WHERE [EPOEvents].AutoID > ?
ORDER BY [EPOEvents].AutoID ASC

If Splunk could update this SQL template for users it will hopefully save others the time in debugging this issue.

I was unable to edit the existing SQL statements in DB Connect 3.x and ended up just making a whole new input from scratch. Remember to set the checkpoint (AutoID) to the last valid entry in your Splunk logs to ensure you get the backlog.

Hope that helps.