There are a couple thing that you need to look at.
First subselect queries can only have one column. Secondly, you likely need an Left outer join on the Reg key within the main query. Third, you use always use ResourceID for your "not in" Fourthly, Understand that the IP address that you are displaying is all KNOWN IP address for that system, not just the current IP address. Fifthly, Starting with CM12 v_r_system will/can include system that have been deleted. user v_R_system_valid instead. I edited the query by hand, I'm not need my desk so I couldn't test this query, so watch out for typos. SELECT DISTINCT sys.NetBIOS_name0 as 'PC Name', ip.IP_Addresses0 as 'IP address', sys.User_Name0 as 'User Name', REG.Value0 as 'Reg Value' FROM dbo.v_r_system_Valid sys join dbo.v_RA_System_IPAddresses ip on sys.ResourceID = ip.ResourceID= Left outer join dbo.v_GS_Registry_Values0 REG on sys.ResourceID = REG.ResourceID where sys.ResourceID not in ( SELECT sys.ResourceID FROM dbo.v_r_system_valid sys join CM_SC1.dbo.v_RA_System_IPAddresses ip on sys.ResourceID = ip.ResourceID join CM_SC1.dbo.v_GS_Registry_Values0 REG on sys.ResourceID = REG.ResourceID where reg.Value0 like '%Something%' and ip.IP_Addresses0 like '10.0%') ________________________________ From: [email protected] <[email protected]> on behalf of David McSpadden <[email protected]> Sent: Thursday, July 21, 2016 9:17:46 AM To: Patch Management Mailing List ([email protected]); [email protected] Subject: [mssms] Query help I have this query: SELECT DISTINCT sys1.[NetBIOS_name0] , ip.IP_Addresses0, sys1.User_Name0, REG.Value0 FROM [CM_SC1].[dbo].[v_r_system] sys1 join CM_SC1.dbo.v_RA_System_IPAddresses ip on ip.ResourceID=sys1.ResourceID join CM_SC1.dbo.v_GS_Registry_Values0 REG on REG.ResourceID=sys1.ResourceID where reg.Value0 like '%Something%' and ip.IP_Addresses0 like '10.0%' And it returns all pc that have the registry value of %something%. I would like to actually get a report of all other workstations that do not have this registry value of %something%. So I thought making the above query a sub query I would get my results. I however get 0 results: SELECT DISTINCT sys1.[NetBIOS_name0] , ip.IP_Addresses0, sys1.User_Name0, REG.Value0 FROM [CM_SC1].[dbo].[v_r_system] sys1 join CM_SC1.dbo.v_RA_System_IPAddresses ip on ip.ResourceID=sys1.ResourceID join CM_SC1.dbo.v_GS_Registry_Values0 REG on REG.ResourceID=sys1.ResourceID where ip.IP_Addresses0 not in ( SELECT DISTINCT sys1.[NetBIOS_name0] , ip.IP_Addresses0, sys1.User_Name0, REG.Value0 FROM [CM_SC1].[dbo].[v_r_system] sys1 join CM_SC1.dbo.v_RA_System_IPAddresses ip on ip.ResourceID=sys1.ResourceID join CM_SC1.dbo.v_GS_Registry_Values0 REG on REG.ResourceID=sys1.ResourceID where reg.Value0 like '%Something%' and ip.IP_Addresses0 like '10.0%') David McSpadden System Administrator Indiana Members Credit Union P: 317.554.8190 [Description: Description: imcu email icon]<http://imcu.com/> [Description: Description: facebook email icon] <https://www.facebook.com/IndianaMembersCU> [Description: Description: twitter email icon] <https://twitter.com/IndMembersCU> [Description: Description: email logo] [http://www.amuletsolutions.com/images/mcp.gif]<http://www.google.com/url?sa=i&rct=j&q=&esrc=s&source=images&cd=&cad=rja&uact=8&ved=0ahUKEwjFztf-tePJAhXK5iYKHcPtAxEQjRwIBw&url=http://www.amuletsolutions.com/awards.aspx&bvm=bv.110151844,d.amc&psig=AFQjCNHkrx8CednTEOOq4zUxYyrRUGzUsg&ust=1450459757284499> This e-mail and any files transmitted with it are property of Indiana Members Credit Union, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this email is strictly prohibited. Please consider the environment before printing this email.
