System Management

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Model like “%HP ProBook 4710s%”

In case you need to find the computer model and manufacturer of a remote computer, just use the following command in a command prompt window:

%windir%\System32\wbem\WMIC.exe /node:"ComputerNameORIpAddress" ComputerSystem Get Model, Manufacturer

If you need to specify user credentials, use the following command:

%windir%\System32\wbem\WMIC.exe /node:"ComputerNameORIpAddress" /user:"domain\username" /password:"password" ComputerSystem Get Model, Manufacturer

Recently I reinstalled ConfigMgr server in my company and was looking for an easy method to upgrade all Configuration Manager clients to 4.00.6487.2000 version.
Put some notes on the table and finally made the following plan:

• Create a report that counts all client versions. (This is optional, just for information purposes).
  Report query is:

  SELECT TOP (100) PERCENT Client_Version0 AS [ConfigMgr client version], COUNT(Client_Version0) AS Total
FROM dbo.v_R_System GROUP BY Client_Version0, Client0 HAVING (Client0 = 1)
ORDER BY Total DESC, [ConfigMgr client version]

• Create a collection (“Older Clients” for example) with all system resources with a client version not 4.00.6487.2000.
  Collection query is:

  SELECT SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
FROM SMS_R_System
WHERE SMS_R_System.ClientVersion != "4.00.6487.2000"

  This way, system resources with older client version will be members of this collection.

• Created a package and program with ConfigMgr client upgrade with custom command line parameters.
  Program command line is:

  CCMSETUP.EXE /noservice SMSSITECODE=CFM SMSCACHESIZE=1024 SMSSLP=CFM.DOMAIN.COM SMSMP=CFM.DOMAIN.COM RESETKEYINFORMATION=TRUE

• Advertised it to “Older Clients” collection.

Now, as system resources with older client version are members of this collection they will receive the advertisement and will silently install the latest ConfigMgr client.

When the collection will have no system resources, I will know that all clients are upgraded. Also, I can check this by opening the same report from any browser on any computer.

Following the post regarding how to install drivers based on computer model using WMI query, here is another query you can use in your task sequences to install some processor specific software for example.

To find the processor manufacturer during a task sequence, insert a condition for the step you need, and select Query WMI. Then, for WMI Namespace use root\cimv2. For WQL Query use SELECT * FROM Win32_Processor WHERE Manufacturer like “%GenuineIntel%” or SELECT * FROM Win32_Processor WHERE Manufacturer like “%AuthenticAMD%” depending on what you need.

To find the processor manufacturer of your computer, open a command prompt and type the following: WMIC CPU GET Manufacturer.

Last month, after restoring the only ConfigMgr site and importing new reports, I saw that none of the reports I’m using have a link to another report.

To be sincere, this was a problem I didn’t know where to start troubleshooting. Thanks to guys from Microsoft’s ConfigMgr forum, who helped me identify the problem and recommended some troubleshooting steps.

So, the problem was due the fact that none of my reports had a link to another report. Don’t know how this happened… Anyway, to resolve this issue, I quickly installed a ConfigMgr site in a virtual machine and using Tom Watson’s advice, created a query that lists all reports with links to other reports. The query is something like this:

SELECT     TOP (100) PERCENT ReportID, Name, Category, DrillThroughReportID
FROM       dbo.v_Report
WHERE      (NOT (DrillThroughReportID IS NULL))
ORDER BY   ReportID

and the result looks like this:

DrillThroughReportID tells the ReportdID of the linked report. Basically, in a default configuration, I know the original ReportID and the linked ReportID.

Now, knowing these info, I’m manually creating the links in my production environment. This is a long process indeed, but I couldn’t find a way to automate this.

For the one who searched for these…

To create a collection with all Windows XP SP1 systems, use the query bellow:

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 1"

Similarly, to create a collection with all Windows XP SP2 systems, just change the Service Pack version number:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 2"

And the same for all Windows XP SP3 systems:

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 3"

Want to know if your users store mp3 files on Desktop and My Documents folder?

1. Add .mp3 file type to Software Inventory Client Agent properties
   1. Navigate to Site Database – Site Management – Site Name – Site Settings – Client Agents and double click Software Inventory Client Agent
   2. Select Inventory Collection tab
   3. Click the new button and type *.mp3 as a file name.
   4. Set the location: All client hard disks or Variable or path name
      
   5. Click OK to close all open windows
2. (Optional) Wait for Software Inventory to run on all clients
3. Create a report using the following query:SELECT TOP (100) PERCENT SYS.Netbios_Name0, SF.FileName, SF.FileSize / 1024 AS [Size, KB], SF.FilePath FROM dbo.v_GS_SoftwareFile AS SF INNER JOIN dbo.v_R_System AS SYS ON SYS.ResourceID = SF.ResourceID WHERE (SF.FileName LIKE '%mp3') AND (SF.FilePath LIKE '%My Documents%') OR (SF.FileName LIKE '%mp3') AND (SF.FilePath LIKE '%Desktop%') ORDER BY SYS.Netbios_Name0

When using task sequence, you might want to install different driver packages for different computer models. This can be accomplished by using a WMI query.

First of all you need to have driver packages for all your computer models and know the exact model name for every computer.

To find this, open a command prompt and type WMIC ComputerSystem GET Model (use this command on every computer to find it’s model).

Then, Edit your task sequence. Add how many steps you need with “Apply Driver Package”. Select the driver package you have created for a certain computer model (HP dc5700 in my case).

In the Options tab, click Add Condition and select Query WMI. In the WMI Query Properties window, make you sure you have root\cimv2 as WMI Namespace and write the following query in the WMI Query input box:

SELECT * FROM Win32_ComputerSystem WHERE Model LIKE “%dc5700%” for HP Compaq dc5700 computer models.

Now, for every driver package, replace the model name with the computer model name the driver package is for. This way, the task sequence will install the correct drivers on every computer that will match the model specified in the query and will skip other steps sending the following status message: “The task sequence execution engine skipped the action (HP Compaq dc5700) in the group (Apply driver packages) because the condition was evaluated to be false”.

As I have some free time, I’m testing different things with SCCM, mainly in creating different collections that I might need. Bellow you can find the query that will show you all x64 systems, client and server operating systems.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC"

You can further customize this so it will show you only x64 servers or only x64 Vista systems etc.

Someone just asked how to create a collection with all laptop computers? Well, if there is a good naming convention on site, this query will be easy to make even for me But, otherwise, we need to use chassis type value because laptops and notebooks are not the same things.

The full list with chassis types are bellow (taken from http://www.microsoft.com/technet/scriptcenter/guide/sas_cpm_btnz.mspx?mfr=true):

So, to make a collection with all kind of mobile systems (Portable, Laptop, Notebook, Sub Notebook) we will use 8, 9, 10 and 14 chassis types. The final query looks like this:

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("8", "9", "10", "14")

If you receive the “This query has a syntax error.” error, change the quotation marks when pasting the query, as this one does not work with ConfigMgr.