CCMEXEC.COM – System Center blog

CCMEXEC.COM – by Jörgen Nilsson

Browsing Posts tagged SCCM Reports

Hi,
Answered a question on Technet forum this morning and about a report to list all Windows 7 computers without Service Pack 1 installed.
I modified it slightly for this post to include the possibility to filter on a specific collectionID.

When creating the report create a prompt in the report called varcollection

Report Query:

Select SYS.Netbios_Name0, OPSYS.Caption0 as C054, OPSYS.Version0, @varcollection as Collection
from v_R_System SYS
join v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID join v_FullCollectionMembership FCM on FCM.ResourceID =SYS.ResourceID
where OPSYS.Caption0 like 'Microsoft Windows 7%' and OPSYS.Version0 <> '6.1.7601' and FCM.CollectionID = @varcollection
order by SYS.Netbios_Name0

Select SYS.Netbios_Name0, OPSYS.Caption0 as C054, OPSYS.Version0, @varcollection as Collection

from v_R_System SYS

join v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID join v_FullCollectionMembership FCM on FCM.ResourceID =SYS.ResourceID

where OPSYS.Caption0 like 'Microsoft Windows 7%' and OPSYS.Version0 <> '6.1.7601' and FCM.CollectionID = @varcollection

order by SYS.Netbios_Name0

SQL query for the varcollection prompt:


begin
if (@__filterwildcard = '')
select distinct CollectionID, Name from v_Collection order by Name
else
select distinct CollectionID, Name from v_Collection
WHERE CollectionID like @__filterwildcard
order by Name
end

As a follow up and as I  promised in a previous post here are both reports which makes it possible to track advertisement status per collection and to link it to the next report, All system resources in a specific collection for a specific advertisement in a specific state. These both reports makes it possible to track advertisement status for a specific advertisment and collection, which is very useful when installing/upgrading applications and reporting needs to be done per site, building or department.

Import both reports which can be found here, and link them like this.

advertisementpercollectionlinkI hope these two reports are useful in your environments out there. Thanks again Ola for you help!

Another report ;-) ! There are many scenarios where I found that it would have been great to be able to track advertisement status for a specific advertisement per collection. For instance, if a manager places a service request that 40 computers at that department should have Office 2010 installed, it would be great to have a report showing the result per collection. 
advpercol1

 

 

 

 

 

 

 

 

 

You can download it here. I plan to post the linked report aswell later on so it will be possible to link the report to see which computers have succeeded or not.

My friend Ola helped again to finish this report, thanks!

Stay tuned for the next part!

This reports will help you plan you roll-out project and plan it by presenting a report with a count of each computer model per collection. If you already have collections in SCCM per site, per department, per subnet or whatever you wan’t, you can use them to report which models exist. The reports can be downloaded from here.

modelreports1

 

 

 

 

 

 

The second report provides the possibilty to dig into which computers of a specific model exist in a collection, including computername. When the reports are imported the links between the reports are lost, here is how you re-link them.

  • Right click on the report “Computer models count per collection” and select properties.
  • Select the Links tab
  • Select link to another report, select the other report(Computers specific model in a specific collection) inlcuded and add the prompts described below.

linkreport

 

 

 

 

 

 

 

 

 

 

 

 

Test the reports so that the links are working, then you are good to go!

I got this request from a customer and thought I could share it. Below query describes how to modify the query for the built-in report “Computer information for a specific computer” to include serial number.  The added sql commands are marked in red color if you want to change it yourself instead of copying the SQL syntax.

SELECT SYS.Netbios_Name0, SYS.User_Name0, SYS.User_Domain0,  SYS.Resource_Domain_OR_Workgr0,
  OPSYS.Caption0 as C054, OPSYS.Version0,
 MEM.TotalPhysicalMemory0,  IPAddr.IP_Addresses0, Processor.Manufacturer0,
 CSYS.Model0, v_GS_PC_BIOS.SerialNumber0, Processor.Name0, Processor.MaxClockSpeed0 
FROM v_R_System SYS
LEFT JOIN  v_RA_System_IPAddresses IPAddr on SYS.ResourceID = IPAddr.ResourceID
LEFT JOIN  v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID
LEFT JOIN  v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID
LEFT JOIN  v_GS_PROCESSOR Processor  on Processor.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_PC_BIOS on SYS.ResourceID = v_GS_PC_BIOS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
WHERE SYS.Netbios_Name0 = @variable
ORDER BY SYS.Netbios_Name0, SYS.Resource_Domain_OR_Workgr0

Enjoy!