Reporting Group

HI

I looking for SQL Query report which shows me computers with no plug in installed. Like computers who does not have Inventory solution plug-in, Computers who does not have Software management plug-in etc

We have SQL query which give computers with plug in installed but also i need computers who miss out to installed certain or any plug in.

I haven't given this a thorough testing... but this I think will tell you which computers just have the Altiris Agent installed and nothing else,

select * from vComputer where Guid not in 
(select distinct _ResourceGuid from Inv_AeX_AC_Client_Agent
where [Agent Name] not like 'Altiris Agent')

As an example of a query which will target for a specific plugin not being installed, this is an example for Inventory,

select * from vComputer where Guid not in 
(select distinct _ResourceGuid from Inv_AeX_AC_Client_Agent
where [Agent Name]  like 'Altiris Inventory Agent')

And this should help you figure out what all the agent names are,

select distinct [Agent Name] from Inv_AeX_AC_Client_Agent

 
Kind Regards,
Ian./

Someone else helped me out with a similar query a while back but I cannot find the initial post.  I have modified it to meet my needs.  Here is the query that you can use. 

SELECT --*

c.[Guid]

, v.[name]

, v.[OS Name]

, v.[OS Revision]

, v.[user]

, v.[IP Address]

, v.[Domain]

, v.[ismanaged]

FROM ( SELECT DISTINCT _ResourceGuid

FROM Inv_AeX_AC_Client_Agent

WHERE [Agent Class Guid] = '43043A36-0302-4375-8086-F7DD368DCADC' -- Windows Altiris Agent

AND [Product Version] LIKE '7.5.%' -- all AltirisAgent 7.5 and any SP

--AND [Build Number] < ???? -- only AltirisAgent 7.5

) AS a

JOIN vRM_Computer c ON c.[Guid] = a._ResourceGuid

join vcomputer v on v.[guid] = c.[guid]

-- JOIN Inv_AeX_AC_Identification i on i._ResourceGuid = c.[Guid]

LEFT JOIN ( SELECT _ResourceGuid

FROM Inv_AeX_AC_Client_Agent

WHERE [Agent Class Guid] = '11E36ED4-A571-4304-90AA-F0CD2366C335' -- Windows inventory Agent (use "select * from Inv_AeX_AC_Client_Agent" to determine which GUID you need to use. Replace this GUID for the agent you want to search on)

) AS s ON s._ResourceGuid = c.[Guid]

WHERE c.IsManaged = 1

AND v.[OS Name] not like '%server%'  --excluding servers

AND v.[OS Name] not like '%embed%'  --excluding embedded

-- AND ( i.[System Type] = 'Win32' OR i.[System Type] = 'Win64' )

AND s._ResourceGuid IS NULL

order by v.[name]

I hope this helps!  Sorry to whomever provided me with the base query to begin with...want to give you props!

Thanks!

Hi!

You can find similar queries from default reports or filters | SMP Console.

To see SQL Query of default report, you need to clone a default report ⇒ open default report and click "Edit". For example default report "Computers with no additional Plug-ins"

Thanks,

IP.

cbz9104,

many good solutions, here is another query you could try...

select

c.name as Computer
,c.[OS Name]
from vComputer c

-- include computers without the software Management solution agent

where Guid not in (
select _resourceguid from Inv_AeX_AC_Client_Agent
where [Agent Name] = 'Software Management Solution Agent' )
and c.IsManaged = 1

Check out this SQL Query: 
https://www-secure.symantec.com/connect/articles/symantec-management-platform-agent-version-build-numbers

It shows you the Agent und Plug-in Versions in one view. If a plug-in is not installed, the report says NUL.

Hi CBZ9014, you've not yet marked this thread as solved. Do you need further assistance here? If not, could you please mark as solved the solution/most helpful replies. 

Many thanks,
Ian./