SCOM - remove subscriber from all subscriptions and delete subscriber

when trying to delete a subscriber, you may get an error that the subscriber is still in use. but there is no quick and simple way to view in the SCOM console the memberships for the subscriber....


but there is a simple powershell script that will provide that information.


let's connect to SCOM (I prefer to use the Powershell_ISE console.... ) first:


$MG= "YourManagementServerFQDN"
$Module = get-module|where {$_.Name -match "OperationsManager"}
if (!($Module)){
    Write-Host "Import OperationsManager Module"
    import-module OperationsManager
}
Write-Host "Connecting to SCOM Management Group"
$ManagementServer = New-Object Microsoft.EnterpriseManagement.ManagementGroup($MG)


the next step is to get all subscriptions, and then enumerate the members (and only show those that match the member you are looking for

$a = Get-SCOMNotificationSubscription

foreach ($b in $a)
{
       $ns = $b.DisplayName
       $b.ToRecipients | foreach { If ($_.Name -match "SubscriberName") { Write-Host $ns " --"                   $B.Enabled.ToString() } }
}


when you create a subscriber it automatically picks your domainname \username so in our environment I only search for the username - and I just test it first in the console in the subscriber pane.


but... powershell is to make life easy - so can even be a lot more simple.
In the script below it will find any subscription where the subscribername is found, and it then removes that subscriber from the ToRecipients, and updates the subscription.
and the last step is to remove the subscriber once it has been removed from all subscriptions !!


$a = Get-SCOMNotificationSubscription
$user = "SubscriberName"
foreach ($b in $a)
{
 $ns = $b.DisplayName
 $d = $Null
 foreach ($c in $b.ToRecipients)
 {
   If ($c.Name -match $user)
    {
        Write-Host $ns " -- " $B.Enabled.ToString()
        $d = $C
    }
 }
 if ($d -ne $null)
 {
    $b.ToRecipients.Remove($d)
    $b.Update()
 }
}
write-host "now we delete the subscriber $user"
$del = Get-SCOMNotificationSubscriber -Name "*$user"
Remove-SCOMNotificationSubscriber $del


That's it !

System Center Service Manager 2012 R2 CSV sync by using Import-SCSMClassInstance

Problem: import-SCSMinstance imports objects that are in a specified CSV file, but... it does not remove "obsolete" objects. - I want to really sync - load what is missing, and remove what is obsolete

 if you drop all objects and then import them again, objects get a new GUID, so you lose history for the objects that are imported again, so this is not a preferred solution.

here is how you truly sync (mirror) the objects - this example is a simple class only with displaynames of groups:

Create your CSV file, this should contain all the objects that should be in your class
in my example, I load them from a SQL database:

$query = "select last_name from ca_contact where contact_type=2308"
$connection = new-object system.data.sqlclient.sqlconnection($StrConn);
$adapter = new-object system.data.sqlclient.sqldataadapter ($query, $connection)
$set = new-object system.data.dataset
$adapter.Fill($set)
$table = new-object system.data.datatable
$table = $set.Tables[0]

this is collecting some group names into $table
then create the CSV file and I add the values also to array $csv (I  run a "-contains" against this array - which is not possible against the $table variable)

$csvfile = "C:\SCSMimport\USDGroups.csv"
$csv=@()
Foreach ($R in ($table))
{
     Add-Content $csvfile ($R.last_name)
     $csv += $name
}

get all your current objects loaded in SCSM:
$GrpClass = Get-SCSMClass -Name USDgroups
$all = Get-SCSMClassInstance -Class $GrpClass

and now we compare $all against the array that contains anything that should be in the group.
 
foreach ($obj in $all)
{    if ($csv -notcontains $obj.DisplayName)
    {        "obsolete group : " + $obj.DisplayName
        Remove-SCSMClassInstance -Instance $obj
     }
}  

So.. anything not in the CSV but still loaded in the class in SCSM is now removed from SCSM

and then the last step to ensure new objects are added:
Import-SCSMInstance -DataFileName $csvfile -FormatFileName 'C:\tools\SCSMimport\USDGroups.xml'

 that's it !
 

SQL maintenance - custom rebuild / reorganize

I found a while ago a SQL script which will do a custom rebuild/reorganize based on the fragmentation level of each table. which seems more intelligent than just rebuilding all indexes which can be quite time consuming on large databases.

however I also found out recently that the loop stopped because it was the victim of a deadlock, and therefore the command failed, and it did not continue. so I have added now a try catch block around the execution of the command.

the other part is that it does only select those with more than 1000 pages, so now I am running nearly the same commands, but now I select those with less than 1000 pages and more than 20% fragmentation and I just always rebuild them. the 20% is just a random picked number, and can be changed/updated to meet your requirements

use OperationsManager
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @pagecnt bigint
DECLARE @command nvarchar(4000);
DECLARE @pagelock int;
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag,
 page_count as pagecnt
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0 AND page_count > 1000;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag, @pagecnt;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
        SELECT @pagelock = allow_page_locks
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
 -- Only process indexes where page locks are allowed
  IF @pagelock = 1
  BEGIN;
 -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
     SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE; ' +
    N'UPDATE STATISTICS ' +  @schemaname + N'.' + @objectname + N' ' + @indexname +';'
        IF @frag >= 30.0
           SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        begin try
   EXEC (@command);
         PRINT N'Executed: ' + @command;
  end try
  begin catch
   PRINT N'Command failed: ' + @command;
  end catch
  END;
    END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag,
 page_count as pagecnt
INTO #work_to_do1
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 20.0 AND index_id > 0 AND page_count < 1000;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do1;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag, @pagecnt;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
        SELECT @pagelock = allow_page_locks
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
 -- Only process indexes where page locks are allowed
  IF @pagelock = 1
  BEGIN;
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        begin try
               EXEC (@command);
               PRINT N'Executed (small): ' + @command;
         end try
         begin catch
              PRINT N'Command failed (small): ' + @command;
          end catch
  END;
    END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do1;

GO 

OpsMgr 2012 - Subscriptions - selective enabling of subscriptions again

With most of the System Center Operations Manger (2012 and 2007) updates, you have to disable all the subscriptions and enabled them later again.
the examples are for 2012, but just delete the "SCOM" from the poweshell commands, and it should run in 2007.


So disabling of all subscriptions can be done with a one liner in powershell:

Get-SCOMNotificationSubscription | Where {$_.Enabled  -eq $True} | Disable-SCOMNotificationSubscription

however in our environment, we have a lot of subscription, and there is also a number disabled. so enabling all the proper subscriptions again is a painfull slow excercise.... but not anymore....

this simple poweshell creates a logfile with all the subscriptions and their state (so run it before you disable the subscriptions.....)

$log = "D:\Tools\Powershell\subscriptions.txt"
set-content $log ""
$pcs = Get-SCOMNotificationSubscription
foreach ($pc in $pcs)
{
    [string]$entry = $pc.displayname + "," + $pc.enabled
 add-content $log $entry
}


and now when you're done with your tasks, and you want to enable your subscriptions again:

$log = "D:\Tools\Powershell\subscriptions.txt"
$file = get-content $log
foreach ($line in $file)
{
    if ($line -ne "")
    {
        $pc = $line.split(",")
        if ($pc[1] -eq "True")
        {
            Get-SCOMNotificationSubscription -DisplayName $pc[0] |Enable-SCOMNotificationSubscription
        }
    }
}

the script is a bit slow, so it will run for 15 minutes or more, but you can do something else

Regards,
Andre