Manually run partitioning and grooming on SCOM database

The following query if run on the SCOM db will run the grooming on all the partition tables. There are 122 partition tables in SCOM 2012. This will run for each one of them as their Iscurrent value is set to 1.

/*-------------------------------*/

declare @counter int  set @counter = 0  while @counter < 122

begin

exec p_PartitioningAndGrooming

set @counter = @counter + 1

print 'The counter is ' + cast(@counter as char)

end

/*-----------------------------*/

Change authentication account in NTLM Webapplication

#Usage:.Change-NTLMAccount -rms "rms.contoso.com" -currentaccount 'curracct' -newAccount 'newacct'

param([string] $rms,$currentAccount,$newAccount)

function DisplayUpdate ($str, $color = "white") {
$now = [DateTime]::Now

Write-Host "[$now]" -ForegroundColor white -NoNewline
Write-Host " $str`r" -ForegroundColor $color
}

$scriptPath = split-path -parent $MyInvocation.MyCommand.Path
$managementGroupRmsName = $rms
$newActionAccountName = $newAccount
#Load Dll's
Displayupdate "Loading SDK dll's"
$void1 = [System.Reflection.Assembly]::LoadFrom("$scriptPathMicrosoft.EnterpriseManagement.Core.dll")
if(!$void1) {Displayupdate "DLL's not found..exiting..." "red";break;}
$void2 = [System.Reflection.Assembly]::LoadFrom("$scriptPathMicrosoft.EnterpriseManagement.OperationsManager.dll")
if(!$void2) {Displayupdate "DLL's not found..exiting..." "red";break;}
$void3 = [System.Reflection.Assembly]::LoadFrom("$scriptPathMicrosoft.EnterpriseManagement.Runtime.dll")
if(!$void3) {Displayupdate "DLL's not found..exiting..." "red";break;}
$mg = New-Object Microsoft.EnterpriseManagement.ManagementGroup($managementGroupRmsName)
Displayupdate "connected to MG:$Mg.name"
Displayupdate "Getting mps.."
$mps = $mg.getmanagementpacks() | where{$_.name -match "NTLM"}
if($mps.count -gt 0 ) {
$count = $mps.count
Displayupdate "[$count] number of mp's found" "yellow"
$healthServices = dir -R | where {$_.UniquePathName -like "Microsoft.SystemCenter.HealthService*"}
$Srs = $mg.getmonitoringSecurereferences() | where{$_.name -match "WebApplication"}
$healthServices = dir -R | where {$_.UniquePathName -like "Microsoft.SystemCenter.HealthService*"}
$newActionAccount = $mg.GetMonitoringSecureData() | where {$_.UserName -eq $newActionAccountName -and $_.Type -eq "Windows" -and $_.name -notmatch "Data Warehouse Report Deployment Account"}

foreach($sr in $Srs)
{
$secureDataHSRefs = $mg.GetMonitoringSecureDataHealthServiceReferenceBySecureReferenceId($sr.Id)
foreach($secureDataHSRef in $secureDataHSRefs){
$healthService = $mg.GetMonitoringObject($secureDataHSRef.HealthServiceId)
$currentActionAccount = $mg.GetMonitoringSecureData($secureDataHSRef.MonitoringSecureDataId)
DisplayUpdate "Changing Action Account on $($sr.GetManagementpack().Displayname)" "yellow"
$secureDataHSRef.MonitoringSecureDataId = $newActionAccount.Id
$secureDataHSRef.Update()
}
}
}

else
{
Displayupdate "No NTLM mp's found..exiting.." "red"
break;
}

DisplayUpdate "Script finished"

My frequently used SCOM Queries

I won't take the credit for most of these queries. I have compiled them overtime from different blogs, sites

/* Database size */

SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;.

/*PERFORMANCE related queries*/

select top 20 pcv.ObjectName, pcv.CounterName, count (pcv.countername) as Total from performancedataallview as pdv, performancecounterview as pcv where (pdv.performancesourceinternalid = pcv.performancesourceinternalid) group by pcv.objectname, pcv.countername order by count (pcv.countername) desc

select top 2000 Path,ObjectName, count(ObjectName) from PerformanceDataAllView pdv with (NOLOCK) inner join PerformanceCounterView pcv with (NOLOCK) on pdv.performancesourceinternalid = pcv.performancesourceinternalid inner join BaseManagedEntity bme with (NOLOCK) on pcv.ManagedEntityId = bme.BaseManagedEntityId where  pcv.ObjectName like 'Processor' group by Path,ObjectName order by count(ObjectName) desc

 

/* Top Noisy Rules in the last 24 hours */

