All Apps and Add-ons

Splunk DB Connect & McAfee ePO integration: How to remove duplicate entries from a search on SQL DB?

vrajshekar
Path Finder

I have integrated McAfee ePO 5.10 with Splunk 8.0.3 using DB-connect. I am seeing a lot of duplicate entries when I run the search below on SQL DB.

In the EPO-Events table, I only have 39 rows, whereas when I run this search it turns out to be 1,521 rows.
Could someone please help? I am new to this.

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],
         [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],
         [EPOComputerPropertiesMT].[DomainName] AS [dest_nt_domain],
         [EPOEvents].[TargetHostName] AS [dest_dns],
         [EPOEvents].[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].[OSVersion] AS [os_version], [EPOComputerPropertiesMT].[OSBuildNum] AS [os_build], [EPOComputerPropertiesMT].[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], [EPOEvents].[AnalyzerName] AS [product], [EPOEvents].[AnalyzerVersion] AS [product_version], [EPOEvents].[AnalyzerEngineVersion] AS [engine_version], [EPOEvents].[AnalyzerDATVersion] AS [dat_version], [EPExtendedEvent].[SourceHash], [EPExtendedEvent].[SourceParentProcessHash], [EPExtendedEvent].[SourceProcessHash], [EPExtendedEvent].[TargetHash], [EPOProdPropsView_THREATPREVENTION].[verDAT32Major] AS [TP_dat_version], [EPOProdPropsView_THREATPREVENTION].[verEngine32Major] AS [TP_engine32_version], [EPOProdPropsView_THREATPREVENTION].[verEngine64Major] AS [TP_engine64_version], [EPOProdPropsView_THREATPREVENTION].[verHotfix] AS [TP_hotfix], [EPOProdPropsView_THREATPREVENTION].[ProductVersion] AS [TP_product_version]
FROM "ePO_INSTANCE-1"."dbo"."EPOEvents", "ePO_INSTANCE-1"."dbo"."EPOProdPropsView_THREATPREVENTION", "ePO_INSTANCE-1"."dbo"."EPOComputerPropertiesMT", "ePO_INSTANCE-1"."dbo"."EPOComputerProperties", "ePO_INSTANCE-1"."dbo"."EPExtendedEvent"
ORDER BY  AutoID ASC
Labels (2)
0 Karma
1 Solution

vrajshekar
Path Finder

Working query

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],
[EPOEvents].[DetectedUTC] AS [detected_timestamp],
[EPOEvents].[TargetFileName] AS [file_name],
[EPExtendedEvent].[SourceHash] AS [SourceHash],
[EPExtendedEvent].[SourceParentProcessHash] AS [SourceParentProcessHash],
[EPExtendedEvent].[SourceProcessHash] AS [SourceProcessHash],
[EPExtendedEvent].[TargetHash] AS [TargetHash],
[EPOEvents].[AnalyzerDetectionMethod] AS [detection_method],
[EPOEvents].[ThreatActionTaken] AS [vendor_action],
[EPOEvents].[TargetUserName] AS [logon_user],
[EPOComputerProperties].[DomainName] AS [dest_nt_domain],
[EPOEvents].[TargetHostName] AS [dest_dns],
[EPOEvents].[TargetHostName] AS [dest_nt_host],
[EPOComputerProperties].[IPHostName] AS [fqdn],
CAST([EPOEvents].[ThreatHandled] AS int) AS [threat_handled],
[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].[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], [EPOEvents].[AnalyzerName] AS [product], [EPOEvents].[AnalyzerVersion] AS [product_version], [EPOEvents].[AnalyzerEngineVersion] AS [engine_version], [EPOEvents].[AnalyzerDATVersion] AS [dat_version], [EPOProdPropsView_THREATPREVENTION].[verDAT32Major] AS [TP_dat_version], [EPOProdPropsView_THREATPREVENTION].[verEngine32Major] AS [TP_engine32_version], [EPOProdPropsView_THREATPREVENTION].[verEngine64Major] AS [TP_engine64_version], [EPOProdPropsView_THREATPREVENTION].[verHotfix] AS [TP_hotfix], [EPOProdPropsView_THREATPREVENTION].[ProductVersion] AS [TP_product_version]
FROM [EPOEvents] LEFT JOIN [EPOLeafNodeMT] ON EPOEvents.AgentGUID = [EPOLeafNodeMT].[AgentGUID] LEFT JOIN [EPOEventFilterDesc] ON EPOEvents.[ThreatEventID] = [EPOEventFilterDesc].[EventId] AND ([EPOEventFilterDesc].[Language]='0409') LEFT JOIN [EPOComputerProperties] ON EPOLeafNodeMT.AutoID = [EPOComputerProperties].[ParentID] LEFT JOIN [EPOProdPropsView_THREATPREVENTION] ON EPOLeafNodeMT.AutoID = [EPOProdPropsView_THREATPREVENTION].[LeafNodeID] LEFT JOIN [EPExtendedEvent] ON [EPOEvents].[AutoID] = [EPExtendedEvent].[EventAutoID]
ORDER BY [EPOEvents].[DetectedUTC] asc

