?
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
Medium Priority
?
979 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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

1. Boot PC and press F10, select storage options and change the compatibility from “AHCI” to “IDE”, save and exit 2. Boot PC and press F12 3. Upon PXE display of searching for DHCP server, press Pause break to obtain MAC address 3. Open Configu…
Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
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 anti-spam), the admin…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

862 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