SQL query not working

Question

Hi experts,

I need to find out devices other than windows 10 that are there in the organization. For this I have created a SCCM SQL query as below:

select Name0, vrs.AADTenantID, User_Name0, Full_Domain_Name0, AD_Site_Name0, Operating_System_Name_and0, LastHW, LastActiveTime, Last_Logon_timestamp0, Distinguished_Name0 from v_R_System vrs
join v_CH_ClientSummary ch on ch.ResourceID=vrs.ResourceID
where Operating_System_Name_and0 not like (select Name0, vrs.AAdTenantID, User_Name0, Full_Domain_Name0, AD_Site_Name0, Operating_System_Name_and0, LastHW, LastActiveTime, Last_Logon_timestamp0, Distinguished_Name0 from v_R_System vrs
join v_CH_ClientSummary ch on ch.ResourceID=vrs.ResourceID
where Operating_System_Name_and0 like '%workstation 10%')

This query is throwing an error but I am unable to find out the resolution of this.

Can someone please help me with the solution?

Answer 1

you can only have on column with your subselect query and it should be resourceid.
https://askgarth.com/blog/subselect-query-for-reports-sql/

Answer 2

Hi GarimaDas

Part of this query is repeated inside a subquery and it's not clear why it's structured that way. The error message isn't included here, but it may be because you're trying to compare a single field against multiple return fields from the subquery. That usually gives an error i.e. cannot return more than one value.

You could try changing line 4 as below, and deleting the rest of the query assuming it's not needed.

where Operating_System_Name_and0 not like '%workstation 10%'

Hope that's helpful!

Answer 3

Looking at your post Garima, maybe creating a dynamic collection will be easier. Use the query designer to achieve this.