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
Tuesday, August 20, 2013
Monday, August 19, 2013
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
(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
Friday, July 19, 2013
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)
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)
Subscribe to:
Posts (Atom)