CCMEXEC.COM – System Center blog

CCMEXEC.COM – by Jörgen Nilsson

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When using a mandatory OSD advertisement to install a Operating system it is a great benefit to remove the computer from the Collection to where the OS deployment is advertised. The OSD task sequence advertisement can then be set to always rerun and all problems related to reinstalling an existing computer is solved.

This can be achieved by using a status filter rule together with a VBscript which removes the computer from the collection once the Task Sequence completes successfully.

UPDATE!!
I have updated the script to search for active computer records in SCCM using the name and then removing the computer from the collection using the ResourceID instead of using the name for matching. I have seen at customers that some third party applications created direct memberships with a different naming convention than the SCCM Admin Console does, this updated script will solve this problem.

Update 2
The script have been updated with the possibility to enter more than one collection to remove the computer from, it can also write an event to the event-log on the SCCM server with the name of the computer and the collection/collections it will be removed from.
I have removed the script code from this blog and made it available as a file instead, to avoid problems when cut/pasting the text.

You can download it here: http://ccmexec.com/wp-content/uploads/2010/12/Remove.vbs.txt

Download the script and save it as “remove.vbs”  edit the following line with the collection/collections you want the computer removed from

sCollectionIDs = “00100053:0010004A:00100069″

when that is done, complete the steps below to configure the status filter rule.

———————————————-

Configuring the status filter rule:

  1. Under site settings create a new status filter rule
  2. Configure it to use the following settings:

Component : Task Sequence Manager
Message Id: 11171

Run a Program: cscript.exe e:\sccmtools\remove.vbs %msgsys

Status1 status filter rule 2