All Apps and Add-ons

Working DBConnect Query for McAfee with ENS

Explorer

Not sure where the best place to put this is but i wanted to provide the SQL query we're using for the McAfee Add-on.
This includes integration with Endpoint Security Threat Prevention. This also fixes an issue with the default query where the destnthost field was being populated from a less-than-ideal location (i believe this to essentially be a bug).

```

SELECT [EPOEvents].[ReceivedUTC] AS [timestamp],
[EPOEvents].[AutoID],
[EPOEvents].[ThreatName] AS [signature],
[EPOEvents].[ThreatType] AS [threattype],
[EPOEvents].[ThreatEventID] AS [signature
id],
[EPOEvents].[ThreatCategory] AS [category],
[EPOEvents].[ThreatSeverity] AS [severityid],
[EPOEventFilterDesc].[Name] AS [event
description],
[EPOEvents].[DetectedUTC] AS [detectedtimestamp],
[EPOEvents].[TargetFileName] AS [file
name],
[EPOEvents].[AnalyzerDetectionMethod] AS [detectionmethod],
[EPOEvents].[ThreatActionTaken] AS [vendor
action],
CAST([EPOEvents].[ThreatHandled] AS int) AS [threathandled],
[EPOEvents].[TargetUserName] AS [logon
user],
[EPOComputerProperties].[UserName] AS [user],
[EPOComputerProperties].[DomainName] AS [destntdomain],
[EPOEvents].[TargetHostName] AS [destdns],
[EPOComputerProperties].[ComputerName] AS [dest
nthost],
[EPOComputerProperties].[IPHostName] AS [fqdn],
[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 [destnetmask],
[EPOComputerProperties].[NetAddress] AS [dest
mac],
[EPOComputerProperties].[OSType] AS [os],
[EPOComputerProperties].[OSServicePackVer] AS [sp],
[EPOComputerProperties].[OSVersion] AS [osversion],
[EPOComputerProperties].[OSBuildNum] AS [os
build],
[EPOComputerProperties].[TimeZone] AS [timezone],
[EPOEvents].[SourceHostName] AS [srcdns],
[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 [srcmac],
[EPOEvents].[SourceProcessName] AS [process],
[EPOEvents].[SourceURL] AS [url],
[EPOEvents].[SourceUserName] AS [source
logonuser],
[EPOComputerProperties].[IsPortable] AS [is
laptop],
[EPOEvents].[AnalyzerName] AS [product],
[EPOEvents].[AnalyzerVersion] AS [productversion],
[EPOEvents].[AnalyzerEngineVersion] AS [engine
version],
[EPOEvents].[AnalyzerDATVersion] AS [datversion],
[EPOProdPropsView
VIRUSCAN].[datver] AS [vsedatversion],
[EPOProdPropsViewVIRUSCAN].[enginever64] AS [vseengine64version],
[EPOProdPropsView
VIRUSCAN].[enginever] AS [vseengineversion],
[EPOProdPropsViewVIRUSCAN].[hotfix] AS [vsehotfix],
[EPOProdPropsViewVIRUSCAN].[productversion] AS [vseproductversion],
[EPOProdPropsView
VIRUSCAN].[servicepack] AS [vsesp],
[EPOProdPropsView
THREATPREVENTION].[verDAT32Major] as [enstpdatversion],
[EPOProdPropsViewTHREATPREVENTION].[verEngine64Major] as [enstpengine64version],
[EPOProdPropsView
THREATPREVENTION].[verEngine32Major] as [enstpengineversion],
[EPOProdPropsViewTHREATPREVENTION].[verHotfix] as [enstphotfix],
[EPOProdPropsViewTHREATPREVENTION].[productversion] as [enstpproductversion],
[EPOProdPropsView
EPOAGENT].[productversion] as [maproductversion],
[EPOProdPropsViewENDPOINTSECURITYPLATFORM].[productversion] as [enspfproductversion],
[EPOProdPropsView
FIREWALL].[productversion] as [ensfwproductversion],
[EPOProdPropsViewWEBCONTROL].[productversion] as [enswcproductversion]
FROM [EPOEvents]
LEFT JOIN [EPOLeafNode]
ON [EPOEvents].[AgentGUID] = [EPOLeafNode].[AgentGUID]
LEFT JOIN [EPOProdPropsView
VIRUSCAN]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsViewVIRUSCAN].[LeafNodeID]
LEFT JOIN [EPOComputerProperties]
ON [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID]
LEFT JOIN [EPOEventFilterDesc]
ON [EPOEvents].[ThreatEventID] = [EPOEventFilterDesc].[EventId]
AND ([EPOEventFilterDesc].[Language]='0409')
LEFT JOIN [EPOProdPropsView
THREATPREVENTION]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsViewTHREATPREVENTION].[LeafNodeID]
LEFT JOIN [EPOProdPropsView
EPOAGENT]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsViewEPOAGENT].[LeafNodeID]
LEFT JOIN [EPOProdPropsView
ENDPOINTSECURITYPLATFORM]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsViewENDPOINTSECURITYPLATFORM].[LeafNodeID]
LEFT JOIN [EPOProdPropsView
FIREWALL]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsViewFIREWALL].[LeafNodeID]
LEFT JOIN [EPOProdPropsView
WEBCONTROL]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_WEBCONTROL].[LeafNodeID]
WHERE [EPOEvents].[AutoID] > ?
ORDER BY [EPOEvents].[AutoID] ASC

```

Path Finder

In DBConnect 2 error t', params: 'None', caused by: Exception(\' com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1..\',).',)"

I am getting the same exception, any idea how we can resolve this.

0 Karma

Explorer

Hi,

If we need to be using the rising column parameter as a checkpoint, can you confirm that
WHERE [EPOEvents].[AutoID] > ?
ORDER BY [EPOEvents].[AutoID] ASC

should be replaced with

WHERE [EPOEvents].[AutoID] > 0 {{ AND [EPOEvents].$rising_column$ > ? }} ORDER BY [EPOEvents].[AutoID]

Thanks

0 Karma

Explorer

I use the EPOEvents.AutoID as my rising column and run the posted query as is.

0 Karma

Explorer

The request raised an error in dbx.log, indicating that {{ AND [EPOEvents].$rising_column$ > ? }} was missing.
Can you confirm that in order to use it in dbx connect v1, it can be modified with the aforementioned modification?

Maybe db connect v2 introduces this condition in the configuration file when you select the rising column parameter in the GUI?

0 Karma