/build/static/layout/Breadcrumb_cap_w.png
02/21/2017 716 views
Hi, 

I am trying to find which systems are not in compliance with our newly deployed Virus Protection. I do this by querying for a specific file on all systems, then creating a subselect off of that original query. My issue is that I the subselect doesn't return any results. I know for a fact that a few machines should be popping up, but nothing is there. First query brings back accurate results. Can anybody point me in the right direction here?  

First Query looking for the file:

select distinct SMS_R_System.Name, SMS_R_System.Client, SMS_R_System.ClientType, SMS_R_System.ClientVersion, SMS_G_System_SoftwareFile.FileName, SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "CylanceUi.exe"

Subselect query:

select distinct SMS_R_System.Name, SMS_R_System.Client, SMS_R_System.ClientType, SMS_R_System.ClientVersion, SMS_G_System_SoftwareFile.FileName, SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Name not in (select distinct SMS_R_System.Name, SMS_R_System.Client, SMS_R_System.ClientType, SMS_R_System.ClientVersion, SMS_G_System_SoftwareFile.FileName, SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "CylanceUi.exe")


Any help or pointings in the right direction would be very helpful. All other Subselect queries I have which use Add Remove programs work properly. 



0 Comments   [ + ] Show comments

Comments


All Answers

0
This is just a guess. What if you change the second query's WHERE clause as follows:

WHERE SMS_R_System.NAME NOT IN (
SELECT DISTINCT SMS_R_System.NAME
FROM SMS_R_System
INNER JOIN SMS_G_System_SoftwareFile ON SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
WHERE SMS_G_System_SoftwareFile.FileName = "CylanceUi.exe")

Answered 02/28/2017 by: JasonEgg
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share