Subselect Queries not giving any results
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
Answers (1)
Please log in to answer
Posted by:
JasonEgg
7 years ago
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")