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
968 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
[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
  • 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
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
 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Every system administrator encounters once in while in a problem where the solution seems to be a needle in haystack.  My needle was an anti-virus version causing problems with my Exchange server. I have an HP DL350 with Windows Server 2008 Stand…
This is my 3rd article on SCCM in recent weeks, the 1st (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/Windows_Server_2008/A_4466-A-beginners-guide-to-installing-SCCM2007-on-Windows-2008-R2-Server.html) dealing with installat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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