Which hotfixes should I apply?

In general - you should evaluate all hotfixes available, and only apply those applicable to your environment.  However, some of these below I have seen impact almost every environment, and should be heavily considered.
This list is nothing official.... this is just a general list of the recommended hotfixes I end up proactively applying to most environments.... it is not a complete list of ALL hotfixes, and you may be affected by other issues.

Before we get to the lists – some general guidance on hotfixes to make you more successful:

 

ALWAYS - on Server 2008 OS, run the hotfix MSI from an elevated command prompt window.  This will launch the install of the hotfix, and then launch the boot-strapper window in an elevated process – which is required.  Do this regardless of the UAC configuration of the 2008 OS.
ALWAYS - make sure you read the instructions to understand if the hotfix is a SQL update, installed to the RMS, MS, and/or Gateway, AND/OR applies to agents as well.
ALWAYS - make sure you double-check the DLL version of the updated files to make sure the hotfix successfully applied after installing.
ALWAYS - make sure you double-check the \AgentManagement directory of the management servers and gateways, to make sure if there is an agent update, the x86 and x64 MSP was copied over correctly.
ALWAYS – when installing a hotfix/cumulative update on an OpsMgr server role, run the downloaded MSI, such as “SystemCenterOperationsManager2007-SP1-KB954049-X86-X64-ENU.MSI” – and install the “System Center 2007 Hotfix Utility” to the DEFAULT location – and then kick off the update FROM THE UI that comes up by clicking “Run Software Update”.  This is critical and not following this process is the cause for many failures to apply the hotfix DLL’s, or failure to copy the agent MSP update files to the \Agentmanagement directory.  NEVER run the MSP files manually on a SCOM server role… because the additional steps run by the boot-strapper will not execute if you do that.  The only exception to this – is running from the command line.  See:  http://blogs.technet.com/b/kevinholman/archive/2010/10/12/command-line-and-software-distribution-patching-scenarios-for-applying-an-opsmgr-cumulative-update.aspx
ALWAYS check the language version of the hotfix, and make sure it is the same language version as your SCOM base install.  For instance – if you have a English base SCOM install – do not download a localized German version of a hotfix and apply it – or it can break the English SCOM base install.
ALWAYS log on to your OpsMgr role servers using a domain user account that meets the following requirements:

• SCOM administrator role
• Member of the Local Administrators group on all SCOM role servers (RMS, MS, GW, Reporting)
• SA privileges on the SQL server instances hosting the Operations DB and the Warehouse DB.

These rights (especially the user account having SA priv on the DB instances) are often overlooked.  These are the same rights required to install SCOM, and must be granted to apply major hotfixes and upgrades (like RTM>SP1, SP1>R2, etc…)  Most of the time the issue I run into is that the SCOM admin logs on with his account which is a SCOM Administrator role on the SCOM servers, but his DBA’s do not allow him to have SA priv over the DB instances.  This must be granted temporarily to his user account while performing the updates, then can be removed, just like for the initial installation of SCOM as documented HERE.  At NO time do your service accounts for MSAA or SDK need SA priv to the DB instances…. unless you decide to log in as those accounts to perform an update (which I do not recommend).




Common OpsMgr 2007 Post-R2 hotfixes:
This list ABSOLUTELY assumes you are at OpsMgr R2-RTM level as a base (6.1.7221.0).   

Hotfix	Update Files	Resolves	Applies to:	Comments
MP Update	Microsoft.SystemCenter.2007.mp 6.1.7695.0 Microsoft.SystemCenter.OperationsManager.2007.mp 6.1.7695.0 Microsoft.SystemCenter.OperationsManager.AM.DR.2007.mp 6.1.7695.0 Microsoft.SystemCenter.OperationsManager.Reports.2007.mp 6.1.7695.0 ODR.mp 6.1.7695.0	New reports, knowledge, monitors, rules.  See MP Guide.	MP import only	I recommend this update for ALL OpsMgr R2 environments.
2495674 R2 CU5	OpsMgr 2007 R2 CU5 Cumulative Update http://www.microsoft.com/download/en/details.aspx?id=26938  Multiple.  See KB Article.  Note this is a DLL update, MP updates, and SQL scripts update.	Many updates.  See KB article for all Cumulative updates at LINK	RMS MS GW  Agents AuditCollector Console WebConsole MP Import TSQL Script	This hotfix includes a SQL script, which you execute on the database in a query window.
971233	none	The console shows customized subscriptions SMTP{GUID} after you upgrade to OpsMgr R2 from OpsMgr SP1	Operations Database (TSQL only)	I recommend this hotfix only if you are impacted with this issue.

