SCCM Report by Computer


  • We are using System Center 2007 R2 with SQL 2008.

    I am trying to create a report that lists patch compliance by machine. It would be similar in scope to the standard compliance 3 report Update list (per update). I have no background in SQL and my attempts so far have not been successful.

    I would like it to take in a collection and update list. I would like it to output something similar to this. Where missing = patches that are required in the update list that are not installed. Where Total is the total number of patches in the list. Compliant just needs to be a percent of the first 2. I would like this to happen for each computer in the collection.

    Computer Name          Missing           Total          % Compliant        Collection ID

    WKS01                         (4)              (174)             97.8%               ABC000014

    WKS02                         (8)              (174)             95.6%               ABC000014

    Any help would be appreciated.

    יום רביעי 23 פברואר 2011 18:58


  • Hi,

    Take a look at some of these examples. Here you will find compliance pr. computer, collection etc -

    Kent Agerlund | My blogs: and | Twitter @Agerlund | Linkedin: /kentagerlund
    יום רביעי 23 פברואר 2011 19:30
    מנחה דיון
  • Hi TowerTech,

    Try to go to your report properties --> Edit SQL Statement --> Prompts --> Add a new Prompt -->

       Name: UPDATELIST
       Prompt text: "Update List ID (Required)"
       [x] Check on "Provide a SQL statement"  and click on "Edit SQL Statement" at the same window. Put this text :

       if (@__filterwildcard = '')
          select distinct CI_UniqueID as UPDATELIST, Title as Title from v_AuthListInfo order by Title
         select distinct CI_UniqueID as UPDATELIST, Title as Title from v_AuthListInfo
           where ((CI_UniqueID like @__filterwildcard) or
               (Title like @__filterwildcard))
          order by Title

    Click Ok Three times then re-run the report.

    Good luck!

    יום שישי 25 פברואר 2011 17:55

כל התגובות