Script Displaying All Deployments for a Computer Asset

    You might have a need to identify any possible deployments, that are assigned to a particular machine in SCCM. This script will create a list of such deployments and dump it into a .csv file

Usage:

 

    Save the code below to a .ps1 file. Replace the following strings:

1. SMS_Provider_Server_FQDN - replace with an FQDN of you SMS Provider server

2. root\SMS\Site_XXX - replace the XXX with your SCCM Site code

    If you wish to add additional filter you can add additional condition here (replace XXX with the pattern you want to look up):

$Deployments = (Get-WmiObject @ConnectionObj -Query "Select * From SMS_DeploymentInfo WHERE CollectionID='$($Collection.CollectionID)' AND TargetName LIKE '%XXX%'")

 

    If you want to change the output location change the directory and a file name in the last line of the script.

Additional Notes:
 

    The script is a remake of another script found on the Internet. The original idea of the script dumping User deployments to the screen was adapted to the needs of the organization.

Code:

$ConnectionObj = @{

    ComputerName = "SMS_Provider_Server_FQDN"

    NameSpace = "root\SMS\Site_XXX"

}

$Array = @()

$Computer = Get-WMIObject @ConnectionObj -Query "Select * From SMS_R_System WHERE Name = 'XXX'"

$Collections = Get-WmiObject -Class sms_fullcollectionmembership @ConnectionObj -Filter "ResourceID = '$($Computer.resourceid)'"

Foreach ($Collection in $collections)

{                 

    $Deployments = (Get-WmiObject @ConnectionObj -Query "Select * From SMS_DeploymentInfo WHERE CollectionID='$($Collection.CollectionID)'")

    Foreach ($Deploy in $Deployments)

    {

        $Properties = @{

            ComputerName = $Computer.Name

            CollectionName = $Deploy.CollectionName

            CollectionID = $Deploy.CollectionID

            DeploymentID = $Deploy.DeploymentID

            DeploymentName = $Deploy.DeploymentName

            ApplicationName = $Deploy.TargetName

            ApplicationSubName = $Deploy.TargetSubname

        }       

        $Array += $(New-Object -TypeName PSObject -prop $Properties)

    }

}

$Array | Export-CSV -NoTypeInformation "C:\Temp\Applications.csv"

Advanced Troubleshooting - NLB Address for SCOM Console

Symptoms:

    After the installation of a brand new SCOM environment one of the lasts steps before handing the system over to the production was to ensure, that the SCOM Console is available for all the users via Network Load Balancing address in both Web and thick client versions. The Active Directory team set up appropriate DNS entry and the Network team set up the load balancing....

    After the configuration was completed it turned out, that even though the web console was working properly the application one behaved very strange. It was connecting properly from any place to the direct servers' FQDN addresses without any authentication prompt, but when directed to the NLB address it didn't recognize the user account and was asking for credentials. When provided with the valid ones it was throwing the following error:

Operations Manager Console error when connecting via NLB address

    The whole output of the error message is below:

====================================================

Date: 12/8/2016 5:19:09 PM
Application: Operations Manager
Application Version: 7.0.9538.1136
Severity: Error
Message: Failed to connect to server 'XXX'

Microsoft.EnterpriseManagement.Common.ServerDisconnectedException: The client has been disconnected from the server. Please call ManagementGroup.Reconnect() to reestablish the connection. ---> System.ServiceModel.ProtocolException: You have tried to create a channel to a service that does not support .Net Framing. It is possible that you are encountering an HTTP endpoint. ---> System.IO.InvalidDataException: Expected record type 'PreambleAck', found '72'.
   --- End of inner exception stack trace ---

