How to filter an SSRS query by the number of results
Posted on 2014-02-17
I have been tasked with pulling information from our SCCM database on all applications installed in the environment. I have a query to do this but I would like to filter it further by limiting the results to only those applications with 25 or more instances.
I am using the below query to pull the ARP table entries and eliminating a variety of known items that I don't care about - mainly security patches.
I only need to see apps with 25 or more installations. Is there a statement I can add to the below query to accomplish this?
I am not a SQL expert by any means so any assistance would be greatly appreciated.
--------- SQL Query ----------
Select sys.Netbios_Name0, sys.User_Name0, sys.Operating_System_Name_and0,
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
where Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 6%'
and DisplayName0 not like 'Security Update %'
and DisplayName0 not like 'Update for Windows %'
and DisplayName0 not like 'Hotfix for %'
and DisplayName0 not like '% Security Update %'
and DisplayName0 not like '%(KB%'
and DisplayName0 not like '% MUI %'