How to add an extra event field to the ePO query using DB connect 2.4 and ePO 5.3?

[Ohiotech]

Additional information: I'm not confident on the left join syntax, but the query appears to fail before it gets to that syntax. Also, using MS SQL mgmt studio, this query without my changes fails a syntax check.

SELECT [ReceivedUTC] AS [timestamp],
[ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[AutoID],
[ThreatName] AS [signature],
[ThreatType] AS [threat_type],
[ThreatEventID] AS [signature_id],
[ThreatCategory] AS [category],
[ThreatSeverity] AS [severity_id],
[DetectedUTC] AS [detected_timestamp],
[TargetFileName] AS [file_name],
[AnalyzerDetectionMethod] AS [detection_method],
[TargetHash] AS [target_hash],
[ThreatActionTaken] AS [vendor_action],
CAST([ThreatHandled] AS int) AS [threat_handled],
[TargetUserName] AS [logon_user],
[ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[UserName] AS [user],
[ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[DomainName] AS [dest_nt_domain],
[ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[TargetHostName] AS [dest_dns],
[ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[TargetHostName] AS [dest_nt_host],
[ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[IPHostName] AS [fqdn],
[dest_ip] = ( convert(varchar(3),nx
convert(tinyint,
substring(convert(varbinary(4),
convert(bigint,
([ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[IPV4x] + 2147483648))),
1,
1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[IPV4x] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[IPV4x] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[IPV4x] + 2147483648))),4,1))) ), [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[SubnetMask] AS [dest_netmask], [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[NetAddress] AS [dest_mac], [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[OSType] AS [os], [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[OSServicePackVer] AS [sp], [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[OSVersion] AS [os_version], [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[OSBuildNum] AS [os_build], [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[TimeZone] AS [timezone], [ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[SourceHostName] AS [src_dns], [src_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[SourceIPV4] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[SourceIPV4] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[SourceIPV4] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[SourceIPV4] + 2147483648))),4,1))) ), [ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[SourceMAC] AS [src_mac], [ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[SourceProcessName] AS [process], [ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[SourceURL] AS [url], [ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[SourceUserName] AS [source_logon_user], [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[IsPortable] AS [is_laptop], [ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[AnalyzerName] AS [product], [ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[AnalyzerVersion] AS [product_version], [ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[AnalyzerEngineVersion] AS [engine_version], [ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[AnalyzerEngineVersion] AS [dat_version], [ePO_xxxxxxxxxxxx].[dbo].[EPOProductPropertiesMT].[DATVer] AS [vse_dat_version], [ePO_xxxxxxxxxxxx].[dbo].[EPOProductPropertiesMT].[EngineVer64] AS [vse_engine64_version], [ePO_xxxxxxxxxxxx].[dbo].[EPOProductPropertiesMT].[EngineVer] AS [vse_engine_version], [ePO_xxxxxxxxxxxx].[dbo].[EPOProductPropertiesMT].[Hotfix] AS [vse_hotfix], [ePO_xxxxxxxxxxxx].[dbo].[EPOProductPropertiesMT].[ProductVersion] AS [vse_product_version], [ePO_xxxxxxxxxxxx].[dbo].[EPOProductPropertiesMT].[Servicepack] AS [vse_sp]
FROM "ePO_xxxxxxxxxxxx"."dbo"."EPOEventsMT"
LEFT JOIN [ePO_xxxxxxxxxxxx].[dbo].[EPOLeafNodeMT]
ON [EPOEventsMT].[AgentGUID] = [ePO_xxxxxxxxxxxx].[dbo].[EPOLeafNodeMT].[AgentGUID]
LEFT JOIN [ePO_xxxxxxxxxxxx].[dbo].[EPOProductPropertiesMT]
ON [ePO_xxxxxxxxxxxx].[dbo].[EPOLeafNodeMT].[AutoID] = [ePO_xxxxxxxxxxxx].[dbo].[EPOProductPropertiesMT].[AutoID]
LEFT JOIN [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT]
ON [ePO_xxxxxxxxxxxx].[dbo].[EPOLeafNodeMT].[AutoID] = [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[ParentID]
LEFT JOIN [ePO_xxxxxxxxxxxx].[dbo].[EPOEventFilterDesc]
ON [EPOEventsMT].[ThreatEventID] = [ePO_xxxxxxxxxxxx].[dbo].[EPOEventFilterDesc].[EventId]
AND (EPOEventFilterDesc.Language='0409')
LEFT JOIN [ePO_xxxxxxxxxxxx].[dbo].[EPExtendedEventMT]
ON [ePO_xxxxxxxxxxxx].[dbo].[EPOLeafNodeMT].[AutoID] = [ePO_xxxxxxxxxxxx].[dbo].[EPOEventFilterDesc].[TargetHash]

Invalid Query

External search command 'dbxquery' returned error code 1. First 1000 (of 5282) bytes of script output: "RuntimeError: Failed to run query: "SELECT * FROM (SELECT [ReceivedUTC] AS [timestamp], [ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[AutoID], [ThreatName] AS [signature], [ThreatType] AS [threat_type], [ThreatEventID] AS [signature_id], [ThreatCategory] AS [category], [ThreatSeverity] AS [severity_id], [DetectedUTC] AS [detected_timestamp], [TargetFileName] AS [file_name], [AnalyzerDetectionMethod] AS [detection_method], [TargetHash] AS [target_hash], [ThreatActionTaken] AS [vendor_action], CAST([ThreatHandled] AS int) AS [threat_handled], [TargetUserName] AS [logon_user], [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[UserName] AS [user], [ePO_xxxxxxxxxxxx].[dbo].[EPOComputerPropertiesMT].[DomainName] AS [dest_nt_domain], [ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[TargetHostName] AS [dest_dns], [ePO_xxxxxxxxxxxx].[dbo].[EPOEven"

[Ohiotech]

My apologies for the delay, I went on PTO right after posting this question.

I changed the query to match the full path and added the correct table per your statements. I still get the same invalid query response with a slightly different output that doesn't supply the full output of the error.

The DB structure is the default ePO tables for version 5.3. This does not make sense that the Splunk supplied query (what is currently working) and a simple change of an additional field (corrected from your suggestion) fails to execute in the DB connect app.

Is this type of issue supported by Splunk Support for DB connect?

[Ohiotech]

Working Solution*****
I worked with a Splunk SME and added the following syntax which gave me a column for target_hash and saved in the input statement without error***

[AnalyzerDetectionMethod] AS [detection_method],
[ThreatActionTaken] AS [vendor_action],
[ePO_xxxxxxxxxxxx].[dbo].[EPExtendedEventMT].[TargetHash] AS [target_hash],
CAST([ThreatHandled] AS int) AS [threat_handled],

LEFT JOIN [ePO_xxxxxxxxxxxx].[dbo].[EPExtendedEventMT]
ON [ePO_xxxxxxxxxxxx].[dbo].[EPOLeafNodeMT].[AutoID] = [ePO_xxxxxxxxxxxx].[dbo].[EPExtendedEventMT].[EventAutoID]
AND (EPOEventFilterDesc.Language='0409')) t WHERE AutoID > ? ORDER BY AutoID ASC

[solarboyz1]

It appears you are referencing the threatHash field incorrectly.

SELECT [ReceivedUTC] AS [timestamp],
[ePO_xxxxxxxxxxxx].[dbo].[EPOEventsMT].[AutoID],
[ThreatName] AS [signature],
[ThreatType] AS [threat_type],
[ThreatEventID] AS [signature_id],
[ThreatCategory] AS [category],
[ThreatSeverity] AS [severity_id],
[DetectedUTC] AS [detected_timestamp],
[TargetFileName] AS [file_name],
[AnalyzerDetectionMethod] AS [detection_method],
[ePO_xxxxxxxxxxxx].[dbo].[EPExtendedEventMT].[TargetHash] AS [target_hash],
.
.
LEFT JOIN [ePO_xxxxxxxxxxxx].[dbo].[EPExtendedEventMT]
ON [ePO_xxxxxxxxxxxx].[dbo].[EPOLeafNodeMT].[AutoID] = [ePO_xxxxxxxxxxxx].[dbo].[EPExtendedEventMT].[TargetHash]

target_hash in a different table, so the full path is needed.
Also, you had a LEFT Join statement, but the ON statement didn't include the same table as you were joining.

Those are the issues that stood out to me, without seeing your DB structure.