select ManagedEntityTypeSystemName, DiscoverySystemName, count(*) As 'Changes' from (select distinct MP.ManagementPackSystemName, MET.ManagedEntityTypeSystemName, PropertySystemName, D.DiscoverySystemName, D.DiscoveryDefaultName, MET1.ManagedEntityTypeSystemName As 'TargetTypeSystemName', MET1.ManagedEntityTypeDefaultName 'TargetTypeDefaultName', ME.Path, ME.Name, C.OldValue, C.NewValue, C.ChangeDateTime from dbo.vManagedEntityPropertyChange C inner join dbo.vManagedEntity ME on ME.ManagedEntityRowId=C.ManagedEntityRowId inner join dbo.vManagedEntityTypeProperty METP on METP.PropertyGuid=C.PropertyGuid inner join dbo.vManagedEntityType MET on MET.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId inner join dbo.vManagementPack MP on MP.ManagementPackRowId=MET.ManagementPackRowId inner join dbo.vManagementPackVersion MPV on MPV.ManagementPackRowId=MP.ManagementPackRowId left join dbo.vDiscoveryManagementPackVersion DMP on DMP.ManagementPackVersionRowId=MPV.ManagementPackVersionRowId AND CAST(DefinitionXml.query('data(/Discovery/DiscoveryTypes/DiscoveryClass/@TypeID)') AS nvarchar(max)) like

'%'+MET.ManagedEntityTypeSystemName+'%' left join dbo.vManagedEntityType MET1 on MET1.ManagedEntityTypeRowId=DMP.TargetManagedEntityTypeRowId left join dbo.vDiscovery D on D.DiscoveryRowId=DMP.DiscoveryRowId where ChangeDateTime > dateadd(hh,-24,getutcdate()) ) As #T group by ManagedEntityTypeSystemName, DiscoverySystemName order by count(*) DESC

and

/* Modified properties in the last 24 hours */

select distinct MP.ManagementPackSystemName, MET.ManagedEntityTypeSystemName, PropertySystemName, D.DiscoverySystemName, D.DiscoveryDefaultName, MET1.ManagedEntityTypeSystemName As 'TargetTypeSystemName', MET1.ManagedEntityTypeDefaultName 'TargetTypeDefaultName', ME.Path, ME.Name, C.OldValue, C.NewValue, C.ChangeDateTime from dbo.vManagedEntityPropertyChange C inner join dbo.vManagedEntity ME on ME.ManagedEntityRowId=C.ManagedEntityRowId inner join dbo.vManagedEntityTypeProperty METP on METP.PropertyGuid=C.PropertyGuid inner join dbo.vManagedEntityType MET on MET.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId inner join dbo.vManagementPack MP on MP.ManagementPackRowId=MET.ManagementPackRowId inner join dbo.vManagementPackVersion MPV on MPV.ManagementPackRowId=MP.ManagementPackRowId left join dbo.vDiscoveryManagementPackVersion DMP on DMP.ManagementPackVersionRowId=MPV.ManagementPackVersionRowId     AND CAST(DefinitionXml.query('data(/Discovery/DiscoveryTypes/DiscoveryClass/@TypeID)') AS nvarchar(max)) like

'%'+MET.ManagedEntityTypeSystemName+'%' left join dbo.vManagedEntityType MET1 on MET1.ManagedEntityTypeRowId=DMP.TargetManagedEntityTypeRowId left join dbo.vDiscovery D on D.DiscoveryRowId=DMP.DiscoveryRowId where ChangeDateTime > dateadd(hh,-24,getutcdate()) ORDER BY MP.ManagementPackSystemName, MET.ManagedEntityTypeSystemName

/*Discovery frequencies in the management packs*/

select       ds.DiscoveryName,       mp.MpName,       CAST(md.ModuleConfiguration as XML).value('/Frequency[1]','int') as 'Frequency' from Module (nolock) as md inner join Discovery (nolock) as ds       on md.ParentId = ds.DiscoveryId inner join ManagedType (nolock) as mt       on mt.ManagedTypeId = ds.DiscoveryTarget inner join ManagementPack (nolock) as mp       on mp.ManagementPackId = ds.ManagementPackId

Who is connected to my SCOM console

The following command will help to find who is connected to your SCOM console. This works for SCOM 2012.

(Get-SCOMManagementGroup).getconnectedusernames()

For SCOM 2007 you will have to use the ones below. I have not tested the below commands, though.

Get-ManagementGroupConnection | foreach-object {$_.ManagementGroup.getConnectedUserNames()}
Get-ManagementGroupConnection | foreach-object {$_.ManagementGroup.getConnectedUserNames()} | measure-object

 

Adding or removing a new SCOM zone from Om12 agent

Using Powershell:

Remove Management group

$OMCfg = New-Object -ComObject AgentConfigManager.MgmtSvcCfg;

$OMCfg.RemoveManagementGroup("zonetoberemoved")

 

Add management group

$OMCfg = New-Object -ComObject AgentConfigManager.MgmtSvcCfg;

$OmCfg.AddmanagementGroup(‘zonenametobeadded’,'MSNamewithFQCN',5723)

Run command promt as sytem user

The following link helps for opening command prompt as a system user.

http://verbalprocessor.com/2007/12/05/running-a-cmd-prompt-as-local-system/

Running a powershell script as a Scheduled Task

I found this link to run a powershell script using the Task Scheduler.

http://techhead.co/using-task-scheduler-to-run-a-powershell-script/

To create a simple task to copy a directory from one location to another.Open task scheduler and click Create Basic task.Give the name as Copy task. You can specify and select whether you want to run the task only when you login or keep it running whether a user logs in or not. Once that is done then click the Actions tab and click new. Let the Action be as Start a program. In the Settings text box enter this.

%windir%\System32\WindowsPowerShell\v1.0\powershell.exe

And in the Add arguments enter this after replacing the script path.

–Noninteractive –Noprofile –Command "&{Path to your Script}"

Open notepad and edit the Script file. Enter these lines and save it.

copy-item c:\yourfolderpath -destination c:\destinationpath -recurse

You can start the task and check if the copy is happening. Check the folder permissions and don't add any spaces in the directory names if your copy is not working.