CCMEXEC.COM – System Center blog

CCMEXEC.COM – by Jörgen Nilsson

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 have used this report so many times now I just have to share it. It will show all computer with a specific file in a specific collection, and has shown to be very useful. Thanks to my colleague Ola Ahrens for applying the finishing touches.

Computer with file

The report mof file can be downloaded here.

To sum up the serial number reporting I have been working on at a customer. This post covers how to update the builtin report "Hardware 01A – Summary of computers in a specific collection", to display the serial number from the value inventoried from the BIOS instead of the value inventoried using System Enclosure. The reason for this change is that the Lenovo comptures as well as other vendors display a blank in value for the serial number in the report. Changing it to use the value already inventoried using the Win32_BIOS WMI provider displays the correct value.

The changes made to the report are highlighted in red, so you can modify the report instead of copying the whole report.

select  distinct

v_R_System_Valid.ResourceID,

v_R_System_Valid.Netbios_Name0 AS [Computer Name],

v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],

v_Site.SiteName as [SMS Site Name],

[Top Console User] = CASE

when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = ‘-1′)

then ‘Unknown’

Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0

End,

v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],

v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],

v_GS_PC_BIOS.SerialNumber0 AS [Serial Number],

v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],

v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],

v_GS_COMPUTER_SYSTEM.Model0 AS [Model],

v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],

v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],

(Select sum(Size0)

from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )

where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and

v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)],

(Select sum(v_GS_LOGICAL_DISK.FreeSpace0)

from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )

where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)]

from v_R_System_Valid

inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)

left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)

inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)

inner join v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = v_R_System_Valid.ResourceID)

inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)

inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)

inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)

left  join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)

inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)

left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)

Where v_FullCollectionMembership.CollectionID = @CollectionID

Order by v_R_System_Valid.Netbios_Name0v_R_System_Valid.ResourceID,

v_R_System_Valid.Netbios_Name0 AS [Computer Name],
v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
v_Site.SiteName as [SMS Site Name],
[Top Console User] = CASE
when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = ‘-1′)
then ‘Unknown’
Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
End,
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
v_GS_PC_BIOS.SerialNumber0 AS [Serial Number],
v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],
v_GS_COMPUTER_SYSTEM.Model0 AS [Model],
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
(Select sum(Size0)
from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and
v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)],
(Select sum(v_GS_LOGICAL_DISK.FreeSpace0)
from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)]
from v_R_System_Valid
inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)
inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)
left  join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)
inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)
left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)
Where v_FullCollectionMembership.CollectionID = @CollectionID
Order by v_R_System_Valid.Netbios_Name0 distinct
v_R_System_Valid.ResourceID,
v_R_System_Valid.Netbios_Name0 AS [Computer Name],
v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
v_Site.SiteName as [SMS Site Name],
[Top Console User] = CASE
when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = ‘-1′)
then ‘Unknown’
Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
End,
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
v_GS_PC_BIOS.SerialNumber0 AS [Serial Number],
v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],
v_GS_COMPUTER_SYSTEM.Model0 AS [Model],
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
(Select sum(Size0)
from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and
v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)],
(Select sum(v_GS_LOGICAL_DISK.FreeSpace0)
from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)]
from v_R_System_Valid
inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)
inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)
left  join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)
inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)
left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)
Where v_FullCollectionMembership.CollectionID = @CollectionID
Order by v_R_System_Valid.Netbios_Name0After This

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!

When working with the reports in SCCM you may want a report to get all the serial numbers for all computers. Below is a very simple report which includes: Name, Manufacturer, Serial number, Model.

It will look like this:

Lenovo_report

SQL Satement:

select v_R_System.Name0, v_GS_PC_BIOS.Manufacturer0, v_GS_PC_BIOS.SerialNumber0,v_GS_COMPUTER_SYSTEM.Model0

FROM v_R_System JOIN v_GS_PC_BIOS on  v_R_System.ResourceID =  v_GS_PC_BIOS.ResourceID JOIN v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID

Have you ever wondered if Anti-virus is running on all client computers? Or if the desktop firewall is started and running as supposed to be? If you haven’t started with DCM yet you should really try it out!
Using DCM it is possible to check if a specific service is running using DCM.

The following steps descibes how to create a configuration item for reporting if the Spooler service is running. We use WMI and query win32\Service for the state of the service.

Create a new Configuration Item as shown in the example below, where “Name=Spooler” determines which service to monitor(easy to change to for instance TmListen for Trend Micro Officescan):

DCM1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

On the validation tab press new and configure the validation as shown below:

DCM3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After pressing OK change the severity on the next page to Error:

DCM2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Then you are ready to add it to your baseline.

When creating a baseline for your environment Desired Configuration Management in Configuration Manager can be used to monitor that all laptops operating system drive is encrypted using DCM. This could be useful to verify that an administrator somewhere haven’t removed bitlocker or to make sure that the deployment strategi for bitlocker is working correctly.

  • Create a new Configuration Item General or Operating System is up to you, under Desired Configuration in the SCCM console.
  • On the Settings tab select New
  • Name it Bitlocker Status
  • Namespace:  Root\CIMV2\Security\MicrosoftVolumeEncryption
  • Class:  Win32_EncryptableVolume
  • Property:  DriveLetter
Bitlocker Settings screen

Bitlocker Settings screen

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  • On the validation screen
  • Operator:  Equals
  • Value:  C:
  • Severity:  Error

Bitlocker_CI2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  • On the Status screen change the severity to Error

Bitlocker_CI3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Completed!

No create a DCM Baseline containing the Bitlocker Configuration Item and assign it to a collection containing you Windows 7 and Windows Vista computers.

When using for instance a Install Software Updates step in a task sequence for instance if you use a Task sequence to first uninstall Office 2003 and then install Office 2007 then you want to install the latest patches during this task Sequence to make sure all security updates are installed.

If the Install Software Updates task needs to reboot the computer the default values for reboot delay and reboot message will be used. As this is no restart computer task the following Task Sequence Variable needs to be defined in the Task Sequence to be able to control the message displayed and the timeout which will be used:

SMSTSRebootTimeout, timeout used for controlling for how long the message should appear for the user
SMSTSRebootMessage, message to be desplayed to the user 

Task Sequence Editor example

Task Sequence Editor example