Custom DBConnect to get McAfee EPO inventory info

[kyleburk]

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

[GOB_Bluth]

This is helpful, thank you.
Curiously when I run this against epo 5.1 most of the records do not contain vse_dat_version fields which is what I really need.

Any ideas?

[richgalloway]

Thanks for sharing that, @kyleburk!
Would you mind editing your posting into question/answer format? Then you can accept the answer so future readers will find your solution.
Also, consider posting it on wiki.splunk.com.