Download the SQL Query
select
      CS.Name0,
      CS.UserName0,
case
when (sum(case when UCS.status=2 then 1 else 0 end))>0 then (‘Needs ‘+(cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ‘ Patches’))
else ‘Good Client’
end as ‘Status’,
      ws.lasthwscan as ‘Last HW scan’,
      FCM.collectionID
from
      v_UpdateComplianceStatus UCS
left outer join dbo.v_GS_COMPUTER_SYSTEM  CS on CS.ResourceID = UCS.ResourceID
join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID
join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName=’UpdateClassification’
left join v_gs_workstation_status ws on ws.resourceid=CS.resourceid
left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid
Where
      UCS.Status = ‘2’
and FCM.collectionid = ‘SMS00001’
Group by
      CS.Name0,
      CS.UserName0,
      ws.lasthwscan,
      FCM.collectionID
Order by
      CS.Name0,
      CS.UserName0,
      ws.lasthwscan,
      FCM.collectionID
output ::
Name0 UserName0 Status Last HW scan collectionID
MACHINE1 CONTOSO\USER11 Needs 43 Patches 7/1/2014 9:47 SMS00001
MACHINE2 CONTOSO\USER2 Needs 43 Patches 6/30/2014 14:16 SMS00001
MACHINE3 CONTOSO\USER3 Needs 61 Patches 7/1/2014 12:34 SMS00001
MACHINE4 NULL Needs 23 Patches 7/2/2014 12:27 SMS00001
MACHINE5 CONTOSO\USER5 Needs 36 Patches 7/1/2014 12:08 SMS00001
MACHINE6 NULL Needs 51 Patches 6/5/2014 20:05 SMS00001
MACHINE7 CONTOSO\USER8 Needs 30 Patches 7/2/2014 12:54 SMS00001
MACHINE8 NULL Needs 32 Patches 6/27/2014 8:28 SMS00001
MACHINE9 CONTOSO\USER9 Needs 43 Patches 6/19/2014 9:15 SMS00001
MACHINE10 CONTOSO\USER12 Needs 10 Patches 6/30/2014 9:50 SMS00001

Advertisements

Join the conversation! 2 Comments

  1. Line 9 FCM.collectionID–, what is the logic here? what is after ID?

    Like

    Reply
  2. that has been corrected, it was a to ignore the ‘,’ at the end.

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Category

Uncategorized

Tags

, , , , , ,