myITforum and Windows IT Pro Forums

 Creating a report that prompts for collection and gives a count of Microsoft installed stw

Author Message
smokin5s

  • Total Posts : 31
  • Scores: 2
  • Reward points : 10050
  • Joined: 12/7/2012
  • Status: offline
Creating a report that prompts for collection and gives a count of Microsoft installed stw Wednesday, February 06, 2013 1:58 PM (permalink)
0
I am trying to create an SCCM report that will prompt for a collection ID and then give results of installed software by count of Microsoft installed software under Add/Remove programs. (This is for Microsoft True-up)
 
I have something that seems to work except for a few things...
 
1.) The counts appear to be off
2.) It's displaying alot of irrelivant data (software that doesn't cost money or need true'd up Or showing every instance of Office installed IE Excel, Word, etc.... as well as showing Office Professional)
 
 
Below is my code... can someone please tell me what I'm doing wrong... I tried removing some of the software that I didn't want to see, but there's just too much to remove as well as the counts are way off
 
_______________________________________________
 
SELECT distinct DisplayName0, Count(*) AS 'Count', Publisher0, @CollID as CollectionID
FROM v_Add_Remove_Programs arp
JOIN v_FullCollectionMembership fcm on arp.ResourceID=fcm.ResourceID
WHERE  fcm.CollectionID = @CollID AND (Publisher0 LIKE 'Microsoft%') AND DisplayName0 NOT LIKE '%Hotfix%' AND DisplayName0 NOT LIKE '%Security Update%' AND DisplayName0 NOT LIKE '%Update for%' AND DisplayName0 NOT LIKE '%.NET%' AND DisplayName0 NOT LIKE '%Viewer%' AND DisplayName0 NOT LIKE '%Language Pack%'  AND DisplayName0 NOT LIKE '%Internet Explorer%' AND DisplayName0 NOT LIKE '%MSXML%' AND DisplayName0 NOT LIKE '%SDK%' AND DisplayName0 NOT LIKE '%C++%' AND DisplayName0 NOT LIKE '%Redistributable%' AND DisplayName0 NOT LIKE '%Search%' AND DisplayName0 NOT LIKE '%SMS%' AND DisplayName0 NOT LIKE '%Silverlight%' AND DisplayName0 NOT LIKE '%Live Meeting%' AND DisplayName0 NOT LIKE '%(KB%' AND DisplayName0 NOT LIKE '%Office Web%' AND DisplayName0 NOT LIKE '%Office %Proof%' AND DisplayName0 NOT LIKE '%Server %Proof%' AND DisplayName0 NOT LIKE '%Office %Shared%' AND DisplayName0 NOT LIKE '%Baseline Security Analyzer%' AND DisplayName0 NOT LIKE '%Compatibility Pack%' AND DisplayName0 NOT LIKE '%User State Migration Tools%'
GROUP BY DisplayName0, Publisher0
ORDER BY Publisher0
 