Common OpsMgr 2007 Post-SP1 hotfixes:
This list ABSOLUTELY assumes you are at OpsMgr SP1 level as a base (6.0.6278.0).  These DO NOT APPLY these to OpsMgr R2. 

Hotfix	Update Files	Resolves	Applies to:	Comments
MP Update	Microsoft.SystemCenter.2007.mp 6.0.6709.0 Microsoft.SystemCenter. OperationsManager.2007.mp 6.0.6709.0 Microsoft.SystemCenter. OperationsManager.AM.DR.2007.mp 6.0.6709.0	Agent restarts, many other critical enhancements	Management Pack Import only (Import via console once extracted)	I recommend this update for ALL OpsMgr SP1 environments.
2028594	SP1 Cumulative Update 1.  Multiple files.  See KB article	Many.  See KB article	RMS MS GW Agents Consoles MP Import SQL Scripts (OpsDB and DW)	I recommend this update for ALL OpsMgr SP1 environments. ***Note:  This update REQUIRES 971541 as a prerequisite
971541	SP1 Rollup hotfix.  Multiple files.  See KB article	Many.  See KB article	RMS MS GW Reporting Agents Console MP Import	I recommend this update for ALL OpsMgr SP1 environments.
972881	Managedentitychange.sp.sql	The changes to the display name of a managed entity are not synchronized in the Operations Manager Data Warehouse database	Data Warehouse Database (T-SQL only)	I recommend this update for ALL OpsMgr SP1 environments. This hotfix includes a SQL script, which you execute on the database in a query window.
954643	Managementpackinstall.sp.sql	Event ID 31569 is logged after you install a management pack that includes reports on a System Center Operations Manager 2007 SP1 server	Data Warehouse Database (T-SQL only)	I recommend this hotfix only if you are impacted with these events. This hotfix includes a SQL script, which you execute on the database in a query window.
974254	Autotablecreation.sql Viewcreatesprocs.sql	1. Unable to create large number of groups. 2. Import fails when importing an MP or when creating a MP from a  template	Operations Database (TSQL only)	I recommend this hotfix only if you are impacted with this issue. This hotfix includes a SQL script, which you execute on the database in a query window.

Common related Windows Operating System Hotfixes:
This list is not sorted by OS or anything special – just a collection of OS related hotfixes that SCOM might require, or might fix an issue with the OS that impacts OpsMgr.  These can apply to SP1 or R2 environments.  

