Troubleshooting Tip: Average CPU ,Memory and C Drive Usage in Parallel across Multiple Servers
,
I am using the windows PowerShell workflows to identify Average CPU ,Memory and C Drive Usage in Parallel across Multiple Servers.
It’s a quick check to see the health of servers that I use regularly to identify issues with servers.
workflow parallelUsageCheck {
param(
[int]$threads
)
$array = @()
$avg = Get-WmiObject win32_processor |
Measure-Object -property LoadPercentage -Average |
Foreach {$_.Average}
$mem = Get-WmiObject win32_operatingsystem |
Foreach {“{0:N2}” -f ((($_.TotalVisibleMemorySize – $_.FreePhysicalMemory)*100)/ $_.TotalVisibleMemorySize)}
$free = Get-WmiObject Win32_Volume -Filter “DriveLetter = ‘C:'” |
Foreach {“{0:N2}” -f (($_.FreeSpace / $_.Capacity)*100)}
$array += [pscustomobject] [ordered] @{
AverageCpu = $avg
MemoryUsage = $mem
PercentFree = $free
}
$array | Sort-Object -Property AverageCpu -Descending|select -First 10
}
Clear-Host
parallelUsageCheck -PSComputerName Server1,Server2,Server3 `
|select * -ExcludeProperty PSSourceJobInstanceId|Out-GridView
Regards,
Chaitanya
Case Statement on Dynamically Generated Pivot Columns
Hi All,
I got into a situation where I have to apply case statement on Dynamically generated columns. Below is my requirement and how I how I achieved that.
I have created below result set with pivoting. Actually Server1,server2,server3 … are Rows and I am making them as columns. I am generating the servers information as columns dynamically
| AssemblyName | Server 1 | Server 2 | Server 3 |
| Assembly1 | NOT EXIST | NULL | NOT EXIST |
| Assembly2 | NOT EXIST | NULL | NOT EXIST |
| Assembly3 | NOT EXIST | NULL | NOT EXIST |
I want the result like this.
| AssemblyName | Server 1 | Server 2 | Server 3 |
| Assembly1 | NOT EXIST | EXIST | NOT EXIST |
| Assembly2 | NOT EXIST | EXIST | NOT EXIST |
| Assembly3 | NOT EXIST | EXIST | NOT EXIST |
Below is the solution for this.
–Below is the Dynamic SQL Pivot Script.
DECLARE @servers nvarchar(max)
SELECT @servers =
STUFF(
(
select distinct ‘,[‘ + S.ServerName + ‘]’
from tempassemblies S
for xml path(”)
),
1,1,”)
DECLARE @SQL nvarchar(max)
SELECT @SQL = N’
select
* into Temp_Assemblies
from (
SELECT p.AssemblyName, p.ServerName,p.CurrentStatus
FROM dbo.tempassemblies p
) Data
PIVOT (
max(CurrentStatus )
FOR [ServerName]
IN (
‘ + @servers + ‘
)
) PivotTable
‘
exec sp_executesql @SQL
–update NULL values to ‘Custom Values’
DECLARE @dynamicsql nvarchar(MAX)
SELECT @dynamicsql= COALESCE(@dynamicsql + ‘ ‘, ”) +
‘UPDATE Temp_Assemblies SET ‘ + quotename(name)+ ‘ = ”EXIST” WHERE ‘ + quotename(name) + ‘ IS NULL ‘
FROM sys.columns WHERE object_id = object_id(‘Temp_Assemblies’)
EXECUTE (@dynamicsql)
Regards,
Chaitanya
increment the string value letter by letter for every row
Hi ,
This is my first post in this blog. yesterday my friend asked me how to display the sting letter by letter in sql server
we have string function substring used here
below is the script for this
use tempdb
go
if object_id(‘tempdb..#temp’) is not null
begin
drop table #temp
end
go
— declaring the variable to store the string
declare @test varchar(100)
set @test=’chaitanya’ — assigning the value to the variable
declare @count int
select @count=len(@test)
declare @i int
set @i=1
create table #temp
(
column1 varchar(100)
)
declare @result varchar(100)
declare @j int
set @j=0
while(@i <=@count)
begin
select @result =substring(@test,@j,@i+1)
insert #temp(column1)
select @result
set @i=@i+1
end
select * from #temp
–result
c
ch
cha
chai
chait
chaita
chaitan
chaitany
chaitanya
Please let me know if you have any concerns or any suggestions
Regards,
Chaitanya
SQL BLOGGING 