All Apps and Add-ons

Custom DBConnect to get McAfee EPO inventory info

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 destnthost,
b.product,
b.oasstatus,
b.vse
datversion,
b.vse
engine64version,
b.vse
engineversion,
b.vse
hotfix,
b.vseproductversion,
b.vsesp,
b.enstp
datversion,
b.enstp
engine64version,
b.enstp
engineversion,
b.enstp
hotfix,
b.enstpproductversion,
b.maproductversion,
b.enspfproductversion,
b.ensfwproductversion,
b.enswcproductversion

FROM ( SELECT DISTINCT [EPOLeafNode].[NodeName]
FROM [EPOLeafNode] ) a
LEFT JOIN (
SELECT
[EPOLeafNode].[NodeName] AS [destnthost],
CASE
WHEN [EPOProductProperties].[ProductCode] LIKE 'VIRUSCAN%' THEN 'VirusScan Enterprise'
WHEN [EPOProductProperties].[ProductCode] LIKE 'ENDPAM%' 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 [AMCustomProps].bOASEnabled LIKE '0' THEN 'Disabled'
ELSE 'Unknown'
END AS [oas
status],
[EPOProdPropsViewVIRUSCAN].[datver] AS [vsedatversion],
[EPOProdPropsView
VIRUSCAN].[enginever64] AS [vseengine64version],
[EPOProdPropsViewVIRUSCAN].[enginever] AS [vseengineversion],
[EPOProdPropsView
VIRUSCAN].[hotfix] AS [vsehotfix],
[EPOProdPropsView
VIRUSCAN].[productversion] AS [vseproductversion],
[EPOProdPropsViewVIRUSCAN].[servicepack] AS [vsesp],
[EPOProdPropsViewTHREATPREVENTION].[verDAT32Major] as [enstpdatversion],
[EPOProdPropsView
THREATPREVENTION].[verEngine64Major] as [enstpengine64version],
[EPOProdPropsViewTHREATPREVENTION].[verEngine32Major] as [enstpengineversion],
[EPOProdPropsView
THREATPREVENTION].[verHotfix] as [enstphotfix],
[EPOProdPropsView
THREATPREVENTION].[productversion] as [enstpproductversion],
[EPOProdPropsViewEPOAGENT].[productversion] as [maproductversion],
[EPOProdPropsView
ENDPOINTSECURITYPLATFORM].[productversion] as [enspfproductversion],
[EPOProdPropsViewFIREWALL].[productversion] as [ensfwproductversion],
[EPOProdPropsView
WEBCONTROL].[productversion] as [enswcproductversion]

FROM
[EPOLeafNode]
INNER JOIN [EPOProductProperties] ON [EPOLeafNode].[AutoID] = [EPOProductProperties].[ParentID]
LEFT JOIN [AMCustomProps] ON [EPOLeafNode].[AutoID] = [AMCustomProps].[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 [EPOProdPropsViewTHREATPREVENTION]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsView
THREATPREVENTION].[LeafNodeID]
LEFT JOIN [EPOProdPropsViewEPOAGENT]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsView
EPOAGENT].[LeafNodeID]
LEFT JOIN [EPOProdPropsViewENDPOINTSECURITYPLATFORM]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsView
ENDPOINTSECURITYPLATFORM].[LeafNodeID]
LEFT JOIN [EPOProdPropsViewFIREWALL]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsView
FIREWALL].[LeafNodeID]
LEFT JOIN [EPOProdPropsViewWEBCONTROL]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsView
WEBCONTROL].[LeafNodeID]
LEFT JOIN [EPOProdPropsViewVIRUSCAN]
ON [EPOLeafNode].[AutoID] = [EPOProdPropsView
VIRUSCAN].[LeafNodeID]
WHERE
EPOProductProperties.ProductCode LIKE 'VIRUSCAN%'
OR EPOProductProperties.ProductCode LIKE 'ENDPAM%'
) b
ON a.NodeName = b.dest
nt_host

Explorer

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

Any ideas?

0 Karma

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, an upvote would be appreciated.
0 Karma