SCCM 2007 - Software Compliance Reports

New Member
  • Total Posts : 1
  • Scores: 0
  • Reward points: 5900
  • Joined: 2013/01/24 12:54:27
  • Status: offline
2013/01/24 13:58:29 (permalink)

SCCM 2007 - Software Compliance Reports

About 6 months ago I was tasked with improving Patch management at the company I work for.  Alongside improving processes I have been trying to build reports to show overall compliance against the patches we are pushing.  I have been working on queries for reporting patching for a while now and I have something that I cobbled together from the built in reporting from Microsoft and various queries I have found on the internet.   This report seems to work great and I wanted to share it.  Also since I have no SQL training or experience I am looking for any feedback on what could be done to improve its accuracy or speed.

SELECT     v_R_System.Name0, v_R_System.User_Name0, COUNT(v_Update_ComplianceStatusAll.Status) AS 'Required Updates',
FROM         v_Update_ComplianceStatusAll INNER JOIN
                      v_UpdateInfo ON v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID INNER JOIN
                      v_R_System ON v_Update_ComplianceStatusAll.ResourceID = v_R_System.ResourceID INNER JOIN
                      v_RA_System_SMSAssignedSites ON v_R_System.ResourceID = v_RA_System_SMSAssignedSites.ResourceID INNER JOIN
                      v_GS_LastSoftwareScan ON v_R_System.ResourceID = v_GS_LastSoftwareScan.ResourceID INNER JOIN
                      v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
WHERE       /*Update is required*/
            (v_Update_ComplianceStatusAll.Status = 2) AND
            /*Update is marked as Deployed in SCCM*/
            (v_UpdateInfo.IsDeployed = 1) AND
            /*Update is not on our list of updates we are not pushing to everyone.*/
            (v_UpdateInfo.ArticleID NOT IN ('2736233')) AND
            /*Update was released from Microsoft more than 30 days ago*/
            (DATEDIFF(dd, v_UpdateInfo.DatePosted, GETDATE()) > 30) AND
            /*Machine is not a server*/
            (v_R_System.Operating_System_Name_and0 NOT LIKE '%server%') AND
            /*Machine has a hardwarescan or Sofware scan within the last 30 days*/
            (DATEDIFF(dd, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) < 30 OR
                      DATEDIFF(dd, v_GS_LastSoftwareScan.LastScanDate, GETDATE()) < 30) AND
            /*Machine is in the collection of machines we deploy to*/
            (v_R_System.Name0 IN
                (SELECT Name FROM _RES_COLL_[CollectionID]))
GROUP BY v_R_System.Name0, v_R_System.User_Name0, v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0
ORDER BY 'Required Updates' DESC


0 Replies Related Threads

    Jump to:
    © 2018 APG vNext Commercial Version 5.5