Tuesday, August 20, 2013
My frequently used SCOM Queries
/* 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
Monday, August 19, 2013
Who is connected to my SCOM console
(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
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)
Friday, June 28, 2013
Run command promt as sytem user
http://verbalprocessor.com/2007/12/05/running-a-cmd-prompt-as-local-system/
Thursday, January 17, 2013
Running a powershell script as a Scheduled Task
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.
Monday, October 8, 2012
IE9 32-bit crashing.
Faulting application name: iexplore.exe, version: 9.0.8112.16450, time stamp: 0x503723f6
Faulting module name: unknown, version: 0.0.0.0, time stamp: 0x00000000
Exception code: 0xc00000fd
Fault offset: 0x739ae2d4
Faulting process id: 0x4ac
Faulting application start time: 0x01cda5cf238949d0
Faulting application path: C:\Program Files (x86)\Internet Explorer\iexplore.exe
Faulting module path: unknown
Report Id: 617f9fb0-11c2-11e2-b050-002713b3ec0b
After lot of surfing and reading through blogs. I stumbled on this solution myself. I was comparing the registry keys from my machine and another one which had IE-9 installed. I found that this key was different.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\UnattendBackup\ActiveSetup\Home_Page]. On my machine it was set to an intranet site of our organization.I changed that to http://www.google.com. The internet explorer started working again. On the first launch it still went to the intranet default site. But was not crashing anymore.
Sunday, September 23, 2012
SCOM 2007 authoring console crash
And this gave me a fair idea of what could be happening.My scom zone had many management packs loaded. By many i mean many, more that 500.So I just checked this reg key on another zone and the classes that were scoped in that. This console had only the root management server scoped. I copied the reg key from this RMS and imported on the problematic one. Woo hoo!! My console was not crashing anymore and it opened at the RMS scope here too. Here is the reg key.
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Microsoft Operations Manager\3.0\Console\Navigation\MonitoringConfigNavSettings] "SelectedViewNodeId"="91aaa624-bebf-21db-858c-76d50a6a8d99" "ScopedClasses"="1a9387f0-6fe5-5527-a2cb-73f2f6be6bc7" "AuthoringSpaceViewedFirstTime"=dword:00000000 "UseDefaultScoping"=dword:00000000
Thursday, June 7, 2012
Export all jobs from a SQL server
Open ssms,connect to your sql server.Navigate to SQL Server Agent-- jobs.
Click View and select Object Explorer Details or press F7. The Object explorer details window should open on the right. Select all the jobs,right click select Script job as-Create to -- File. Select a location and give any file name and save.
All the jobs would be exported as a single file and you can run that on any other database to restore all the jobs.
Saturday, April 21, 2012
Comparing two folders.
http://www.myitforum.com/articles/1/view.asp?id=10092
#usage = .\Compare-Items-in-two-directories.ps1 -path1 "e:\to write\dataondisk" -path2 "f:\dataoncd"
param([String] $path1,$path2)
$Diskdir = @()
$CDdir = @()
Get-childitem -Path$path1-recurse | %{$Diskdir=$Diskdir+$_.name}
$Diskdir.count Get-childitem -Path$path2-recurse | %{$CDdir=$CDdir+$_.name} $CDdir.count
$arraymatch= @()
$arraynotmatch= @()
foreach ($Itemin$Diskdir)
{
if($CDDir-contains$Item)
{
$arraymatch=$array+$item
}
else {
$arraynotmatch=$arraynotmatch+$item
}
}
$arraynotmatch
Thursday, March 1, 2012
How to open Event log of remote computer using powershell
If the following error is thrown.
Get-EventLog : The network path was not found.
Check if the remote registry service is started on the remote computer
Monday, February 13, 2012
Creating a library of scripts
I found this nice thread for invoking powershell scripts from another.
http://blogs.msdn.com/b/powershell/archive/2007/06/19/get-scriptdirectory.aspx
Since there is a script I keep calling for pulling the current data.
This came in useful. What I am doing is passing my other scripts a list of servers.
This list of servers keeps changing constantly and is updated into a SQL table. So every time I wanted a current list of servers I had to connect to SQL and pull the list manually
and present that to the script I wanted to run on that list.
So I found a good script which would allow me to query the table within power shell and pass that list to any other script.
I just add the following lines to the script I want to run on all the servers.
$ScriptDir = split-path -parent $MyInvocation.MyCommand.Path
. "$ScriptDir\Lib.ps1"
Where lib.ps1 is my script querying the database.
Access variables or data of one script from another.
First script stored at C:\Parag\Scripts\Powershell :
$a = @("asdf","123")
return $a
Second script:
$scriptdir = "C:\Parag\Scripts\Powershell"
$c = . $scriptdir\first.ps1
$c