Splunk Enterprise Security

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

Ohiotech
Explorer

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"

0 Karma

Ohiotech
Explorer

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?

0 Karma

Ohiotech
Explorer

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

0 Karma

solarboyz1
Builder

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...