http://www.sqlservercentral.com/blogs/brian_kelley/2008/09/23/wsus-3-0-query-to-return-systems-that-haven-t-checked-in-within-the-week/

Printed 2014/09/02 07:11PM

WSUS 3.0 - Query to Return Systems That Haven't Checked in Within the Week

2008/09/23

I've been playing with WSUS lately and I've been less than satisfied with the reporting. Since the information I need to get at exists in a SQL Server database, I've been writing my own queries to get it out. Here's a basic query to get out what systems in a particular group haven't checked in within the last week. There are 3 basic tables which have to be put together: tbComputerTarget (contains the information on the computers), tbTargetGroup (contains the information on the groups), and tbTargetInTargetGroup (join table between the two). I have found cases where a system will have checked in to WSUS so that we see it's computer name, IP address, etc., but it doesn't actually report a status time (nor any status information), hence an OR for LastReportedStatusTime is NULL to include those systems, too.

 

DECLARE @GroupName nvarchar(512)
SET @GroupName = '<Group Name>'

SELECT
    FullDomainName
  , IPAddress
  , LastReportedStatusTime
FROM tbComputerTarget CT
 
JOIN tbTargetInTargetGroup TTG
   
ON CT.TargetID = TTG.TargetID
 
JOIN tbTargetGroup TG
   
ON TTG.TargetGroupID = TG.TargetGroupID
WHERE TG.Name = @GroupName
  
AND (DATEDIFF(dd, LastReportedStatusTime, GETDATE()) > 6
           
OR LastReportedStatusTime IS NULL)

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.