Solved

How to filter an SSRS query by the number of results

Posted on 2014-02-17
4
315 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Left Join with Tuple returning more rows 10 90
sql query to reportserver  table error 3 40
sql query help 4 45
How do I refer to a session variable in a query? 4 24
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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now