System Management

SELECT DISTINCT TOP (100) PERCENT DisplayName0, Version0, COUNT(Version0) AS Total
FROM         dbo.v_GS_ADD_REMOVE_PROGRAMS
GROUP BY DisplayName0, Version0
HAVING      (DisplayName0 LIKE ‘%Adobe Reader%’)
ORDER BY Total DESC, DisplayName0

SELECT     TOP (100) PERCENT Manufacturer0 AS Manufacturer, Model0 AS Model, COUNT(Model0) AS Total
FROM         dbo.v_GS_COMPUTER_SYSTEM
GROUP BY Model0, Manufacturer0
ORDER BY Total DESC, Manufacturer, Model

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.

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.