I still having problems. Following your advice I have the following query, but it returns an error Parameter #1 has not been set.
My query is as follows:
SELECT
[EPOEventsMT].[ReceivedUTC] as [timestamp],
[EPOEventsMT].[AutoID],
[EPOEventsMT].[ThreatName] as [signature],
[EPOEventsMT].[ThreatType] as [threat_type],
[EPOEventsMT].[ThreatEventID] as [signature_id],
[EPOEventsMT].[ThreatCategory] as [category],
[EPOEventsMT].[ThreatSeverity] as [severity_id],
[EPOEventFilterDesc].[Name] as [event_description],
[EPOEventsMT].[DetectedUTC] as [detected_timestamp],
[EPOEventsMT].[TargetFileName] as [file_name],
[EPOEventsMT].[AnalyzerDetectionMethod] as [detection_method],
[EPOEventsMT].[ThreatActionTaken] as [vendor_action],
CAST([EPOEventsMT].[ThreatHandled] as int) as [threat_handled],
[EPOEventsMT].[TargetUserName] as [logon_user],
[EPOComputerPropertiesMT].[UserName] as [user],
[EPOComputerPropertiesMT].[DomainName] as [dest_nt_domain],
[EPOEventsMT].[TargetHostName] as [dest_dns],
[EPOEventsMT].[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].[OSCsdVersion] as [sp],
[EPOComputerPropertiesMT].[OSVersion] as [os_version],
[EPOComputerPropertiesMT].[OSBuildNum] as [os_build],
[EPOComputerPropertiesMT].[TimeZone] as [timezone],
[EPOEventsMT].[SourceHostName] as [src_dns],
[src_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEventsMT].[SourceIPV4] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEventsMT].[SourceIPV4] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEventsMT].[SourceIPV4] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEventsMT].[SourceIPV4] + 2147483648))),4,1))) ),
[EPOEventsMT].[SourceMAC] as [src_mac],
[EPOEventsMT].[SourceProcessName] as [process],
[EPOEventsMT].[SourceURL] as [url],
[EPOEventsMT].[SourceUserName] as [source_logon_user],
[EPOComputerPropertiesMT].[IsPortable] as [is_laptop],
[EPOEventsMT].[AnalyzerName] as [product],
[EPOEventsMT].[AnalyzerVersion] as [product_version],
[EPOEventsMT].[AnalyzerEngineVersion] as [engine_version],
[EPOEventsMT].[AnalyzerDATVersion] as [dat_version],
[EPOProdPropsView_VIRUSCAN].[datver] as [vse_dat_version],
[EPOProdPropsView_VIRUSCAN].[enginever64] as [vse_engine64_version],
[EPOProdPropsView_VIRUSCAN].[enginever] as [vse_engine_version],
[EPOProdPropsView_VIRUSCAN].[hotfix] as [vse_hotfix],
[EPOProdPropsView_VIRUSCAN].[productversion] as [vse_product_version],
[EPOProdPropsView_VIRUSCAN].[servicepack] as [vse_sp]
FROM "ePO_XXX-EPO-BOS-P01"."dbo"."EPOEventsMT"
LEFT JOIN "ePO_XXX-EPO-BOS-P01"."dbo"."EPOLeafNodeMT" ON [EPOEventsMT].[AgentGUID] = [EPOLeafNodeMT].[AgentGUID]
LEFT JOIN "ePO_XXX-EPO-BOS-P01"."dbo"."EPOProdPropsView_VIRUSCAN" ON [EPOLeafNodeMT].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID]
LEFT JOIN "ePO_XXX-EPO-BOS-P01"."dbo"."EPOComputerPropertiesMT" ON [EPOLeafNodeMT].[AutoID] = [EPOComputerPropertiesMT].[ParentID]
LEFT JOIN "ePO_XXX-EPO-BOS-P01"."dbo""EPOEventFilterDesc" ON [EPOEventsMT].[ThreatEventID] = [EPOEventFilterDesc].[EventId]
AND ([EPOEventFilterDesc].[Language]='0409')
WHERE [EPOEventsMT].[AutoID] > ? AND [EPOEventsMT].[ReceivedUTC] > DATEADD(day, -1, GETDATE() )
ORDER BY [EPOEventsMT].[AutoID] ASC
... View more
