myITforum and Windows IT Pro Forums

 SCCM 2007 - Software Compliance Reports

Author Message

  • Total Posts : 1
  • Scores: 0
  • Reward points : 5900
  • Joined: 1/24/2013
  • Status: offline
SCCM 2007 - Software Compliance Reports Thursday, January 24, 2013 2:58 PM (permalink)
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

    Online Bookmarks Sharing: Share/Bookmark

    Jump to:

    Current active users

    There are 0 members and 2 guests.

    Icon Legend and Permission

    • New Messages
    • No New Messages
    • Hot Topic w/ New Messages
    • Hot Topic w/o New Messages
    • Locked w/ New Messages
    • Locked w/o New Messages
    • Read Message
    • Post New Thread
    • Reply to message
    • Post New Poll
    • Submit Vote
    • Post reward post
    • Delete my own posts
    • Delete my own threads
    • Rate post

    2000-2018 ASPPlayground.NET Forum Version 3.9