SQL query not working

Garima Das 1,061 Reputation points
2022-11-15T16:20:46.66+00:00

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?

Microsoft Security | Intune | Configuration Manager | Other
Developer technologies | Transact-SQL
0 comments No comments
{count} votes

Accepted answer
  1. Garth 5,801 Reputation points
    2022-11-15T21:35:46.763+00:00

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

    2 people found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Ciarán Mac Lochlainn 6 Reputation points Microsoft Employee
    2022-11-15T18:23:19.96+00:00

    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!

    1 person found this answer helpful.
    0 comments No comments

  2. Rahul Jindal 10,911 Reputation points MVP
    2022-11-16T08:22:29.69+00:00

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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.