View solution in original post

0 Karma

vrajshekar
Path Finder

Working query

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],
[EPOEvents].[DetectedUTC] AS [detected_timestamp],
[EPOEvents].[TargetFileName] AS [file_name],
[EPExtendedEvent].[SourceHash] AS [SourceHash],
[EPExtendedEvent].[SourceParentProcessHash] AS [SourceParentProcessHash],
[EPExtendedEvent].[SourceProcessHash] AS [SourceProcessHash],
[EPExtendedEvent].[TargetHash] AS [TargetHash],
[EPOEvents].[AnalyzerDetectionMethod] AS [detection_method],
[EPOEvents].[ThreatActionTaken] AS [vendor_action],
[EPOEvents].[TargetUserName] AS [logon_user],
[EPOComputerProperties].[DomainName] AS [dest_nt_domain],
[EPOEvents].[TargetHostName] AS [dest_dns],
[EPOEvents].[TargetHostName] AS [dest_nt_host],
[EPOComputerProperties].[IPHostName] AS [fqdn],
CAST([EPOEvents].[ThreatHandled] AS int) AS [threat_handled],
[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].[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], [EPOEvents].[AnalyzerName] AS [product], [EPOEvents].[AnalyzerVersion] AS [product_version], [EPOEvents].[AnalyzerEngineVersion] AS [engine_version], [EPOEvents].[AnalyzerDATVersion] AS [dat_version], [EPOProdPropsView_THREATPREVENTION].[verDAT32Major] AS [TP_dat_version], [EPOProdPropsView_THREATPREVENTION].[verEngine32Major] AS [TP_engine32_version], [EPOProdPropsView_THREATPREVENTION].[verEngine64Major] AS [TP_engine64_version], [EPOProdPropsView_THREATPREVENTION].[verHotfix] AS [TP_hotfix], [EPOProdPropsView_THREATPREVENTION].[ProductVersion] AS [TP_product_version]
FROM [EPOEvents] LEFT JOIN [EPOLeafNodeMT] ON EPOEvents.AgentGUID = [EPOLeafNodeMT].[AgentGUID] LEFT JOIN [EPOEventFilterDesc] ON EPOEvents.[ThreatEventID] = [EPOEventFilterDesc].[EventId] AND ([EPOEventFilterDesc].[Language]='0409') LEFT JOIN [EPOComputerProperties] ON EPOLeafNodeMT.AutoID = [EPOComputerProperties].[ParentID] LEFT JOIN [EPOProdPropsView_THREATPREVENTION] ON EPOLeafNodeMT.AutoID = [EPOProdPropsView_THREATPREVENTION].[LeafNodeID] LEFT JOIN [EPExtendedEvent] ON [EPOEvents].[AutoID] = [EPExtendedEvent].[EventAutoID]
ORDER BY [EPOEvents].[DetectedUTC] asc

0 Karma

PavelP
Motivator

Hello @vrajshekar

is it possible you have 1512 different events in the DB? They cannot be duplicate because they have different AutoID values.

0 Karma

vrajshekar
Path Finder

Nope. That was the first thing I checked.
EPOEvents has 39 rows and EPExtendedEvent has 39 rows.

When I run
select * from EPOEvents, EPExtendedEvent

It return 1521 rows.

0 Karma

PavelP
Motivator

difficult to say without seeing your data, but seems to be not a splunk issue.

Have you tried ask on McAfee community forum:

ePO: https://community.mcafee.com/t5/ePolicy-Orchestrator/bd-p/epolicy-orchestrator

SIEM: https://community.mcafee.com/t5/Security-Information-and-Event/bd-p/siem

If you post there, please leave a link here, so everybody can benefit.

0 Karma

vrajshekar
Path Finder
0 Karma

PavelP
Motivator

@vrajshekar the second link is not available: The message you are trying to access is not available.

0 Karma

vrajshekar
Path Finder

@PavelIP Just edited the comment, moderator removed the question since they are already addressing the same issue in the other post.

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