I am trying to setup an input in DBConnect app to ingest logs from McAfee ePO.
I have successfully created a connection to the DB. However, unable to get past the input settings.
As per the docs, that mention, If a database other than the default McAfee database is selected, the template must be updated with the actual McAfee database and schema name.
I have updated the template to change the default DB from EPOEvents
--> "ePO_ABCEPO_Events"."dbo"."EPOEvents"
On the "New Inputs", I have selected the connection, catalog, schema and chose the McAfee template. But still, the rising column is not generating any column.
However, when I just choose the Table, it automatically runs the query - SELECT * FROM "ePO_ABCEPO"."dbo"."EPOEvents"
and displays the table and Rising Column does display all the available columns.
Please advise how can I solve this issue. The docs arent so well documented for setting the inputs. for DBConnect 3.x.x
Answering my own question here. I was able to fix the issue by doing the following,
FROM
keyword in the sql query, replaced all table names from EPOEvents --> "ePO_ABCEPO_Events"."dbo"."EPOEvents"
AND [EPOEvents].[ReceivedUTC] > DATEADD(day, -1, GETDATE() )
at the end of the query as the query used to get timed out all the time.OSServicePackVer
as one of the table columns, however, it seems to have changed in new version of EPO server to OSCsdVersion
, so change that part in the query.
So the modified query looks like 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].[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].[AnalyzerDATVersion] 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 "ePO_ABCEPO"."dbo"."EPOEvents"
LEFT JOIN "ePO_ABCEPO"."dbo"."EPOLeafNode" ON [EPOEvents].[AgentGUID] = [EPOLeafNode].[AgentGUID]
LEFT JOIN "ePO_ABCEPO"."dbo"."EPOProdPropsView_VIRUSCAN" ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID]
LEFT JOIN "ePO_ABCEPO"."dbo"."EPOComputerProperties" ON [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID]
LEFT JOIN "ePO_ABCEPO"."dbo"."EPOEventFilterDesc" ON [EPOEvents].[ThreatEventID] = [EPOEventFilterDesc].[EventId]
AND ([EPOEventFilterDesc].[Language]='0409')
WHERE [EPOEvents].[AutoID] > ? AND [EPOEvents].[ReceivedUTC] > DATEADD(day, -1, GETDATE() )
ORDER BY [EPOEvents].[AutoID] ASCIf you choose the right, Connection, catalog, schema, select the mcafee add-on template and modify the generated sql query accordingly, the rising column should get populated automatically.
Answering my own question here. I was able to fix the issue by doing the following,
FROM
keyword in the sql query, replaced all table names from EPOEvents --> "ePO_ABCEPO_Events"."dbo"."EPOEvents"
AND [EPOEvents].[ReceivedUTC] > DATEADD(day, -1, GETDATE() )
at the end of the query as the query used to get timed out all the time.OSServicePackVer
as one of the table columns, however, it seems to have changed in new version of EPO server to OSCsdVersion
, so change that part in the query.
So the modified query looks like 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].[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].[AnalyzerDATVersion] 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 "ePO_ABCEPO"."dbo"."EPOEvents"
LEFT JOIN "ePO_ABCEPO"."dbo"."EPOLeafNode" ON [EPOEvents].[AgentGUID] = [EPOLeafNode].[AgentGUID]
LEFT JOIN "ePO_ABCEPO"."dbo"."EPOProdPropsView_VIRUSCAN" ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID]
LEFT JOIN "ePO_ABCEPO"."dbo"."EPOComputerProperties" ON [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID]
LEFT JOIN "ePO_ABCEPO"."dbo"."EPOEventFilterDesc" ON [EPOEvents].[ThreatEventID] = [EPOEventFilterDesc].[EventId]
AND ([EPOEventFilterDesc].[Language]='0409')
WHERE [EPOEvents].[AutoID] > ? AND [EPOEvents].[ReceivedUTC] > DATEADD(day, -1, GETDATE() )
ORDER BY [EPOEvents].[AutoID] ASCIf you choose the right, Connection, catalog, schema, select the mcafee add-on template and modify the generated sql query accordingly, the rising column should get populated automatically.
I still having problems. Following your advice I have the following query, but it returns an error Parameter #1 has not been set.
My query is as follows:
SELECT
[EPOEventsMT].[ReceivedUTC] as [timestamp],
[EPOEventsMT].[AutoID],
[EPOEventsMT].[ThreatName] as [signature],
[EPOEventsMT].[ThreatType] as [threat_type],
[EPOEventsMT].[ThreatEventID] as [signature_id],
[EPOEventsMT].[ThreatCategory] as [category],
[EPOEventsMT].[ThreatSeverity] as [severity_id],
[EPOEventFilterDesc].[Name] as [event_description],
[EPOEventsMT].[DetectedUTC] as [detected_timestamp],
[EPOEventsMT].[TargetFileName] as [file_name],
[EPOEventsMT].[AnalyzerDetectionMethod] as [detection_method],
[EPOEventsMT].[ThreatActionTaken] as [vendor_action],
CAST([EPOEventsMT].[ThreatHandled] as int) as [threat_handled],
[EPOEventsMT].[TargetUserName] as [logon_user],
[EPOComputerPropertiesMT].[UserName] as [user],
[EPOComputerPropertiesMT].[DomainName] as [dest_nt_domain],
[EPOEventsMT].[TargetHostName] as [dest_dns],
[EPOEventsMT].[TargetHostName] as [dest_nt_host],
[EPOComputerPropertiesMT].[IPHostName] as [fqdn],
[dest_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerPropertiesMT].[IPV4x] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerPropertiesMT].[IPV4x] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerPropertiesMT].[IPV4x] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerPropertiesMT].[IPV4x] + 2147483648))),4,1))) ),
[EPOComputerPropertiesMT].[SubnetMask] as [dest_netmask],
[EPOComputerPropertiesMT].[NetAddress] as [dest_mac],
[EPOComputerPropertiesMT].[OSType] as [os],
[EPOComputerPropertiesMT].[OSCsdVersion] as [sp],
[EPOComputerPropertiesMT].[OSVersion] as [os_version],
[EPOComputerPropertiesMT].[OSBuildNum] as [os_build],
[EPOComputerPropertiesMT].[TimeZone] as [timezone],
[EPOEventsMT].[SourceHostName] as [src_dns],
[src_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEventsMT].[SourceIPV4] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEventsMT].[SourceIPV4] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEventsMT].[SourceIPV4] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEventsMT].[SourceIPV4] + 2147483648))),4,1))) ),
[EPOEventsMT].[SourceMAC] as [src_mac],
[EPOEventsMT].[SourceProcessName] as [process],
[EPOEventsMT].[SourceURL] as [url],
[EPOEventsMT].[SourceUserName] as [source_logon_user],
[EPOComputerPropertiesMT].[IsPortable] as [is_laptop],
[EPOEventsMT].[AnalyzerName] as [product],
[EPOEventsMT].[AnalyzerVersion] as [product_version],
[EPOEventsMT].[AnalyzerEngineVersion] as [engine_version],
[EPOEventsMT].[AnalyzerDATVersion] 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 "ePO_XXX-EPO-BOS-P01"."dbo"."EPOEventsMT"
LEFT JOIN "ePO_XXX-EPO-BOS-P01"."dbo"."EPOLeafNodeMT" ON [EPOEventsMT].[AgentGUID] = [EPOLeafNodeMT].[AgentGUID]
LEFT JOIN "ePO_XXX-EPO-BOS-P01"."dbo"."EPOProdPropsView_VIRUSCAN" ON [EPOLeafNodeMT].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID]
LEFT JOIN "ePO_XXX-EPO-BOS-P01"."dbo"."EPOComputerPropertiesMT" ON [EPOLeafNodeMT].[AutoID] = [EPOComputerPropertiesMT].[ParentID]
LEFT JOIN "ePO_XXX-EPO-BOS-P01"."dbo""EPOEventFilterDesc" ON [EPOEventsMT].[ThreatEventID] = [EPOEventFilterDesc].[EventId]
AND ([EPOEventFilterDesc].[Language]='0409')
WHERE [EPOEventsMT].[AutoID] > ? AND [EPOEventsMT].[ReceivedUTC] > DATEADD(day, -1, GETDATE() )
ORDER BY [EPOEventsMT].[AutoID] ASC
Hi, Sorry replying almost a year later.
As I have stated the my answer, you have to choose Connection, catalog, schema on the left side.