AnsweredAssumed Answered

total capacity of cdm

Question asked by La-Qa on Feb 13, 2017
Latest reply on Feb 16, 2017 by BryanKMorrow

Hello Team

We use the following query to get the details of machines currently being monitored on nimsoft. we get the output like in below screenshot. We need also to get the total capacity of each parameter i.e cpu disk n memory .Please can you edit the following query to get my desired output

 

THanks

 

 

 

 

 

 

select a.customer
,a.source
,f.ip
,a.date
,CONVERT(DECIMAL(10,2),a.cpu) as cpu
,CONVERT(DECIMAL(10,2),b.mem) as memory
,c.files as files
,100 - (CONVERT(DECIMAL(10,2),c.disk_perc)) as disk_perc_free
-- ,CONVERT(DECIMAL(10,2),d.disk_mb) as disk_mb
from
(
SELECT cpu.origin as customer
,cpu.source as source
,CAST(MONTH(cpu.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(cpu.sampletime) AS VARCHAR(4)) as Date
,avg(cpu.samplevalue) as cpu
FROM [CA_UIM].[dbo].[V_QOS_CPU_USAGE] CPU


where month(cpu.sampletime) = 12
and year(cpu.sampletime) = 2016
group by CAST(MONTH(cpu.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(cpu.sampletime) AS VARCHAR(4)), cpu.source,cpu.origin
) a
join
(
SELECT mem.origin as customer
,mem.source as source
,CAST(MONTH(mem.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(mem.sampletime) AS VARCHAR(4)) as Date
,avg(mem.samplevalue) as mem
FROM [CA_UIM].[dbo].[V_QOS_MEMORY_USAGE] mem


where month(mem.sampletime) = 12
and year(mem.sampletime) = 2016
group by CAST(MONTH(mem.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(mem.sampletime) AS VARCHAR(4)), mem.source,mem.origin
) b on a.customer = b.customer and a.source = b.source and a.Date = b.Date
join
(
SELECT disk.origin as customer
,disk.source as source
,disk.target as files
,CAST(MONTH(disk.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(disk.sampletime) AS VARCHAR(4)) as Date
,avg(disk.samplevalue) as disk_perc
FROM V_QOS_DISK_USAGE_PERC disk


where month(disk.sampletime) = 12
and year(disk.sampletime) = 2016
group by CAST(MONTH(disk.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(disk.sampletime) AS VARCHAR(4)), disk.source,disk.origin,disk.target
) c on a.customer = c.customer and a.source = c.source and a.Date = c.Date
join
(
SELECT diskmb.origin as customer
,diskmb.source as source
,diskmb.target as files
,CAST(MONTH(diskmb.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(diskmb.sampletime) AS VARCHAR(4)) as Date
,avg(diskmb.samplevalue) as disk_mb
FROM V_QOS_DISK_USAGE diskmb


where month(diskmb.sampletime) = 12
and year(diskmb.sampletime) = 2016
group by CAST(MONTH(diskmb.sampletime) AS VARCHAR(2)) + '-' + CAST(YEAR(diskmb.sampletime) AS VARCHAR(4)), diskmb.source,diskmb.origin,diskmb.target
) d on c.customer = d.customer and c.source = d.source and c.Date = d.Date and c.files = d.files
join
(SELECT distinct cm.ip,cm.name
from cm_computer_system cm
) f on f.name = d.source

Outcomes