Machine without AntiVirus

Duchemin, Dominique 2,006 Reputation points
2022-11-21T17:42:36.13+00:00

Hello,

I am trying to create a query of machines which do not have Sophos and do not have System Center Endpoint Protection:

Select Name0 from v_R_System
inner join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
where CollectionID = 'UCP00020'
and NOT EXISTS
(
Select
/* ISS - All Servers /
Name0 as [Server Name],
ProductName0 as [Product Name],
Publisher0 as [Publisher],
MAX(ProductVersion0) as [Version]
from v_GS_INSTALLED_SOFTWARE v_GS_INSTALLED_SOFTWARE
join v_R_System on v_R_System.ResourceID = v_GS_INSTALLED_SOFTWARE.ResourceID
where v_GS_INSTALLED_SOFTWARE.ProductName0 IS NOT NULL
and v_GS_INSTALLED_SOFTWARE.ResourceID IN
(
select distinct v_R_System.ResourceID
from v_R_System
inner join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
where CollectionID = 'UCP00020')
and Publisher0 = 'Sophos Limited'
and ProductName0 = 'Sophos Anti-Virus'
Group By
Name0,
ProductName0,
ProductVersion0,
Publisher0
UNION
Select
/ ISS - All Servers */
Name0 as [Server Name],
ProductName0 as [Product Name],
Publisher0 as [Publisher],
MAX(ProductVersion0) as [Version]
from v_GS_INSTALLED_SOFTWARE v_GS_INSTALLED_SOFTWARE
join v_R_System on v_R_System.ResourceID = v_GS_INSTALLED_SOFTWARE.ResourceID
where v_GS_INSTALLED_SOFTWARE.ProductName0 IS NOT NULL
and v_GS_INSTALLED_SOFTWARE.ResourceID IN
(
select distinct v_R_System.ResourceID
from v_R_System
inner join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
where CollectionID = 'UCP00020')
and Publisher0 like '%Microsoft Corporation%'
and ProductName0 Like '%System Center Endpoint Protection%'
Group By
Name0,
ProductName0,
ProductVersion0,
Publisher0
)

--------------------------------------------------------------------------------------------------------------------------

If I do

Select Name0 from v_R_System
inner join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
where CollectionID = 'UCP00020'

I have 1,628 resources...

If I do

Select
/* ISS - All Servers /
Name0 as [Server Name],
ProductName0 as [Product Name],
Publisher0 as [Publisher],
MAX(ProductVersion0) as [Version]
from v_GS_INSTALLED_SOFTWARE v_GS_INSTALLED_SOFTWARE
join v_R_System on v_R_System.ResourceID = v_GS_INSTALLED_SOFTWARE.ResourceID
where v_GS_INSTALLED_SOFTWARE.ProductName0 IS NOT NULL
and v_GS_INSTALLED_SOFTWARE.ResourceID IN
(
select distinct v_R_System.ResourceID
from v_R_System
inner join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
where CollectionID = 'UCP00020')
and Publisher0 = 'Sophos Limited'
and ProductName0 = 'Sophos Anti-Virus'
Group By
Name0,
ProductName0,
ProductVersion0,
Publisher0
UNION
Select
/ ISS - All Servers */
Name0 as [Server Name],
ProductName0 as [Product Name],
Publisher0 as [Publisher],
MAX(ProductVersion0) as [Version]
from v_GS_INSTALLED_SOFTWARE v_GS_INSTALLED_SOFTWARE
join v_R_System on v_R_System.ResourceID = v_GS_INSTALLED_SOFTWARE.ResourceID
where v_GS_INSTALLED_SOFTWARE.ProductName0 IS NOT NULL
and v_GS_INSTALLED_SOFTWARE.ResourceID IN
(
select distinct v_R_System.ResourceID
from v_R_System
inner join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
where CollectionID = 'UCP00020')
and Publisher0 like '%Microsoft Corporation%'
and ProductName0 Like '%System Center Endpoint Protection%'
Group By
Name0,
ProductName0,
ProductVersion0,
Publisher0

I have 1,387 resources

but when I ran the complete SQL Statement I obtain 0 resource and no error...
I was expecting 1628-1387 = 241 resources...

What is wrong is the complete statement?

Thanks,
Dom

Microsoft Configuration Manager Application
Microsoft Configuration Manager Application
Microsoft Configuration Manager: An integrated solution for for managing large groups of personal computers and servers.Application: A computer program designed to carry out a specific task other than one relating to the operation of the computer itself, typically to be used by end users.
458 questions
Microsoft Configuration Manager
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. TrudaZeng-MSFT 766 Reputation points
    2022-11-22T06:37:58.337+00:00

    Hi @dom ,

    Thank you for posting in Microsoft Q&A forum. And we are glad to hear that you have found a solution. If there's anything else we can help in the future, feel free to post in Q&A to discuss together.

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments
  2. Duchemin, Dominique 2,006 Reputation points
    2022-11-21T21:18:40.79+00:00

    Hello,

    Not sure it is the best one but I found this working...
    Select sys1.Name0 from v_R_System sys1
    inner join v_FullCollectionMembership col1 on sys1.ResourceID = col1.ResourceID
    where
    Col1.CollectionID = 'UCP00020'
    AND
    sys1.ResourceID NOT IN
    (Select Distinct
    /* ISS - All Servers */
    v_R_System.ResourceID
    from v_GS_INSTALLED_SOFTWARE
    join v_R_System on v_R_System.ResourceID = v_GS_INSTALLED_SOFTWARE.ResourceID
    where v_GS_INSTALLED_SOFTWARE.ProductName0 IS NOT NULL
    and v_GS_INSTALLED_SOFTWARE.ResourceID IN
    (
    select distinct v_R_System.ResourceID
    from v_R_System
    inner join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
    where CollectionID = 'UCP00020')
    and Publisher0 = 'Sophos Limited'
    and ProductName0 = 'Sophos Anti-Virus'

    UNION
    Select
    /* ISS - All Servers */
    v_R_System.ResourceID
    from v_GS_INSTALLED_SOFTWARE v_GS_INSTALLED_SOFTWARE
    join v_R_System on v_R_System.ResourceID = v_GS_INSTALLED_SOFTWARE.ResourceID
    where v_GS_INSTALLED_SOFTWARE.ProductName0 IS NOT NULL
    and v_GS_INSTALLED_SOFTWARE.ResourceID IN
    (
    select distinct v_R_System.ResourceID
    from v_R_System
    inner join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
    where CollectionID = 'UCP00020')
    and Publisher0 Like '%Microsoft Corporation%'
    and ProductName0 Like '%System Center Endpoint Protection%'
    )

    Thanks,
    Dom

    0 comments