Solved

SCCM Report to return total numer of machines in the site, and total number of Adobe 9 installs

Posted on 2011-09-29
9
963 Views
Last Modified: 2013-11-21
Hello,

I would like to generate a report as follows.  Display the Domain Name with the Total number of SCCM clients from the site, then underneath, the Total number of Adobe 9 installs.

It should look like this for example:

----------------------------------------------------
Domain PROD1

Total SCCM Clients =

Total Adobe 9 installed =
----------------------------------------------------

Thanks for your help!
0
Comment
Question by:windows7expert
  • 5
  • 3
9 Comments
 
LVL 9

Expert Comment

by:Scottyworld
ID: 36864755
There's possibly a more efficient way of doing it, but this does the trick.
You will need to customise the 'Adobe Acrobat 9' bit to match what appears in the Add/Remove programs list
This also works with multiple domains

 
SELECT sys.Resource_Domain_OR_Workgr0 as [Domain], count(Distinct sys.ResourceID) as [Total SCCM CLients], arp.DisplayName0 as [Product],Count(Distinct arp.ResourceID) AS [Product count]
FROM
v_R_System as sys,
v_Add_Remove_Programs as arp
WHERE sys.Operating_System_Name_and0 LIKE '%workstation%' AND sys.Resource_Domain_OR_Workgr0 NOT LIKE '' AND arp.DisplayName0 LIKE 'Adobe Acrobat 9%'
GROUP BY sys.Resource_Domain_OR_Workgr0, arp.DisplayName0

Open in new window

0
 
LVL 9

Expert Comment

by:Scottyworld
ID: 36867968
Marginally better - this works a lot faster......
 
SELECT Resource_Domain_OR_Workgr0 as [Domain], count(*) as [Total SCCM CLients], (
	SELECT Count(Distinct arp.ResourceID) AS 'Count'
FROM v_Add_Remove_Programs as arp
WHERE arp.DisplayName0 LIKE 'Adobe Acrobat 9%') as [count]
FROM
v_R_System
WHERE Operating_System_Name_and0 LIKE '%workstation%' AND Resource_Domain_OR_Workgr0 NOT LIKE ''
GROUP BY Resource_Domain_OR_Workgr0

Open in new window

0
 

Author Comment

by:windows7expert
ID: 36893707
Hi Scotty,

I tried it and it comes out like this:

User Domain                 Total SCCM Clients                       Count
PROD1                          1                                                    4258



The domain is right, # of SCCM clients is not coming in correctly (it should be around 5500), the count of our Adobe 9 products is correct.  I'm fooling around with your query to try and get this correct.  Thanks.
0
 
LVL 9

Expert Comment

by:Scottyworld
ID: 36894610
Hmmm, in the second query, try substituting the Count(*) for :
count(distinct v_r_system.ResourceID)

I won't be able to test anything until Monday but will take another look then. Do both of my above queries have the same issue on your system?
0
The problems with reply email signatures

Do you wish that you could place an email signature under a reply? Well, unfortunately, you can't. That great Exchange/Office 365 signature you've created will just appear at the bottom of an email chain. What a pain! Is there really no way to solve this? Well, there might be...

 
LVL 9

Expert Comment

by:Scottyworld
ID: 36901014
I'm guessing it could be the WHERE clause thats causing the issue. Run the following queries across your database and see what the results are:

select * from v_r_system
-- This should produce a list of your 5500 clients (but will also include servers etc)

select distinct Operating_System_Name_and0 from v_r_system
-- This will show all the different OS's running, which is what the WHERE clause is filtering on.

Let me know how you get on

0
 

Author Comment

by:windows7expert
ID: 36905312
Hi Scotty, this is 95% working at this point :)

I'm using you're first query:

SELECT sys.Resource_Domain_OR_Workgr0 as [Domain], count(Distinct sys.ResourceID) as [Total SCCM CLients], arp.DisplayName0 as [Product],Count(Distinct arp.ResourceID) AS [Adobe 9 Count]

FROM

v_R_System as sys,

v_Add_Remove_Programs as arp

WHERE sys.Resource_Domain_OR_Workgr0 LIKE 'PROD1%' AND arp.DisplayName0 LIKE 'Adobe 9%'

GROUP BY sys.Resource_Domain_OR_Workgr0, arp.DisplayName0


This returns the following output:

User Domain                 Total SCCM Clients                       Count
PROD1                          1268                                              4258



The Count of 4258 is accurate Forest-wide (we also have PROD2, PROD3 domains, etc).  Is there any way to get the accurate Count within 1 domain?  PROD1 in my case.  Each of our domains correspond to a Config Mgr Primary site.
0
 
LVL 9

Accepted Solution

by:
Scottyworld earned 500 total points
ID: 36906723
Basically we need to only count arp records that have a domain of PROD1. To do this we need to join arp with sys and only count the records that match with the PROD1 domain. Its much easier using my second query as the join needs to be in the sub query select statement.
Try the following code :
 
SELECT Resource_Domain_OR_Workgr0 as [User Domain], count(*) as [Total SCCM Clients], (
	SELECT Count(Distinct arp.ResourceID) AS 'Count'
	FROM v_Add_Remove_Programs as arp
	LEFT OUTER JOIN v_r_system as sys on sys.ResourceID = arp.ResourceID
	WHERE arp.DisplayName0 LIKE 'Adobe 9%' and sys.Resource_Domain_OR_Workgr0 = 'PROD1') as [Software Count]
FROM
v_R_System
WHERE Resource_Domain_OR_Workgr0 LIKE 'PROD1'
GROUP BY Resource_Domain_OR_Workgr0

Open in new window

0
 
LVL 7

Expert Comment

by:George Simos
ID: 36906882
Why don't you use your site instead the domain? as you said above each domain is a site by itself so why not trying substituting the domain with the site code?
0
 

Author Closing Comment

by:windows7expert
ID: 36910056
Scotty,

Thats exactly what I needed!  I actually added an INNER JOIN to your Query #1, and almost had it.... but the numbers still didn't match.  Looked like the Parentheses did the trick.

Thanks!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SCCM 2012 Report 8 130
Fully delete GPO 2 50
Windows Server mistakenly registering as KMS Host in DNS 10 39
Best way to configure an old server? 13 114
Some time ago I faced the need to use a uniform folder structure that spanned across numerous sites of an enterprise to be used as a common repository for the Software packages of the Configuration Manager 2007 infrastructure. Because the procedu…
Welcome to my series of short tips on migrations. Whilst based on Microsoft migrations the same principles can be applied to any type of migration. My first tip is around source server preparation. No migration is an easy migration, there is a…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
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, fr…

920 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

16 Experts available now in Live!

Get 1:1 Help Now