Solved

How to filter an SSRS query by the number of results

Posted on 2014-02-17
4
316 Views
Last Modified: 2014-02-17
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
Comment
Question by:tneubauertocg
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39865423
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
 

Author Comment

by:tneubauertocg
ID: 39865451
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
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 39865690
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
 

Author Closing Comment

by:tneubauertocg
ID: 39865705
Perfect - thank you very much!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question