Hello All,
We having an issue on the ePO version 5.10, Tables are changed. Whenever we are trying to execute the given query
it is throwing an error java.sql.SQLException: Invalid object name 'EPOLeafNode'. So can you please provide us the
compatible query for ePO version(5.10)
Go through the below link to have the details of configuration related to DBConnect version 3.1.x
https://docs.splunk.com/Documentation/AddOns/released/McAfeeEPO/ConfigureDBConnectv3inputs
And also pay attention to the below points :
**
**
SELECT *
FROM
(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 [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].[OSCsdVersion] 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].[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]
FROM [EPOEvents]
LEFT JOIN "catalog_name"."schema_name"."EPOLeafNode"
ON [EPOEvents].[AgentGUID] = [EPOLeafNode].[AgentGUID]
LEFT JOIN "catalog_name"."schema_name"."EPOProdPropsView_VIRUSCAN"
ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID]
LEFT JOIN "catalog_name"."schema_name"."EPOComputerProperties"
ON [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID]
LEFT JOIN "catalog_name"."schema_name"."EPOEventFilterDesc"
ON [EPOEvents].[ThreatEventID] = [EPOEventFilterDesc].[EventId]
AND (EPOEventFilterDesc.Language='0409')
WHERE [EPOEvents].[ReceivedUTC] > DATEADD(day, -1, GETUTCDATE() ) ) t
WHERE AutoID > ?
ORDER BY AutoID ASC
Hope this would solve your problem.
Go through the below link to have the details of configuration related to DBConnect version 3.1.x
https://docs.splunk.com/Documentation/AddOns/released/McAfeeEPO/ConfigureDBConnectv3inputs
And also pay attention to the below points :
**
**
SELECT *
FROM
(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 [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].[OSCsdVersion] 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].[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]
FROM [EPOEvents]
LEFT JOIN "catalog_name"."schema_name"."EPOLeafNode"
ON [EPOEvents].[AgentGUID] = [EPOLeafNode].[AgentGUID]
LEFT JOIN "catalog_name"."schema_name"."EPOProdPropsView_VIRUSCAN"
ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID]
LEFT JOIN "catalog_name"."schema_name"."EPOComputerProperties"
ON [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID]
LEFT JOIN "catalog_name"."schema_name"."EPOEventFilterDesc"
ON [EPOEvents].[ThreatEventID] = [EPOEventFilterDesc].[EventId]
AND (EPOEventFilterDesc.Language='0409')
WHERE [EPOEvents].[ReceivedUTC] > DATEADD(day, -1, GETUTCDATE() ) ) t
WHERE AutoID > ?
ORDER BY AutoID ASC
Hope this would solve your problem.
Which DBConnect version are you using?
it's 3.1.4