Solved

How to filter an SSRS query by the number of results

Posted on 2014-02-17
4
322 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

689 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