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 more