Hotfix	Resolves	Applies to:	Comments
2470949	The RegQueryValueEx function returns a very large incorrect value for the "Avg. Disk sec/Transfer" performance counter in Windows Server 2008 R2 or in Windows 7	Any OpsMgr Agent Managed or Server role running on Windows 2008 R2 or Windows 2008 R2 SP1, or Win7	I recommend this hotfix to be applied to any Server 2008R2 or Win7 machine, if it is agent managed or holds a SCOM server role.
2495300	Invalid "Avg. Disk sec/Transfer" value returned by the RegQueryValueEx function in Windows Server 2008 or in Windows Vista	Any OpsMgr Agent Managed or Server role running on Windows 2008 or Vista	I recommend this hotfix to be applied to any Server 2008 or Vista machine, if it is agent managed or holds a SCOM server role.
981314	The "Win32_Service" WMI class leaks memory in Windows Server 2008 R2 and in Windows 7	RMS MS GW Agent (only if running on Windows 2008 R2 or Win7)	I recommend this hotfix to be applied to any Server 2008R2 or Win7 machine, if it is agent managed or holds a SCOM server role. This hotfix is already included in Server 2008 R2 Service Pack 1
981263	Management servers or assigned agents unexpectedly appear as unavailable in the Operations Manager console in Windows Server 2003 or Windows Server 2008 (ESE jet database corruption)	RMS MS GW Agent	I recommend this hotfix for all RMS, MS, and GW roles running Windows Server 2003 SP2, or Windows Server 2008 SP2. Apply to agent machines if you feel you are impacted by this issue.
933061	WMI Stability in Server 2003	Agent (2003 OS only)	I recommend this hotfix for all agent managed computers running Windows Server 2003, SP1 or SP2, x86 or x64
955360	Cscript 5.7 update for Server 2003	Agent (2003 OS only)	I recommend this hotfix for all agent managed computers running Windows Server 2003, SP1 or SP2, x86 or x64
968760	High handle count on the RMS A managed application has a high number of thread handles and of event handles in the Microsoft .NET Framework 2.0	RMS	I recommend this hotfix is you are experiencing high handle count on the RMS.  This hotfix requires SP2 for the OS and .NET 2.0 SP2.
968967	The CPU usage of an application or a service that uses MSXML 6.0 to handle XML requests reaches 100% in Windows Server 2008, Windows Vista, Windows XP Service Pack 3, or other systems that have MSXML 6.0 installed (Spinlock)	RMS MS GW Agent	I recommend this hotfix if you are impacted with this issue, which is very common. You might find a MonitoringHost.exe process randomly stuck at 100% CPU.  If so – this hotfix might be applicable.
951327	The System Center Operations Manager 2007 console may crash in Windows Server 2008 or in Windows Vista when you open the Health Explorer window	Any Vista or Server 2008 computer with a SCOM console installed	I recommend this hotfix only if you run the console on Server 2008 or Vista.  This hotfix is already included in Server 2008 SP2.
952664	The Event Log service may stop responding because of a deadlock on a Windows Server 2008-based or Windows Vista-based computer	RMS MS GW Agent	I recommend this hotfix only if you host an OpsMgr server or agent role on Vista or Server 2008.  This hotfix is already included in Server 2008 SP2.
953290	An application may crash when it uses legacy methods to query performance counter values in Windows Vista or in Windows Server 2008	RMS MS GW Agent	I recommend this hotfix only if you host an OpsMgr server or agent role on Vista or Server 2008.  This hotfix is already included in Server 2008 SP2.
958661	FIX: Small memory leaks may occur when you use RSCA to query runtime statistics in IIS 7.0	Any OpsMgr Agent/Server role with IIS 7.0 installed	I recommend this hotfix in all cases where you are monitoring servers with IIS 7.0 installed, and use the IIS Management pack. This hotfix is already included in Server 2008 SP2.
958807	Windows Server 2008 Failover Clustering WMI provider does not correctly handle invalid characters in the private property names causing WMI queries to fail	Any Server 2008 agent managed cluster node	I recommend this hotfix only if you are impacted with this issue, and use the current Cluster MP. This hotfix is already included in Server 2008 SP2.

Make sure you see these additional posts on the subject of hotfixes:
http://blogs.technet.com/kevinholman/archive/2008/06/25/a-little-tidbit-on-hot-fixes-for-opsmgr.aspx
http://blogs.technet.com/kevinholman/archive/2008/06/24/how-do-i-know-which-hotfixes-have-been-applied-to-which-agents.aspx
http://blogs.technet.com/kevinholman/archive/2008/06/27/a-report-to-show-all-agents-missing-a-specific-hotfix.aspx
http://blogs.technet.com/kevinholman/archive/2009/02/25/applying-an-opsmgr-hotfix-to-a-rms-cluster-node-some-things-to-be-aware-of.aspx

Installing SCOM 2007 R2 on a SQL 2008 Instance with all Windows Firewalls Enabled.

