none
New to SQL - Trying to create custom report query

    Question

  • SELECT     v_R_System.Name0, v_Update_ComplianceStatus.Status, v_UpdateInfo.BulletinID, v_UpdateInfo.ArticleID, v_UpdateInfo.Description, v_UpdateInfo.Title,
                          v_UpdateInfo.DatePosted
    FROM         v_R_System INNER JOIN
                          v_Update_ComplianceStatus ON v_R_System.ResourceID = v_Update_ComplianceStatus.ResourceID INNER JOIN
                          v_UpdateInfo ON v_Update_ComplianceStatus.CI_ID = v_UpdateInfo.CI_ID
    WHERE     (v_Update_ComplianceStatus.Status = 2) AND (v_UpdateInfo.DatePosted <= CONVERT(DATETIME, '2013-01-01 00:00:00', 102))

    Hi Guys,

    I have a query which shows me which updates are required to be installed but seem to be having issues which what it is showing for number of updates required.

    ie.  A pc is showing in the above query only 30 updates whereas if I check on machine via windows update it shows 90 updates as does another query I am using below

    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

    Any ideas why I am getting differing update numbers?

    Thanks


    Saturday, February 22, 2014 12:14 AM

Answers

  • Well one will show you all SU and one will show all SU since Jan 1 2013.

    http://www.enhansoft.com/

    • Marked as answer by NighthawkUK Wednesday, February 26, 2014 12:38 PM
    Saturday, February 22, 2014 3:49 AM