Jump to content


OC48

Established Members
  • Posts

    5
  • Joined

  • Last visited

OC48's Achievements

Newbie

Newbie (1/14)

0

Reputation

  1. Kakemonstre, Thank you very much, that was exactly what I needed! And thank you too for adding the edited query. The report is now limited to the collection I specified.
  2. I have a working SQL query courtesy of Eswar Koneti (https://gallery.technet.microsoft.com/office/SCCM-Configmgr-2012-SSRS-c482cca2/). Due to being new to SQL/SSRS custom report building, I am struggling with trying to limit the scope of these results to a specific collection. What I would like to do is add a statement in the query to limit the results to a specific collection ID(s). I am not looking to make a drop down of collections in the report. Here is what I have so far: SELECT COUNT (Total.Name0) AS 'Total Clients', COUNT (office2003.Name0) AS 'Office 2003', COUNT (Office2007.Name0) AS 'Office 2007' , COUNT (Office2010.Name0) AS 'Office 2010', COUNT (Office2013.Name0) AS 'Office 2013', COUNT (Office2016.Name0) AS 'Office 2016' FROM ( SELECT vr.Name0 FROM v_R_System vr WHERE vr.Client0=1 AND vr.Operating_System_Name_and0 NOT LIKE '%server%') AS Total LEFT JOIN ( SELECT DISTINCT sys.Name0 , displayname00 AS OfficeEdition , version00 AS OfficeVersion FROM system_disc sys LEFT JOIN add_remove_programs_data arp ON sys.itemkey = arp.machineid WHERE(displayname00 ='Microsoft Office Basic Edition 2003' OR displayname00 ='Microsoft Office Excel 2003' OR displayname00 ='Microsoft Office Outlook 2003' OR displayname00='Microsoft Office Personal Edition 2003' OR displayname00 ='Microsoft Office Professional Edition 2003' OR displayname00='Microsoft Office Small Business Edition 2003' OR displayname00='Microsoft Office Standard Edition 2003') AND client0 = 1) AS office2003 ON Total.Name0 =office2003.Name0 LEFT JOIN ( SELECT DISTINCT sys.Name0 , displayname00 AS OfficeEdition , version00 AS OfficeVersion FROM system_disc sys LEFT JOIN add_remove_programs_data arp ON sys.itemkey =arp.machineid WHERE(displayname00 LIKE 'Microsoft Office Personal 2007' OR displayname00 LIKE 'Microsoft Office Professional 2007' OR displayname00 LIKE 'Microsoft Office Professional 2007 Trial' OR displayname00 LIKE 'Microsoft Office Professional Hybrid 2007' OR displayname00 LIKE 'Microsoft Office Professional Plus 2007' OR displayname00 LIKE 'Microsoft Office Professional Plus 2007 (Beta)' OR displayname00 LIKE 'Microsoft Office Standard 2007' OR displayname00 LIKE 'Microsoft Office Standard 2007 Trial' OR displayname00 LIKE 'Microsoft Office Ultimate 2007' OR displayname00 LIKE 'Microsoft Office Enterprise 2007' OR displayname00 LIKE 'Microsoft Office Ultimate 2007' OR displayname00 LIKE 'Microsoft Office Ultimate 2007' ) AND sys.client0 = 1) AS Office2007 ON Total.Name0 =office2007.Name0 LEFT JOIN ( SELECT DISTINCT sys.Name0 , displayname00 AS OfficeEdition , version00 AS OfficeVersion FROM system_disc sys LEFT JOIN add_remove_programs_data arp ON sys.itemkey =arp.machineid WHERE(displayname00 LIKE 'Microsoft Office 2010' OR displayname00 LIKE 'Microsoft Office Professional Plus 2010' OR displayname00 LIKE 'Microsoft Office Standard 2010' OR displayname00 LIKE 'Microsoft Office Professional 2010' OR displayname00 LIKE 'Microsoft Office Home and Student 2010' OR displayname00 LIKE 'Microsoft Office Home and Business 2010' OR displayname00 LIKE 'Microsoft Office Professional Plus 2010 (Beta)' OR displayname00 LIKE 'Microsoft Office Starter 2010 - English') AND sys.client0 = 1) AS office2010 ON Total.Name0 =office2010.Name0 LEFT JOIN ( SELECT DISTINCT sys.Name0 , displayname00 AS OfficeEdition , version00 AS OfficeVersion FROM system_disc sys LEFT JOIN add_remove_programs_data arp ON sys.itemkey=arp.machineid WHERE(displayname00 LIKE 'Microsoft Office 2013' OR displayname00 LIKE 'Microsoft Office Professional Plus 2013' OR displayname00 LIKE 'Microsoft Office Standard 2013' OR displayname00 LIKE 'Microsoft Office Professional 2013' OR displayname00 LIKE 'Microsoft Office Home and Student 2013' OR displayname00 LIKE 'Microsoft Office Home and Business 2013' OR displayname00 LIKE 'Microsoft Office Professional Plus 2013 (Beta)' OR displayname00 LIKE 'Microsoft Office Starter 2013 - English') AND sys.client0 = 1) AS office2013 ON Total.Name0 =office2013.Name0 LEFT JOIN ( SELECT DISTINCT sys.Name0 , displayname00 AS OfficeEdition , version00 AS OfficeVersion FROM system_disc sys LEFT JOIN add_remove_programs_data arp ON sys.itemkey=arp.machineid WHERE(displayname00 LIKE 'Microsoft Office 2013' OR displayname00 LIKE 'Microsoft Office Professional Plus 2016' OR displayname00 LIKE 'Microsoft Office Standard 2016' OR displayname00 LIKE 'Microsoft Office Professional 2016' OR displayname00 LIKE 'Microsoft Office Home and Student 2016' OR displayname00 LIKE 'Microsoft Office Home and Business 2016' OR displayname00 LIKE 'Microsoft Office Professional Plus 2016 (Beta)' OR displayname00 LIKE 'Microsoft Office Starter 2016 - English') AND sys.client0 = 1) AS office2016 ON Total.Name0 =office2016.Name0 Can someone with some SQL skills please help me? I have come up with a query that will list all the members of a collection based on collection ID, but I can't seem to figure out how to incorporate it.
  3. Thank you, this is exactly what I needed to get the information! I am including the Powershell query that worked for me in case it is of help to anybody else: Get-WmiObject -ComputerName SCCMSERVER -Namespace "root/SMS/site_AAA" -Query "SELECT * FROM SMS_DPGroupContentInfo WHERE GroupID='{DP Group GUID}'" | Sort Name | Format-Table -Property Name,@{n='Size (MB)';e={$_.SourceSize / 1KB -as [int]}},PackageID -Wrap
  4. Peter, Thank you for your reply as this is truly useful information that I did not previously know. Unfortunately, nothing is logged when I click on the content tab. It logs up to the point that I open the properties dialog box of the DP group, but stops thereafter.
  5. I am familiar with Powershell but find the CM2012 cmdlets to be quite lacking. Being as my WMI querying skills are not that strong, I am looking for help in writing a Powershell WMI query (or script, if necessary) to get a list of content on a distribution point group. I can see it in the GUI, so I know it's there. I just can't figure out how to get it into a manageable list text format. I would like it to contain the following fields: Name,Type,Size (MB),Package ID. Any help would be greatly appreciated.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.