I decided I needed to re-install my lab environment.  I wanted to keep all of the firewalls on during the install process and only open the ports that are actually needed. I installed SQL using a named instance as many customers use a SQL 2008 cluster.
After I installed the SCOM database on the SQL 2008 server with all firewalls on.  I created a firewall rule to let port 1433 allow connections.  As specified in the Supported Configurations doc

Root management server 1433 —> OperationsManager database

I also setup a firewall rule to allow port 1434 back to the RMS server from the SQL Instance Server. (Also in the guide)

Root management server 1434 UDP < — OperationsManager database

I start the install of SCOM to the RMS server.  I unchecked Database as my database is already install the on the SQL instance.

I typed in my SC Database Instance Name and clicked Next

But I got this error “Setup cannot location the SC database”

So I enabled firewall logging to see what was getting dropped blocked by setting the firewall to log dropped packets.


In the SCOM setup I clicked back and then next.
I checked the firewall logs in %systemroot%\system32\Logfiles\Firewall\pfirewall.log  and it looks like UDP port 1434 is being dropped

date	time	action	protocol	src-ip	dst-ip	src-port	dst-port	size	path
12/26/2010	16:56:54	DROP	UDP	192.168.2.63	192.168.2.61	58321	1434	38	RECEIVE

I create another rule on the SQL server to enable UDP port 1434


In the SCOM setup I click back and next again.

Once again same failure.  “Setup cannot location the SC database”
Back to the firewall logs.  It now needs TCP port 62756 (Not in the guide)

date	time	action	protocol	src-ip	dst-ip	src-port	dst-port	size	path
12/26/2010	17:12:03	DROP	TCP	192.168.2.63	192.168.2.61	50503	62756	38	RECEIVE

I create another rule on the SQL server to enable TCP port 62756

After that rule is enabled I am able to continue on a install SCOM successfully with all of the windows firewalls still on.

HTTP 500 Error with Large Reports in SCCM 2007

If you have more than a couple thousand clients in your SCCM 2007 site and have ever tried to run a large report (like “Hardware 01A – Summary of computers in a specific collection” against the All Systems collection, for example), you’ve probably encountered this HTTP 500 error:

This problem and resolution are pretty well documented for Windows Server 2003 and IIS6, but in this particular case we’re running Windows Server 2008 SP2, so we’re using IIS7.  The underlying cause is the same (default ASP Buffering Limit is set too low to handle the size of the report) and the fix is the same (increase the limit), but the steps to fix are just a little different for IIS7:


1. Launch IIS Manager
2. Click on the server name in the left pane to bring up the features for IIS (if you have more than just the SCCM reporting hosted in IIS and don’t want to modify this setting for all sites, expand the Sites node and click Default Web Site to bring up the features for just that site)
3. Double-click on the ASP feature

4. Expand the Limits Properties node and find the Response Buffering Limit item.

By default it’s set at about 4MB (4194304 bytes).  The general rule of thumb is about 1MB per 1000 records, but you may want to go with 1.5 to 2 MB just to be safe.  Modify the value and click Apply in the actions pane.
5. Restart IIS.
Now when you run your report you should actually get the results!

As you can see, in this case we had around 4500 records, which was just enough to fill the response buffer and generate the error.  Bumping it up to 10k bytes to accommodate future growth was more than enough to resolve the issue.

Useful ConfigMgr Resources

========================================================
ConfigMgr Resources/Information:

• Learning:
  • TechCenter on TechNet: http://technet.microsoft.com/en-us/configmgr/default.aspx
  
  
  • Features (Technical Content): http://technet.microsoft.com/library/bb680550.aspx
  
  
  • Virtual Labs: http://technet.microsoft.com/en-us/configmgr/bb539977.aspx
  
  
  • Webcasts: http://www.microsoft.com/events/series/technetmms.aspx?tab=webcasts&id=42364
  
  
  • Audiocasts (WMA): http://technet.microsoft.com/en-us/configmgr/bb727202.aspx
  
  
  • TechNet Virtual Labs, ConfigMgr: http://technet.microsoft.com/configmgr/bb539977.aspx
  
  

