How to filter an SSRS query by the number of results

Hello,

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,

arp.DisplayName0
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 %'
tneubauertocgAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
regmigrantConnect With a Mentor Commented:
you can try to add

sys.Netbios_Name0, sys.User_Name0, sys.Operating_System_Name_and0,

to the group by clause:
GROUP BY DisplayName0, sys.Netbios_Name0, sys.User_Name0, sys.Operating_System_Name_and0

However this number of groupings might mean you never hit the 25 limit because (for example) imagine you have 25 copies of WinWord but they are split over 3 servers with different NetBIOS names - they won't appear because each result is less than 25.


This might give you a better result:-
Select sys.Netbios_Name0, sys.User_Name0, sys.Operating_System_Name_and0,

arp.DisplayName0
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 %'
and displayName0 in
 (select a.displayname0 from v_Add_Remove_Programs a
  group by a.displayname0
  having count(a.displayname0) < 25)

This makes an assumption that v_add_remove_programs is a single table containing a list of all installed applications across the estate
0
 
regmigrantCommented:
without having a better understanding of the number of servers being polled and the locations its a bit difficult to be sure but you are looking for something along the following lines-

....
and DisplayName0 not like '% MUI %'
GROUP BY DisplayName
HAVING COUNT(DisplayName)>25
0
 
tneubauertocgAuthor Commented:
It is pulling the information from our master SCCM SQL database which contains the information for 5000+ clients.

I receive the following error when adding the statements to the query:
GROUP BY DisplayName0
Having COUNT(DisplayName0)>25

--error
Column 'v_R_System.Netbios_Name0' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

any ideas?
0
 
tneubauertocgAuthor Commented:
Perfect - thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.