#1
    gjones

    • Total Posts : 2525
    • Scores: 142
    • Reward points : 148270
    • Joined: 6/5/2001
    • Location: Ottawa, Ontario, Canada
    • Status: offline
    Re:Creating a report that prompts for collection and gives a count of Microsoft installed Thursday, February 07, 2013 8:25 AM (permalink)
    0
    So there is no way around it without purchasing 3rd party software, You will always get a ton of irrelevant data. The only way to “fix it” is to purchase 3rd party software or continually update the query to remove each software title that you don’t want.
     
    As for the count issue, That again is based on how Office applies its service packs to itself. The best you can do, is change the query to count each PC once. Like this.
     
    http://smsug.ca/blogs/garth_jones/archive/2013/02/07/microsoft-true-up-report.aspx
     
    #2
      smokin5s

      • Total Posts : 31
      • Scores: 2
      • Reward points : 10050
      • Joined: 12/7/2012
      • Status: offline
      Re:Creating a report that prompts for collection and gives a count of Microsoft installed Thursday, February 07, 2013 9:43 AM (permalink)
      5
      Thank you for the quick reply, but my counts are still really high...
      Just to use as an Example, the report shows 166 " Microsoft Office 2010 Service Pack 1 (SP1)" Installed, but when I run the report listed below which just displays the results without doing a count I only have 10 Office 2010 installed and none of them show up like the title listed above... Here is my report that I'm running to cross reference...
      ___________________________________
      select distinct
        V_R_SYSTEM.Name0,
        v_Add_Remove_Programs.DisplayName0
      from
        v_R_System
        left join v_Add_Remove_Programs on v_Add_Remove_Programs.ResourceID=V_R_SYSTEM.ResourceID
        left join v_ClientCollectionMembers on v_ClientCollectionMembers.ResourceID=V_R_SYSTEM.ResourceID
      where
        v_ClientCollectionMembers.CollectionID= @Collection
        and v_Add_Remove_Programs.DisplayName0 LIKE @Software
      ____________________________________________________
       
      Am I missing something?
       
      #3
        gjones

        • Total Posts : 2525
        • Scores: 142
        • Reward points : 148270
        • Joined: 6/5/2001
        • Location: Ottawa, Ontario, Canada
        • Status: offline
        Re:Creating a report that prompts for collection and gives a count of Microsoft installed Thursday, February 07, 2013 9:49 AM (permalink)
        0
        opps I missed the word distinct. It should be like this.
         
        Count (distinct arp.ResourceID) AS 'Count',

         
        #4
          smokin5s

          • Total Posts : 31
          • Scores: 2
          • Reward points : 10050
          • Joined: 12/7/2012
          • Status: offline
          Re:Creating a report that prompts for collection and gives a count of Microsoft installed Thursday, February 07, 2013 9:56 AM (permalink)
          0
          YOU are my Hero!
           
          Don't forget to update your link you provided in your first post!
           
          One final question... should I go ahead and remove all of the redundant Excel, Outlook, etc... lists in there? They have different counts, but we never run any install other than the standard Office Professional install which would install all functionality, or at least be displayed as such.
           
          #5
            npherson

            • Total Posts : 399
            • Scores: 61
            • Reward points : 75240
            • Joined: 8/19/2009
            • Location: Saint Paul, Minnesota
            • Status: offline
            Re:Creating a report that prompts for collection and gives a count of Microsoft installed Thursday, February 07, 2013 10:49 AM (permalink)
            0
            >YOU are my Hero! 
             
            Garth is everyone's SQL hero...  he's got a very fashionable cape that he wears to MMS every year.
            See my blog posts on MyITforum:
            http://myitforum.com/myitforumwp/author/npherson
             
            #6
              gjones

              • Total Posts : 2525
              • Scores: 142
              • Reward points : 148270
              • Joined: 6/5/2001
              • Location: Ottawa, Ontario, Canada
              • Status: offline
              Re:Creating a report that prompts for collection and gives a count of Microsoft installed Thursday, February 07, 2013 11:09 AM (permalink)
              0
              lol... There are no pictures of that!!
               
              #7
                gjones

                • Total Posts : 2525
                • Scores: 142
                • Reward points : 148270
                • Joined: 6/5/2001
                • Location: Ottawa, Ontario, Canada
                • Status: offline
                Re:Creating a report that prompts for collection and gives a count of Microsoft installed Thursday, February 07, 2013 11:25 AM (permalink)
                0
                This is where the fun of asset management comes into play….
                 
                Who is you Asset Manager? Is it you?
                 
                Honestly, I would bother filtering out anything that I can’t guarantee is not licensable. This way I don’t accidently filter out something  that should have been filtered out.
                 WHERE  
                    fcm.CollectionID = @CollID 
                 AND arp.DisplayName0 not in(
                  'Microsoft Deployment Toolkit 2010 Update 1 (5.1.1642.01)',
                  'Microsoft Office SharePoint Designer 2007 Service Pack 3 (SP3)',
                  'VC Runtimes MSI',
                  'System Center Configuration Manager Help Updater',
                  'Windows Genuine Advantage v1.3.0254.0',
                  'D3DX10',
                  'Segoe UI',
                  'Bing Bar',
                  '6451A-1',
                  '6451A-2', 
                  '6451A-3', 
                  '6451A-4', 
                  '6451A-5',
                  'SQLIO',
                  'MSVCRT',
                  'MSVCRT_amd64',
                  'Software Update Deployment SuperFlow',
                  'IIS 7.5 Express',
                  'IIS Search Engine Optimization Toolkit 1.0'
                  )
                    AND (Publisher0 LIKE 'Microsoft%') 
                    AND DisplayName0 NOT LIKE '%Hotfix%' 
                    AND DisplayName0 NOT LIKE '%Security Update%' 
                 ...
                 

                 
                 
                 
                #8
                  smokin5s

                  • Total Posts : 31
                  • Scores: 2
                  • Reward points : 10050
                  • Joined: 12/7/2012
                  • Status: offline
                  Re:Creating a report that prompts for collection and gives a count of Microsoft installed Thursday, February 07, 2013 1:58 PM (permalink)
                  0
                  gjones


                  So there is no way around it without purchasing 3rd party software, You will always get a ton of irrelevant data. The only way to “fix it” is to purchase 3rd party software or continually update the query to remove each software title that you don’t want.

                  As for the count issue, That again is based on how Office applies its service packs to itself. The best you can do, is change the query to count each PC once. Like this.

                  http://smsug.ca/blogs/garth_jones/archive/2013/02/07/microsoft-true-up-report.aspx

                  I just spoke with my manager and he said he wouldn't be against purchasing a 3rd party app... What apps would be recommended?
                   
                  #9
                    gjones

                    • Total Posts : 2525
                    • Scores: 142
                    • Reward points : 148270
                    • Joined: 6/5/2001
                    • Location: Ottawa, Ontario, Canada
                    • Status: offline
                    Re:Creating a report that prompts for collection and gives a count of Microsoft installed Monday, February 11, 2013 11:02 AM (permalink)
                    0
                    There are a few companies out there that do this. But the three that I generally point people too are (list alphabetically)
                     
                    1E - Appclarity
                    Assetlabs - AssetCheck
                    BDNA - Technopedia Normalize
                     
                    #10
                      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-2014 ASPPlayground.NET Forum Version 3.9