• Download Service Pack 2 Here: http://www.microsoft.com/downloads/details.aspx?FamilyID=3318741a-c038-4ab1-852a-e9c13f8a8140&displaylang=en (Upgrade download, Full Evaluation download available here).  Configuration Manager 2007 SP1 left support on January, 11^th, 2011 (http://support.microsoft.com/lifecycle/?p1=12769).


• Open (public) Betas Invites & Product Feedback.  Note: the Connect sites have information about both R3 and v.Next.
  • Configuration Manager 2012: https://connect.microsoft.com/ConfigurationManagervnext/program4346
  
  
  • Product Feedback: smswish@microsoft.com
  
  
  • Documentation Feedback: smsdocs@microsoft.com
  
  


• Windows Server 2008 and Server 2008 R2 and Configuration Manager: http://technet.microsoft.com/en-us/library/cc431377.aspx

• · PFE worldwide Blogs, News and Announcements: http://www.opsvault.com/
  • o System Center Configuration Manager 2007 Toolkit V2: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5a47b972-95d2-46b1-ab14-5d0cbce54eb8&displaylang=en
  
  

• Blogs:
  • Product Team:
    • System Center Product Team: http://blogs.technet.com/systemcenter/
    
    
    • Configuration Manager Product Team: http://blogs.technet.com/configmgrteam/
    
    
    • Configuration Manager Support Team: http://blogs.technet.com/configurationmgr/
    
    
  
  
  • PFE UK Manageability: http://blogs.technet.com/b/manageabilityguys
  
  
  • The Deployment Guys (TechNet): http://blogs.technet.com/deploymentguys
  
  
  • Steve Rachui – PFE (MSDN): http://blogs.msdn.com/steverac/
  
  
  • ConfigMgr Writers – team of writers behind production documentation (TechNet): http://blogs.technet.com/wemd_ua_-_sms_writing_team/
  
  
  • Adam Meltzer – ConfigMgr Developer (MSDN): http://blogs.msdn.com/ameltzer/
  
  


• ConfigMgr KB Article RSS Feed: http://support.microsoft.com/common/rss.aspx?rssid=12769&ln=en-us


• Tools:
  • System Center Update Publisher (tool used to publish custom updates) TechCenter: http://technet.microsoft.com/manageability/bb741049.aspx
  
  
  • Acresso (formerly InstallShield) AdminStudio Configuration Manager Edition: http://technet.microsoft.com/en-us/configmgr/bb932316.aspx.
  
  

• Catalogues:
  • Desired Configuration Monitoring, Configuration Catalogues: http://technet.microsoft.com/en-us/configmgr/cc462788.aspx
  
  
  • 3^rd Party Custom Catalogues for Updates: http://technet.microsoft.com/en-us/configmgr/bb892875.aspx
  
  


• System Center Partners (3^rd Party): http://www.microsoft.com/systemcenter/en/us/alliance-members.aspx
  • Dell:
    • Dell & System Center: http://www.dell.com/content/topics/global.aspx/sitelets/solutions/management/microsoft_sms
    
    
    • Dell ConfigMgr Wiki: http://www.delltechcenter.com/page/SCCM+-+System+Center+Configuration+Manager
    
    
    • OSD Extension, Dell Deployment Pack for ConfigMgr: http://www.delltechcenter.com/page/Dell+Deployment+Pack+for+Configmgr+%28Server+Deployment%29
    
    
  
  
  • HP:
    • HP Management Integration Solutions for Microsoft: http://h18013.www1.hp.com/products/servers/management/integration/ms_integration.html
    
    
    • OSD Extension, HP Insight Control Suite for System Center: https://h20392.www2.hp.com/portal/swdepot/displayProductInfo.do?productNumber=HPICE-SC
    
    
  
  
  • IBM:
    • Microsoft System Management Solutions for IBM Servers: http://www.ibm.com/systems/management/director/partners/microsoft/
    
    
    • OSD Extension, IBM Deployment Pack for Microsoft System Center Configuration Manager 2007: http://publib.boulder.ibm.com/infocenter/toolsctr/v1r0/topic/dpsccm/dpsccm_ic_product_page.html
    
    
  
  


