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
961 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The question has been asked on multiple occasions as to how best to do printing in a remote desktop or terminal services environment.   It seems that this particular question has plagued several people and most especially as Terminal Services, as…
Know what services you can and cannot, should and should not combine on your server.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

21 Experts available now in Live!

Get 1:1 Help Now