Server stack trace:
   at System.ServiceModel.Channels.FramingDecoder.ValidatePreambleAck(FramingRecordType foundType)
   at System.ServiceModel.Channels.ClientDuplexDecoder.Decode(Byte[] bytes, Int32 offset, Int32 size)
   at System.ServiceModel.Channels.ConnectionUpgradeHelper.ValidateUpgradeResponse(Byte[] buffer, Int32 count, ClientFramingDecoder decoder)
   at System.ServiceModel.Channels.ConnectionUpgradeHelper.InitiateUpgrade(StreamUpgradeInitiator upgradeInitiator, IConnection& connection, ClientFramingDecoder decoder, IDefaultCommunicationTimeouts defaultTimeouts, TimeoutHelper& timeoutHelper)
   at System.ServiceModel.Channels.ClientFramingDuplexSessionChannel.SendPreamble(IConnection connection, ArraySegment`1 preamble, TimeoutHelper& timeoutHelper)
   at System.ServiceModel.Channels.ClientFramingDuplexSessionChannel.DuplexConnectionPoolHelper.AcceptPooledConnection(IConnection connection, TimeoutHelper& timeoutHelper)
   at System.ServiceModel.Channels.ConnectionPoolHelper.EstablishConnection(TimeSpan timeout)
   at System.ServiceModel.Channels.ClientFramingDuplexSessionChannel.OnOpen(TimeSpan timeout)
   at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
   at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)
   at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.CallOnceManager.CallOnce(TimeSpan timeout, CallOnceManager cascade)
   at System.ServiceModel.Channels.ServiceChannel.EnsureOpened(TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
   at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.EnterpriseManagement.Common.Internal.IDispatcherService.Connect(SdkClientConnectionOptions connectionOptions)
   at Microsoft.EnterpriseManagement.Common.Internal.SdkDataLayerProxyCore.Initialize(EnterpriseManagementConnectionSettings connectionSettings, SdkChannelObject`1 channelObjectDispatcherService)
   at Microsoft.EnterpriseManagement.Common.Internal.SdkDataLayerProxyCore.CreateEndpoint[T](EnterpriseManagementConnectionSettings connectionSettings, SdkChannelObject`1 channelObjectDispatcherService)
   --- End of inner exception stack trace ---
   at Microsoft.EnterpriseManagement.Common.Internal.ExceptionHandlers.HandleChannelExceptions(Exception ex)
   at Microsoft.EnterpriseManagement.Common.Internal.SdkDataLayerProxyCore.CreateEndpoint[T](EnterpriseManagementConnectionSettings connectionSettings, SdkChannelObject`1 channelObjectDispatcherService)
   at Microsoft.EnterpriseManagement.Common.Internal.SdkDataLayerProxyCore.ConstructEnterpriseManagementGroupInternal[T,P](EnterpriseManagementConnectionSettings connectionSettings, ClientDataAccessCore clientCallback)
   at Microsoft.EnterpriseManagement.Common.Internal.SdkDataLayerProxyCore.RetrieveEnterpriseManagementGroupInternal[T,P](EnterpriseManagementConnectionSettings connectionSettings, ClientDataAccessCore callbackDispatcherService)
   at Microsoft.EnterpriseManagement.Common.Internal.SdkDataLayerProxyCore.Connect[T,P](EnterpriseManagementConnectionSettings connectionSettings, ClientDataAccessCore callbackDispatcherService)
   at Microsoft.EnterpriseManagement.ManagementGroup.InternalInitialize(EnterpriseManagementConnectionSettings connectionSettings, ManagementGroupInternal internals)
   at Microsoft.EnterpriseManagement.Mom.Internal.UI.Common.ManagementGroupSessionManager.Connect(String server, String username, SecureString password, String domain)
   at Microsoft.EnterpriseManagement.Monitoring.Console.Internal.ConsoleWindowBase.ConnectWithCredentials(Exception ex, ConsoleJobEventArgs args)
System.ServiceModel.ProtocolException: You have tried to create a channel to a service that does not support .Net Framing. It is possible that you are encountering an HTTP endpoint. ---> System.IO.InvalidDataException: Expected record type 'PreambleAck', found '72'.
   --- End of inner exception stack trace ---