• ConfigMgr Community (including sites like myitforum.com that have community content): http://technet.microsoft.com/configmgr/bb625749.aspx


• Product Information (Sales/Business Content): http://www.microsoft.com/systemcenter/configurationmanager/en/us/default.aspx

ConfigMgr Design Resources

• Configuration Manager Planning and Deployment Overview: http://technet.microsoft.com/en-us/library/bb693806.aspx


• Planning and Deploying the Server Infrastructure for Configuration Manager 2007: http://technet.microsoft.com/en-us/library/bb680397.aspx


• Planning and Deploying Clients for Configuration Manager 2007: http://technet.microsoft.com/en-us/library/bb680373.aspx


• Configuration Manager Supported Configurations: http://technet.microsoft.com/en-us/library/bb680717.aspx


• Configuration Manager Sample Configurations and Common Performance Related Questions: http://go.microsoft.com/fwlink/?LinkID=113136.

OS Deployment

• Desktop Deployment TechCenter on TechNet: http://technet.microsoft.com/en-us/desktopdeployment/default.aspx


• Microsoft Desktop Deployment Toolkit: http://www.microsoft.com/downloads/details.aspx?familyid=3bd8561f-77ac-4400-a0c1-fe871c461a89&displaylang=en
  • Microsoft Deployment Toolkit 2010 is the newest version of Microsoft Deployment Toolkit, a Solution Accelerator for operating system and application deployment. MDT 2010 supports deployment of Windows 7 and Windows Server 2008 R2 in addition to deployment of Windows Vista, Windows Server 2008, Windows Server 2003, and Windows XP.
  
  


• Application Compatibility Guidance: http://technet.microsoft.com/en-gb/desktopdeployment/bb414773.aspx


• Application Compatibility Toolkit: ACT is a collection of tools to inventory, assess and help fix applications for Windows Vista deployment projects. This update to ACT includes new tools to assess application compatibility with Internet Explorer 8 and detect potential application compatibility issues caused by Windows Vista or Windows Server 2008 security updates. http://technet.microsoft.com/library/cc507852.aspx

Out of Band Management

• · Configuring Out of Band Management: http://technet.microsoft.com/en-us/library/cc161822.aspx


• · Troubleshooting Out of Band Management: http://technet.microsoft.com/en-us/library/cc161834.aspx


• · Intel resources:
  • Infrastructure Prep Checklist for Microsoft SCCM: http://communities.intel.com/docs/DOC-2300
  
  
  • SCCM / vPro Common Issues and Potential Resolutions:
    • § SP1: http://communities.intel.com/docs/DOC-1627
    
    
    • § SP2: http://communities.intel.com/docs/DOC-4153
    
    
  
  

Moving SCCM to a New Server

There may come a time where you need to move you SCCM environment to a new server. This will help make that process as possible.
When needing to move SCCM to new hardware you must remember:

1. The side code cannot be renamed without uninstalling SCCM and reinstalling or standing up a new SCCM server (side-by-Side) migration.


2. You must keep the same server name as the existing SCCM server.


3. You must also keep the same drive structure as the existing SCCM server.

So what are the steps you need to take?

1. Backup the site settings and database of the SCCM Server


2. Backup local folders (if any) used for package sources: (source folder, drivers folder…etc). Make a note of the permissions as well.


3. Decommission the SCCM Server and remove from the domain.
   1. Note: Name, IP, site code, installation path,
   
   


4. Build the new server with the information from step 3.
   1. Install and configure all SCCM prerequisites
   
   
   2. If SQL was local before Install the same SQL version and any updates
   
   
   3. Install SCCM 2007 to the same patch level, directory, site code from the old SCCM server
   
   


