[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

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 %'
0
tneubauertocg
Asked:
tneubauertocg
  • 2
  • 2
1 Solution
 
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
 
regmigrantCommented:
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
 
tneubauertocgAuthor Commented:
Perfect - thank you very much!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now