All Apps and Add-ons

Rising column empty while setting an input Splunk DBConnect app

damode
Motivator

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

Labels (1)
0 Karma
1 Solution

damode
Motivator

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.

View solution in original post

damode
Motivator

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
New Member

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

0 Karma

damode
Motivator

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.

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...