5. Restore folders used for SCCM (Sources, drivers, packages, and set permissions


6. Restore the SCCM database from the old server using the Site repair wizard


7. Resolve any errors under the site status.

I hope this blog helps you with your transition

WSUS failed to sync some of the updates

I came across an issue where I was noticing errors in the SMS_WSUS_SyncUpdates Component. Every time the synchronization ran for windows updates, I would get the following:
SMS WSUS Synchronization failed.


Message: Failed to sync some of the updates.
Source: Microsoft.SystemsManagementServer.SoftwareUpdatesManagement.WSyncAction.WSyncAction.SyncUpdates.

This typically has to do with the EULA agreement with some updates. This error should clear up at the next synchronization. However in this case it did not.
Trying a reboot of the server, SCCM Services, WSUS did not help. This was happening with a handful of updates.
To resolve this issue:
I also added the network services account to the root of the WSUS folder as it was missing.
Next I ran a wsustuil -reset
I also uninstalled the SUP role, waited about 5 minutes then reinstalled the role. I then kicked off a synchronization which ran a full synchronization again.
I monitored the wsyncmgr.log file to ensure the updates are synchronizing.

It took some time to do the full synchronization but it completed successfully without reproducing the synchronization errors.

Of duplicate computer accounts in SCCM

Recently wrote a little script that deletes duplicate computer accounts in SCCM. This is because I have included the addition of computers from AD

#Region Description

#

# Name       : remove-DuplicateComputers.ps1

#

# Version    : 1.0

# Web        :

# Date       : 21-12-2010  

# Description: Find duplicate computer objects to SCCM and delete

#             

#EndRegion

#Region Functions

#EndRegion

[array]$ArrayComps=$null

$ConnStr="Provider=MSDASQL;DSN=SMS_TB1;"

$sSQL="select T.Name0, T.CountName

from (select dbo.v_R_System.Name0, count(*) as CountName

from dbo.v_R_System

Group By dbo.v_R_System.Name0 ) as T

where T.CountName > 1

ORDER BY T.Name0"

$adoSQL= New-Object -ComObject  ADODB.Connection

$rsSheet= New-Object -ComObject ADODB.Recordset

$adoSQL.ConnectionString=$ConnStr

$adoSQL.Open()

$rsSheet.Open($sSQL,$adoSQL)

if(!$rsSheet.EOF)

{

 $meter=0

 while (!$rsSheet.EOF){

  $ArrayComps+=@{Name=$rsSheet.Fields.Item("Name0").Value;Count=$rsSheet.Fields.Item("CountName").Value; BadID="";LastDate=$null;}

  $rsSheet.MoveNext()

 }

}

if (!$adoSQL)

{

 $adoSQL.Close()

}

$adoSQL=$null

$meter=0

$meter2=0

foreach ($Comp in $ArrayComps)

{

 Get-WmiObject -Namespace "Root\SMS\site_TB1" -Query  ("select * from SMS_R_System where Name = '"+$Comp.Name+"' ") | %{

  if (!$_.Client -and !$_.Active)

  {

   $Comp.BadID=$_.ResourceId

   #$_.Delete()

   Write-Host "№"($meter++)" Computer " $Comp.Name "with ID="$_.ResourceId "Deleted!!!" -ForegroundColor Red

  }

  else

  {

   if ($Comp.LastDate)

   {

    if ($_.SMSUUIDChangeDate -ge $Comp.LastDate)

    {

     Write-Host "---№"($meter2++)" Computer " $Comp.Name "with ID="$Comp.BadID "Deleted!!!" -ForegroundColor Red

    }

    else

    {

     Write-Host "---№"($meter2++)" Computer " $Comp.Name "with ID="$_.ResourceId "Deleted!!!" -ForegroundColor Red

    }

   }

   else

   {

    $Comp.LastDate=[double](($_.SMSUUIDChangeDate).split("."))[0]

    $Comp.BadID=$_.ResourceId

   }

  }

   

 }

}

#$ArrayComps | ?{!$_.BadID}