SPLUNK DB read query to detect all McAfee products installed on the end client.

[Gopal_Patil]

I am trying to create a DB read query to in in SPLUNK that will list the details of McAfee VSE, HIPS, ENS TP/FW/ATP and HDLP  products installed on the system while running epo 5.10. 

[cdinet]

You can create the same query in epo console, save the query, then highlight it, actions, view sql.  That should give you the sql syntax you would need.

[Gopal_Patil]

I tried that but it keeps giving me partial result"lect top 360 count(*) as 'count', [EPOLeafNode].[AgentVersion], [EPOLeafNode].[AgentVersion] from [EPOLeafNode] group by [EPOLeafNode].[AgentVersion] order by 'count' desc, [EPOLeafNode].[AgentVersion] asc". 

I added the following columns "

Last Communication

System Name

User Name

Operating System

Tags

Product Version (Agent)

Product Version (DLP Endpoint)

Product Version (Endpoint Security Adaptive Threat Protection)

Adaptive Threat Protection content version

Product Version (Endpoint Security Firewall)

Product Version (Endpoint Security Platform)

Product Version (Endpoint Security Threat Prevention)

AMCore Content Date

Product Version (Host Intrusion Prevention)

Product Version (McAfee Firewall for Linux)

Product Version (VirusScan Enterprise for Storage)

DAT Version (VirusScan Enterprise)

Engine (x64) Version (VirusScan Enterprise)

Engine Version (VirusScan Enterprise)

Product Version (VirusScan Enterprise)

"

[cdinet]

If you run same query in epo, does it reproduce desired results?  Create your query in epo as a table, not a chart and see if that makes any difference - no grouping, etc.

[Gopal_Patil]

Yes, I get the desired results. I also tried changing the chart type from pie to table to multi summary, no luck. 

[cdinet]

Ok, copy the syntax you are running in splunk, then run it manually in sql management studio as query.  Does it show correct results there?

[Gopal_Patil]

I think I confused you, I am unable to get the full query from ePO to run in SPLUNK or SMSS. I get that partial SQL query when I select the query and do a view SQL. I need the full query to pull those columns.

[cdinet]

When you go to view sql, can you get a screenshot of that?  Otherwise, I can create it for you.

[cdinet · Accepted solution]

Here is the syntax I got when creating a table with your column names.

select [EPOLeafNode].[NodeName], [EPOLeafNode].[LastUpdate], [EPOComputerProperties].[UserName], dbo.epofn_EPOComputer_GetOS (EPOLeafNode.AutoID), [EPOLeafNode].[Tags], [EPOProdPropsView_EPOAGENT].[productversion], [EPOProdPropsView_UDLP].[productversion], [EPOProdPropsView_TIECLIENTMETA].[productversion], [ATP_CustomProps].[JTIContentVersion], [EPOProdPropsView_FIREWALL].[productversion], [EPOProdPropsView_ENDPOINTSECURITYPLATFORM].[productversion], [EPOProdPropsView_THREATPREVENTION].[productversion], [AM_CustomProps].[AMCoreContentDate], [EPOProdPropsView_HOSTIPS].[productversion], [EPOProdPropsView_MLFWALL].[productversion], [EPOProdPropsView_VSAS_STORAGE].[productversion], [EPOProdPropsView_VIRUSCAN].[datver], [EPOProdPropsView_VIRUSCAN].[enginever64], [EPOProdPropsView_VIRUSCAN].[enginever], [EPOProdPropsView_VIRUSCAN].[productversion], [EPOLeafNode].[AutoID] from [EPOLeafNode] left join [EPOProdPropsView_HOSTIPS] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_HOSTIPS].[LeafNodeID] left join [AM_CustomProps] on [EPOLeafNode].[AutoID] = [AM_CustomProps].[LeafNodeID] left join [EPOProdPropsView_VSAS_STORAGE] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VSAS_STORAGE].[LeafNodeID] left join [EPOProdPropsView_UDLP] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_UDLP].[LeafNodeID] left join [EPOProdPropsView_THREATPREVENTION] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_THREATPREVENTION].[LeafNodeID] left join [EPOComputerProperties] on [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID] left join [EPOProdPropsView_MLFWALL] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_MLFWALL].[LeafNodeID] left join [EPOProdPropsView_ENDPOINTSECURITYPLATFORM] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_ENDPOINTSECURITYPLATFORM].[LeafNodeID] left join [EPOProdPropsView_EPOAGENT] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_EPOAGENT].[LeafNodeID] left join [ATP_CustomProps] on [EPOLeafNode].[AutoID] = [ATP_CustomProps].[LeafNodeID] left join [EPOProdPropsView_TIECLIENTMETA] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_TIECLIENTMETA].[LeafNodeID] left join [EPOProdPropsView_VIRUSCAN] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID] left join [EPOProdPropsView_FIREWALL] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_FIREWALL].[LeafNodeID] order by [EPOLeafNode].[NodeName] asc