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,SM S_R_SYSTEM .ResourceT ype,SMS_R_ SYSTEM.Nam e,SMS_R_SY STEM.SMSUn iqueIdenti fier,SMS_R _SYSTEM.Re sourceDoma inORWorkgr oup,SMS_R_ SYSTEM.Cli ent from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PR OGRAMS on SMS_G_System_ADD_REMOVE_PR OGRAMS.Res ourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYS TEM on SMS_G_System_OPERATING_SYS TEM.Resour ceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PR OGRAMS.Dis playName not like "Symantec Endpoint Protection" and SMS_G_System_OPERATING_SYS TEM.Name like "%Server%" and SMS_G_System_ADD_REMOVE_PR OGRAMS.Ver sion 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
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,SM
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
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_P ROGRAMS) of type "Symantec EndPoint Protection" v12.
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_P
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
)
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers 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.
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!