All Apps and Add-ons

Custom DBConnect to get McAfee EPO inventory info

kyleburk
Explorer

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
Explorer

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?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...