Wanted to share this with community: We use the query below to collect a static inventory of systems currently in McAfee EPO, as well as information on their product installations. We set this up as a batch job that collects full information every 30 minutes or so. Very useful for product coverage graphs, as well as a custom section we added in to give us a list of devices where on-access scan has been disabled by policy or user. Running queries against this for product coverage, dat coverage, etc is much quicker than trying to aggregate data on devices over a long period of time, especially if you have traveling devices that may not check in for a couple days at a time. We place this into the mcafee index with sourcetype=mcafee:epo:inventory SELECT a.NodeName AS dest_nt_host, b.product, b.oas_status, b.vse_dat_version, b.vse_engine64_version, b.vse_engine_version, b.vse_hotfix, b.vse_product_version, b.vse_sp, b.enstp_dat_version, b.enstp_engine64_version, b.enstp_engine_version, b.enstp_hotfix, b.enstp_product_version, b.ma_product_version, b.enspf_product_version, b.ensfw_product_version, b.enswc_product_version FROM ( SELECT DISTINCT [EPOLeafNode].[NodeName] FROM [EPOLeafNode] ) a LEFT JOIN ( SELECT [EPOLeafNode].[NodeName] AS [dest_nt_host], CASE WHEN [EPOProductProperties].[ProductCode] LIKE 'VIRUSCAN%' THEN 'VirusScan Enterprise' WHEN [EPOProductProperties].[ProductCode] LIKE 'ENDP_AM%' THEN 'McAfee Endpoint Security' ELSE NULL END AS [product], CASE WHEN vseOASEnabled.value LIKE '1' THEN 'Enabled' WHEN [AM_CustomProps].bOASEnabled LIKE '1' THEN 'Enabled' WHEN vseOASEnabled.value LIKE '0' THEN 'Disabled' WHEN [AM_CustomProps].bOASEnabled LIKE '0' THEN 'Disabled' ELSE 'Unknown' END AS [oas_status], [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], [EPOProdPropsView_THREATPREVENTION].[verDAT32Major] as [enstp_dat_version], [EPOProdPropsView_THREATPREVENTION].[verEngine64Major] as [enstp_engine64_version], [EPOProdPropsView_THREATPREVENTION].[verEngine32Major] as [enstp_engine_version], [EPOProdPropsView_THREATPREVENTION].[verHotfix] as [enstp_hotfix], [EPOProdPropsView_THREATPREVENTION].[productversion] as [enstp_product_version], [EPOProdPropsView_EPOAGENT].[productversion] as [ma_product_version], [EPOProdPropsView_ENDPOINTSECURITYPLATFORM].[productversion] as [enspf_product_version], [EPOProdPropsView_FIREWALL].[productversion] as [ensfw_product_version], [EPOProdPropsView_WEBCONTROL].[productversion] as [enswc_product_version] FROM [EPOLeafNode] INNER JOIN [EPOProductProperties] ON [EPOLeafNode].[AutoID] = [EPOProductProperties].[ParentID] LEFT JOIN [AM_CustomProps] ON [EPOLeafNode].[AutoID] = [AM_CustomProps].[LeafNodeID] LEFT JOIN [dbo].EPOProductSettings AS vseOASEnabled ON (EPOProductProperties.AutoID = vseOASEnabled.ParentID AND vseOASEnabled.SectionName = N'On-Access General' AND vseOASEnabled.SettingName = N'bEnabled') LEFT JOIN [EPOProdPropsView_THREATPREVENTION] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_THREATPREVENTION].[LeafNodeID] LEFT JOIN [EPOProdPropsView_EPOAGENT] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_EPOAGENT].[LeafNodeID] LEFT JOIN [EPOProdPropsView_ENDPOINTSECURITYPLATFORM] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_ENDPOINTSECURITYPLATFORM].[LeafNodeID] LEFT JOIN [EPOProdPropsView_FIREWALL] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_FIREWALL].[LeafNodeID] LEFT JOIN [EPOProdPropsView_WEBCONTROL] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_WEBCONTROL].[LeafNodeID] LEFT JOIN [EPOProdPropsView_VIRUSCAN] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID] WHERE EPOProductProperties.ProductCode LIKE 'VIRUSCAN%' OR EPOProductProperties.ProductCode LIKE 'ENDP_AM%' ) b ON a.NodeName = b.dest_nt_host ... View more

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 dest_nt_host 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 [threat_type], [EPOEvents].[ThreatEventID] AS [signature_id], [EPOEvents].[ThreatCategory] AS [category], [EPOEvents].[ThreatSeverity] AS [severity_id], [EPOEventFilterDesc].[Name] AS [event_description], [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], [EPOComputerProperties].[DomainName] AS [dest_nt_domain], [EPOEvents].[TargetHostName] AS [dest_dns], [EPOComputerProperties].[ComputerName] AS [dest_nt_host], [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 [dest_netmask], [EPOComputerProperties].[NetAddress] AS [dest_mac], [EPOComputerProperties].[OSType] AS [os], [EPOComputerProperties].[OSServicePackVer] AS [sp], [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], [EPOComputerProperties].[IsPortable] AS [is_laptop], [EPOEvents].[AnalyzerName] AS [product], [EPOEvents].[AnalyzerVersion] AS [product_version], [EPOEvents].[AnalyzerEngineVersion] AS [engine_version], [EPOEvents].[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], [EPOProdPropsView_THREATPREVENTION].[verDAT32Major] as [enstp_dat_version], [EPOProdPropsView_THREATPREVENTION].[verEngine64Major] as [enstp_engine64_version], [EPOProdPropsView_THREATPREVENTION].[verEngine32Major] as [enstp_engine_version], [EPOProdPropsView_THREATPREVENTION].[verHotfix] as [enstp_hotfix], [EPOProdPropsView_THREATPREVENTION].[productversion] as [enstp_product_version], [EPOProdPropsView_EPOAGENT].[productversion] as [ma_product_version], [EPOProdPropsView_ENDPOINTSECURITYPLATFORM].[productversion] as [enspf_product_version], [EPOProdPropsView_FIREWALL].[productversion] as [ensfw_product_version], [EPOProdPropsView_WEBCONTROL].[productversion] as [enswc_product_version] FROM [EPOEvents] LEFT JOIN [EPOLeafNode] ON [EPOEvents].[AgentGUID] = [EPOLeafNode].[AgentGUID] LEFT JOIN [EPOProdPropsView_VIRUSCAN] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[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] = [EPOProdPropsView_THREATPREVENTION].[LeafNodeID] LEFT JOIN [EPOProdPropsView_EPOAGENT] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_EPOAGENT].[LeafNodeID] LEFT JOIN [EPOProdPropsView_ENDPOINTSECURITYPLATFORM] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_ENDPOINTSECURITYPLATFORM].[LeafNodeID] LEFT JOIN [EPOProdPropsView_FIREWALL] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_FIREWALL].[LeafNodeID] LEFT JOIN [EPOProdPropsView_WEBCONTROL] ON [EPOLeafNode].[AutoID] = [EPOProdPropsView_WEBCONTROL].[LeafNodeID] WHERE [EPOEvents].[AutoID] > ? ORDER BY [EPOEvents].[AutoID] ASC ``` ... View more