Wednesday, December 15, 2010

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.

2 Comments:

Anonymous Anonymous said...

Hi José,
for me, this query does not work in sms 2003 with sql 2005.
please, can you help me ?
thanks in advance
bests regards

June 3, 2011 at 3:17 AM  
Blogger Jaison Jose said...

Where do you execute this? Run it on your SQL Management studio (query analyzer).

June 3, 2011 at 4:44 AM  

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home