Query to find non sms clients with recent AD discovery

SELECT
[Name],
[Resourceid],
[Obsolete],
[Client],
[LastSWscan],
[LastHWscan],
[Last discovery run]
FROM
(SELECT
row_number() over (partition by a.Name0 order by e.AgentTime desc) as rownum,
a.Name0 as [Name],
a.resourceid as [Resourceid],
a.Obsolete0 as [Obsolete],
a.Client0 as [Client],
b.LastScandate AS [LastSWscan],
c.LastHWscan as [LastHWscan],
e.AgentTime as [Last discovery run]
FROM v_r_system AS a
LEFT OUTER JOIN v_GS_LastSoftwareScan AS b
ON a.resourceid = b.resourceid
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS AS c
ON a.resourceid = c.resourceid
LEFT OUTER JOIN v_AgentDiscoveries e
on e.resourceid=a.resourceid
WHERE a.Name0 IN (SELECT Name0
FROM v_r_system
GROUP BY Name0
HAVING COUNT(*) < 2) AND (c.LastHWscan is NULL and b.LastScandate is NULL and a.Client0 is NULL) AND e.AgentTime > getdate()-10
AND e.AgentName='SMS_AD_SYSTEM_DISCOVERY_AGENT')as dt
where rownum=1

Description: This query will get you all the resources which are having a recent Active directory system discovery date but do not have SMS client agents installed.