Link to home
Start Free TrialLog in
Avatar of Andrew Millard
Andrew Millard

asked on

SCCM Report Builder - Computers without a specific software and version installed?

Hello,

I need to create a report that specifically shows all the devices, and in particular all the servers, that do not have Symantec Endpoint Protection version 12.* installed.

Managed to create a collection using the following syntax which I think is working:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Symantec Endpoint Protection" and SMS_G_System_OPERATING_SYSTEM.Name like "%Server%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version not like "12.%"

However, i'm going round in circles with the sql2016 report builder and just can't seem to get what I need.

Any help appreciated?

Thanks

Andrew
Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

Dear Andrew,
Your post is not enough specific. If you think that your query runs then test it first in SQL Server Management Studio and then try to implement in your report.
What is your problem with the report?
Please be more specific!
I do not have SSMS in front of me to test it out, but the following may work. If you run into issues, please provide additional detail  and also some sample data to test.

Basically, I am trying to find an exclusive set of Resources (SMS_R_System) that do not have a single entry in (SMS_G_System_ADD_REMOVE_PROGRAMS) of type "Symantec EndPoint Protection" v12.

SELECT SMS_R_SYSTEM.ResourceID,
       SMS_R_SYSTEM.ResourceType,
       SMS_R_SYSTEM.Name,
       SMS_R_SYSTEM.SMSUniqueIdentifier,
       SMS_R_SYSTEM.ResourceDomainORWorkgroup,
       SMS_R_SYSTEM.Client
FROM SMS_R_System 
INNER JOIN SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId 
WHERE SMS_G_System_OPERATING_SYSTEM.Name like "%Server%" 
  AND NOT EXISTS (SELECT DISTINCT ResourceID
                  FROM  SMS_G_System_ADD_REMOVE_PROGRAMS
                  WHERE SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Symantec Endpoint Protection" 
                    AND SMS_G_System_ADD_REMOVE_PROGRAMS.Version LIKE "12.%"
                    AND SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId 
                 )

Open in new window

This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.