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

[vrajshekar]

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

[vrajshekar · Accepted solution]

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

[PavelP]

Hello @vrajshekar

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

[vrajshekar]

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.

[PavelP]

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.

[vrajshekar]

Thanks,

I've posted there as well.

Refer to the links below:
https://community.mcafee.com/t5/ePolicy-Orchestrator/McAfee-sql-db-duplicate-entries/m-p/659074#M726...

[PavelP]

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

[vrajshekar]

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