Server stack trace:
   at System.ServiceModel.Channels.FramingDecoder.ValidatePreambleAck(FramingRecordType foundType)
   at System.ServiceModel.Channels.ClientDuplexDecoder.Decode(Byte[] bytes, Int32 offset, Int32 size)
   at System.ServiceModel.Channels.ConnectionUpgradeHelper.ValidateUpgradeResponse(Byte[] buffer, Int32 count, ClientFramingDecoder decoder)
   at System.ServiceModel.Channels.ConnectionUpgradeHelper.InitiateUpgrade(StreamUpgradeInitiator upgradeInitiator, IConnection& connection, ClientFramingDecoder decoder, IDefaultCommunicationTimeouts defaultTimeouts, TimeoutHelper& timeoutHelper)
   at System.ServiceModel.Channels.ClientFramingDuplexSessionChannel.SendPreamble(IConnection connection, ArraySegment`1 preamble, TimeoutHelper& timeoutHelper)
   at System.ServiceModel.Channels.ClientFramingDuplexSessionChannel.DuplexConnectionPoolHelper.AcceptPooledConnection(IConnection connection, TimeoutHelper& timeoutHelper)
   at System.ServiceModel.Channels.ConnectionPoolHelper.EstablishConnection(TimeSpan timeout)
   at System.ServiceModel.Channels.ClientFramingDuplexSessionChannel.OnOpen(TimeSpan timeout)
   at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
   at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)
   at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.CallOnceManager.CallOnce(TimeSpan timeout, CallOnceManager cascade)
   at System.ServiceModel.Channels.ServiceChannel.EnsureOpened(TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
   at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.EnterpriseManagement.Common.Internal.IDispatcherService.Connect(SdkClientConnectionOptions connectionOptions)
   at Microsoft.EnterpriseManagement.Common.Internal.SdkDataLayerProxyCore.Initialize(EnterpriseManagementConnectionSettings connectionSettings, SdkChannelObject`1 channelObjectDispatcherService)
   at Microsoft.EnterpriseManagement.Common.Internal.SdkDataLayerProxyCore.CreateEndpoint[T](EnterpriseManagementConnectionSettings connectionSettings, SdkChannelObject`1 channelObjectDispatcherService)
System.IO.InvalidDataException: Expected record type 'PreambleAck', found '72'.

 ====================================================

Reason:

    The error message is very uncommon for SCOM configuration and I haven't found any trace of the similar error occurring in regards to the SCOM Console application. There is no further need to any configuration on the SCOM side of things in order to set up Console NLB. Neither firewalls seemed to be a problem, as the same behavior occurred locally on the server and moreover there was no trace of dropped traffic in the firewall logs system. However looking up particular parts of the error message provided some traces - apparently in some cases error message was caused by improper port redirection, for instance when outgoing port was different from the incoming one. That lead to the configuration of the NLB settings, which turned out to be a culprit

Resolution:

    After taking a closer look at the configuration of NLB it turned out, that even though:

1. Traffic IP group was set up correctly and was pointing to a proper NLB address
2. The Pool was configured correctly and contained all the SCOM server nodes inside
3. The Virtual Server was configured to forward port 5724

    There was a problem in the setup of Virtual Server. In general this configuration item looks as follows:

Problematic Configuration of NLB Virtual Server for SCOM Console Application

    As you can see, even though the forwarded port is properly setup to 5724, the Protocol is incorrectly configured as HTTP. It turned out this was the source of the problem. It was fixed by setting it to Generic Client First, which caused the error message to go away.

Troubleshooting - Microsoft KB3192391 crashing SCOM console

Symptoms:

    The SCOM console suddenly started crashing across the whole organization and it was not available for any of the users. It happens on all the computers - including end user workstations as well as the consoles ran directly on the Management Servers. In some cases application crashed right away after starting, in some other it was possible to browse a little bit through the menu and it would crash or freeze after a few clicks. When this happens you might observe all or some of the following error messages in the Application Event Viewer logs:

====================================================

Faulting application name: Microsoft.EnterpriseManagement.Monitoring.Console.exe, version: 7.0.9538.1123, time stamp: 0x54314363
Faulting module name: clr.dll, version: 4.0.30319.36365, time stamp: 0x579fd37e
Exception code: 0xc0000005
Fault offset: 0x00000000000919df
Faulting process id: 0x115c4
Faulting application start time: 0x01d24180b6dc8014
Faulting application path: C:\Program Files\System Center Operations Manager 2012\Console\Microsoft.EnterpriseManagement.Monitoring.Console.exe
Faulting module path: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\clr.dll
Report Id: 35f64cec-ad74-11e6-b4f6-0030846dd8a4
Application: Microsoft.EnterpriseManagement.Monitoring.Console.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an internal error in the .NET Runtime at IP 000007FEF33019DF (000007FEF3270000) with exit code 80131506.

 ====================================================

 The program Microsoft.EnterpriseManagement.Monitoring.Console.exe version 7.0.9538.1123 stopped interacting with Windows and was closed. To see if more information about the problem is available, check the problem history in the Action Center control panel.
 Process ID: 121b0
 Start Time: 01d2418008176db6
 Termination Time: 27
 Application Path: C:\Program Files\System Center Operations Manager 2012\Console\Microsoft.EnterpriseManagement.Monitoring.Console.exe
 Report Id: f104753a-ad73-11e6-b4f6-0030846dd8a4

The protocol host process 21628 did not respond and is being forcibly terminated {filter host process 67228}.

====================================================

 Faulting application name: Microsoft.EnterpriseManagement.Monitoring.Console.exe, version: 7.0.9538.1136, time stamp: 0x559bf219

Faulting module name: KERNELBASE.dll, version: 6.1.7601.23543, time stamp: 0x57d2fe27
Exception code: 0xe0434352
Fault offset: 0x000000000001a06d
Faulting process id: 0x13a2c
Faulting application start time: 0x01d241a75ac7a3f6
Faulting application path: C:\Program Files\System Center Operations Manager 2012\Console\Microsoft.EnterpriseManagement.Monitoring.Console.exe

Faulting module path: C:\Windows\system32\KERNELBASE.dll
Report Id: dedc128e-ad9a-11e6-b4f6-0030846dd8a4

==================================================== 

Reason:

    It turns out, that this issue started approximately at the same moment, at which October 2016 cumulative updates for Windows have been deployed to the servers and workstations. The cause of the error is the installation of Security Update for Microsoft Windows (KB3192391), which has been confirmed by Microsoft here:

https://support.microsoft.com/en-us/kb/3200006


Resolution:

    In order to fix the problem you have to download and install the fix from the Microsoft Update Catalog web page:

http://www.catalog.update.microsoft.com/Search.aspx?q=3200006

Additional Notes:

     The version of the installed fix has to match the version of the operating system on which the SCOM console application is crashing

Script Inventorying Web Application Transaction Monitors' settings

    With the extensive usage of Web Application Transaction Monitors you might face a challenge of inventorying settings in order to keep them in one clear file in human readable format. I tried to approach the problem with both Operations Manager Shell and T-SQL query on the operations Manager DB, but seems like none of the information I was interested in was available in there. Therefore I came up with an idea of parsing .xml file created by export of Management Pack containing the Web Application Transaction Monitor objects. The script collects the following settings from the .xml file:

Name - containing the name of the Web Application Transaction Monitor displayed in SCOM
GUID - containing the GUID of the Web Application Transaction Monitor object
AuthenticationScheme - containing information about the way Watcher Nodes authenticate against the URL (eg. None, NTLM, Negotiate and so on)
WatcherNodesList - containing the list of FQDNs of watcher nodes (each watcher node has it's own entry under this parent property)
URLsList - containing the list of FQDNs of monitored URLs (each URL has it's own entry under this parent property)

    This data is published in the output .xml file. It looks as follows:

Web Application Transaction Monitor .xml parser output



Usage:

   

    Save the code below into the .ps1 file. You have to provide the location of the Management Pack file exported in .xml format in this line:

$MPXMLLocation = "C:\Temp\xxx.xml"

     You also need to provide the file name and the location of the output file here:

 $OutputXMLLocation = "C:\Temp\xxxReport.xml"

    Additional field to modify in the script is the one below, where you need to provide the search expresssion, if you would like to scope the report only to the applications matching it:

$ApplicationNames = $QoSMP.ManagementPack.Monitoring.Discoveries.Discovery | ?{$_.DataSource.DisplayName -match "*XXXXX*"}

    Otherwise you can modify the line to the following one:

$ApplicationNames = $QoSMP.ManagementPack.Monitoring.Discoveries.Discovery | ?{$_.DataSource.DisplayName -match "*"}

Additional Notes:

    The script can be easily modified in order to add any other properties that are of one's interest. It would require the addition of more lines in the section including Add-Member cmdlets

Code:

$MPXMLLocation = "C:\Temp\xxx.xml"
$OutputXMLLocation = "C:\Temp\xxxReport.xml"


[xml]$QoSMP = Get-Content $MPXMLLocation
$WebApplications = @()

$ApplicationNames = $QoSMP.ManagementPack.Monitoring.Discoveries.Discovery | ?{$_.DataSource.DisplayName -match "*XXXXX*"}

foreach ($ApplicationName in $ApplicationNames) {
    $object = New-Object -TypeName PSObject
    $Object | Add-Member -Name "Name" -MemberType Noteproperty -Value $ApplicationName.DataSource.DisplayName
    $Object | Add-Member -Name "GUID" -MemberType Noteproperty -Value $ApplicationName.ID.Substring(0,$ApplicationName.ID.IndexOf("."))
    $Object | Add-Member -Name "AuthenticationScheme" -MemberType Noteproperty -Value ""
    $Object | Add-Member -Name "WatcherNodesList" -MemberType Noteproperty -Value ($ApplicationName.DataSource.WatcherComputersList -replace '[()]','' -split "\|")
    $Object | Add-Member -Name "URLsList" -MemberType Noteproperty -Value ""
    $WebApplications += $object
}

foreach ($DataSourceModuleType in $QoSMP.ManagementPack.TypeDefinitions.ModuleTypes.DataSourceModuleType){
    foreach ($Application in $WebApplications){
        IF ($Application.GUID -match $DataSourceModuleType.ID.Substring(0,$DataSourceModuleType.ID.IndexOf("."))){
            $Application.AuthenticationScheme = $DataSourceModuleType.ModuleImplementation.Composite.MemberModules.ProbeAction.AuthenticationScheme
            foreach ($Request in $DataSourceModuleType.ModuleImplementation.Composite.MemberModules.ProbeAction.Requests.Request){
            $Application.URLsList += $Request.URL + "|"
        }
            $Application.URLsList = $Application.URLsList.SubString(0,$Application.URLsList.Length-1)
            $Application.URLsList = $Application.URLsList -split "\|"
        }
    }
}

($WebApplications | ConvertTo-XML -NoTypeInformation).Save($OutputXMLLocation)

Monitor Serial Number Report for SCCM 2012

    It is possible, however it is not a straight-forward task, to report on the serial number of the screens attached to the computers in your organization. It is done with the usage of WmiMonitorID Hardware Inventory Class, which resides in root\wmi namespace of the computer. You can review the entries present at the computer with the following command:

wmic /namespace:\\root\wmi PATH WmiMonitorID GET * /FORMAT:list

    You will receive the output similar to the following one:

wmic GET WmiMonitorID output



    The property, that is of our interest here obviously is mainly the SerialNumberID one, but you can make use of all the other ones if you find it beneficial for your organization's needs. As you will probably notice unfortunately the data stored in this WMI class is not in human-readable format, therefore it will need to be converted on the T-SQL query level in order to make a nice report out of it. It can be done with the use of the T-SQL query provided below 

Usage:

    First step is to modify Hardware Inventory Client Settings. In order to do so, you have to go to Administration pane in SCCM Console, choose the Default Settings and then click on the Set Classes ... button

    Next click on the Add ... button in order to add the new class. Then Connect ... and type in the root\wmi namespace to connect to.


    From the list choose the wmiMonitorId class and click on OK button.

    The class will appear now on the list of classes available for Hardware Inventory

    The next step depends on your intent. You can tick the box in here, and thus start collecting this WMI class from all the systems in your organization right away. I would however recommend to leave it unticked here and create additional Custom Client Device Settings, where you add this particular class to Hardware Inventory process. Then deploy this custom setting to the collection of computers, that you would like to obtain the serial numbers for. Next step is where the T-SQL query comes in play. With the use of Report Builder installed on your SCCM server you can. again depending on your needs, either create a brand new report, or, which is what I decided to do, use one of the built-in Microsoft reports, preferable the one with a drop-down list of device collections and swap the T-SQL query with the one shown below. If you choose to do the later remember to modify the description and tile fields as well as all the sorting rules/names of the data fields and al the other references to the values from the old report you decided to use as a template. The outcome looks as follows:

 

Monitor Details report in action

    Below you can find the code to put in two Datasets, that will be necessary to be included in the SCCM report

Additional Notes:

    I am not the author of the original version of the T-SQL query provided, meaning mainly the conversion logic. It had to be re-written though, as when implemented in real-life scenario it turned out, that many monitors store all the data shown on the first screen directly in human-readable format, which is where conversion failed leaving empty fields in the report. I included few CASE statements to mitigate the problem. It has been implemented and tested, and it works properly in the form provided. The printscreens provided show the real life implementation's outcome

Code:

DataSet0

SET NOCOUNT ON
DECLARE @input nvarchar(1023)
DECLARE @result nvarchar(1023)
-- split and conversion logic
 DECLARE @convCode nvarchar(1023)
 SET @convCode = N'declare @indx int; declare @valToConvert varchar(4); '
 + CHAR(13) + N'set @result='''''
 + CHAR(13) + N'while LEN(@input) > 0 begin '
 + CHAR(13) + N'select @indx = CHARINDEX('','', @input) '
 + CHAR(13) + N'select @valToConvert = SUBSTRING(@input, 0, @indx)'
 + CHAR(13) + N'if (@valToConvert = ''0'') OR (@valToConvert = '''') break'
 + CHAR(13) + N'select @result = @result + CHAR(@valToConvert) '
 + CHAR(13) + N'select @input = SUBSTRING(@input, @indx+2, LEN(@input) - @indx) end'
 DECLARE @params nvarchar(500)
 SET @params = N'@input varchar(255), @result varchar(255) OUTPUT'
-- table variable
 DECLARE @convertTab table (
    ResourceID int,
    Active0 int,
    InstanceName0 nvarchar(255),
    ManufacturerName0 nvarchar(255),
    ProductCodeID0 nvarchar(255),
    SerialNumberID0 nvarchar(255),
    UserFriendlyName0 nvarchar(255),
    UserFriendlyNameLength0 int,
    WeekOfManufacture0 int,
    YearOfManufacture0 int,
    ManufacturerNameConv varchar(255),
    ProductCodeIDConv varchar(255),
    SerialNumberIDConv varchar(255),
    UserFriendlyNameConv varchar(255)
 )
 -- select data to report on, into the table variable
 INSERT @convertTab
    (ResourceID, InstanceName0, ManufacturerName0, ProductCodeID0, SerialNumberID0,
    UserFriendlyName0, UserFriendlyNameLength0, WeekOfManufacture0, YearOfManufacture0)
 SELECT
    ResourceID, InstanceName0, ManufacturerName0, ProductCodeID0, SerialNumberID0,
    UserFriendlyName0, UserFriendlyNameLength0, WeekOfManufacture0, YearOfManufacture0
 FROM v_GS_WMIMONITORID
 WHERE ResourceID in
    (SELECT ResourceID FROM v_FullCollectionMembership WHERE CollectionID = @CollID)
-- Cursor to iterate through table variable and convert
 DECLARE convert_cursor CURSOR FOR
 SELECT ManufacturerName0, ProductCodeID0, SerialNumberID0,UserFriendlyName0 FROM @convertTab
 DECLARE @mfg varchar(255), @pcode varchar(255), @snum varchar(255), @fname varchar(255)
 DECLARE @out varchar(255)
OPEN convert_cursor
 FETCH NEXT FROM convert_cursor INTO @mfg, @pcode, @snum, @fname
 WHILE @@FETCH_STATUS = 0
 BEGIN
    EXEC sp_executesql @convCode, @params, @input=@mfg, @result=@out OUTPUT
    UPDATE @convertTab SET ManufacturerNameConv = @out WHERE ManufacturerName0 = @mfg
    EXEC sp_executesql @convCode, @params, @input=@pcode, @result=@out OUTPUT
    UPDATE @convertTab SET ProductCodeIDConv = @out WHERE ProductCodeID0 = @pcode
    EXEC sp_executesql @convCode, @params, @input=@snum, @result=@out OUTPUT
    UPDATE @convertTab SET SerialNumberIDConv = @out WHERE SerialNumberID0 = @snum
    EXEC sp_executesql @convCode, @params, @input=@fname, @result=@out OUTPUT
    UPDATE @convertTab SET UserFriendlyNameConv = @out WHERE UserFriendlyName0 = @fname
    FETCH NEXT FROM convert_cursor into @mfg, @pcode, @snum, @fname
END
CLOSE convert_cursor
DEALLOCATE convert_cursor
SET NOCOUNT OFF
-- Return converted data
SELECT
 syst.Name0,
 CASE WHEN cnvt.UserFriendlyNameConv = ''
  THEN cnvt.UserFriendlyName0
  ELSE cnvt.UserFriendlyNameConv
 END AS UserFriendlyName,
 CASE WHEN cnvt.ManufacturerNameConv = ''
  THEN cnvt.ManufacturerName0
  ELSE cnvt.ManufacturerNameConv
 END AS ManufacturerName,  
 CASE WHEN cnvt.ProductCodeIDConv = ''
  THEN cnvt.ProductCodeID0
  ELSE cnvt.ProductCodeIDConv
 END AS ProductCodeID,
 CASE WHEN cnvt.SerialNumberIDConv = ''
  THEN cnvt.SerialNumberID0
  ELSE cnvt.SerialNumberIDConv
 END AS SerialNumberID
FROM @convertTab cnvt
JOIN v_R_System syst ON cnvt.ResourceID = syst.ResourceID
ORDER BY SerialNumberIDConv DESC

DataSet1

SELECT DISTINCT CollectionID, Name FROM fn_rbac_Collection(@UserSIDs)  ORDER BY Name

SCOM Agents Migration to Another Management Group

    During your adventure with System Center Operations Manager you might stumble upon the situation where you will create a brand new SCOM infrastructure just aside the already existing one. It might also happen, that after completing this task you will face one more task - the migration of SCOM agents from old Management Group to the new one. The migration might be completed by running a script on each SCOM client individually - preferably with usage of SCCM deployment. The script contains also random seed in case you would like to, as I did, randomly spread the clients among two SCOM Management Servers. The code ensures 50%/50% division of the load, but it can be adjusted according to needs with a small script modification. The same could be done in case of different number of Management Servers.

Usage:

     Swap the following strings in the script below with the names from your SCOM infrastructure:

• _NewMGName_ - put the name of your new Management Group (the target one)
• _OldMGName_ -  put the name of your new Management Group (the target one)
• _NewMGServerFQDNName_1_ - put the FQDN of the first Management Server in the target Management Group
• _NewMGServerFQDNName_2_ - put the FQDN of the second Management Server in the target Management Group

     The code has to be saved as a .vbs file and run on the SCOM client either manually or for instance via SCCM deployment

Code:

Option Explicit
On Error Resume Next

Dim objMSConfig
Set objMSConfig = CreateObject("AgentconfigManager.MgmtSvcCfg")

Dim objMGNew
Dim objMGOld

Set objMGNew = objMSConfig.GetManagementGroup ("_NewMGName_")
If Err <> 0 Then
    Randomize
    If Rnd > 0.5 Then
        Call objMSConfig.AddManagementGroup ("_NewMGName_", "_NewMGServerFQDNName_1_",5723)
    Else
        Call objMSConfig.AddManagementGroup ("_NewMGName_", "_NewMGServerFQDNName_2_",5723)
    End If
End If

Call objMSConfig.RemoveManagementGroup ("_OldMGName_")

Call objMSConfig.ReloadConfiguration

Log Files Sanitizer

    From time to time you might have a need to send the log files to the supplier support team, and you don't always want to give them away as they are. I have prepared a script, that is parsing the log files and detecting any IP addresses present, from the subnets starting with the first octets defined by you. You might put there any IP address range that is used in your organization and sanitize the log files this way. It is also possible to hide other information such as domain name or FQDNs just with a small modification to the script.

Usage:

    Put all your log files in one directory and save the code of the log parser as a .sp1 file in the same directory. Run the log parser from Administrator PowerShell and it will create new files for any log files, that had some IP addresses detected

Log Sanitizer output

Additional Notes:

    Be careful with *.evtx files, as they store IP addresses in a way, that there are spaces stored between each character (i.e. 1 0 . 1 . 1 2 2 . 1 3). This would not be detected by the log parser, so if you are exporting Windows Event Viewer logs for parsing ensure they are exported in the .csv format

Code:

$logfiles = Get-ChildItem ".\."
Write-Host
forEach ($log in $logfiles){
    Write-Host -f green "Parsing $log"
    $IPsMatched = 0
    Get-Content $log | ?{$_ -match '(?<IP>(10|172|255|192|)\.\d{1,3}\.\d{1,3} \.\d{1,3})'} | ForEach-Object {$IPsMatched++}
    if ($IPsMatched -gt 0){
        Write-Host "Found $IPsMatched IP addresses"
        $parsedLog
        $parsedLogName = $log.Name.Split('.')[0] + "parsed." + $log.Name.Split('.')[-1]
        (Get-Content $log) -replace '(10|172|255|192)\.\d{1,3}\.\d{1,3}\.\d{1,3}','X.X.X.X' | Set-Content $parsedLogName
        Write-Host "Sanitized log file written into " -nonewline; Write-Host -f yellow $parsedLogName
    }
    else {
        Write-Host "No IP addresses found"
    }
    Write-Host
}