Rising column empty while setting an input Splunk DBConnect app

[damode]

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

[damode · Accepted solution]

Answering my own question here. I was able to fix the issue by doing the following,

1. From the FROM keyword in the sql query, replaced all table names from EPOEvents --> "ePO_ABCEPO_Events"."dbo"."EPOEvents"
2. Added this statement - AND [EPOEvents].[ReceivedUTC] > DATEADD(day, -1, GETDATE() ) at the end of the query as the query used to get timed out all the time.
3. the default template query mentions 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] ASC

If 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.

[abeeber_5]

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

[damode]

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.