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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...