Hi, I am using the below query to get the capacity report, but concern is when I exported the report in excel the result of memory is showing in byte, however it should be GB.
CPU result in showing like = .01, but it should be in %.
>Another point, below red highlighted point helps I can see only the last 3 month data but I could not understand, which one format is this for define the date and time.
Please let me know so that I can understand the data and time format.
SELECT TOP 1000000 Nodes.NodeID AS NodeID,
Nodes.Region,
Nodes.Region_Country,
Nodes.Office,
Nodes.Caption AS NodeName,
Nodes."System" AS System,
Nodes.Server_Description AS Server_Description,
Nodes.Company AS Company,
Nodes.OS,
Nodes.Production,
Nodes.Device_Type AS Device_Type,
Nodes.Owner AS Owner,
AVG(CPULoad.AvgLoad) AS AVERAGE_of_AvgCPULoad,
MAX(CPULoad.MaxLoad) AS MAX_of_MaxCPULoad,
AVG(CPULoad.AvgMemoryUsed)/(1024*1024*1024) AS AVERAGE_of_AvgMemoryUsed,
MAX(CPULoad.MaxMemoryUsed)/(1024*1024*1024) AS MAX_of_MaxMemoryUsed,
ROUND(Nodes.TotalMemory/(1024*1024*1024),0) AS TotalMemory,
CPU.CPUCount,
CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth
FROM
Nodes INNER JOIN CPULoad ON (Nodes.NodeID = CPULoad.NodeID)
LEFT JOIN
(
SELECT
[HostNodes].[NodeID] AS NodeID,
N'H' AS NodeType
FROM dbo.VIM_VirtualMachineNodes AS VMNodes
LEFT JOIN dbo.VIM_HostNodes AS HostNodes
ON ([HostNodes].[HostID] = [VMNodes].[HostID])
UNION
(
SELECT
[MACAddresses].[NodeID] AS C4,
N'VM' AS C6
FROM dbo.NodeMACAddresses AS MACAddresses
LEFT JOIN dbo.VIM_HostNodes AS HostNodes2
ON ([HostNodes2].[NodeID] = [MACAddresses].[NodeID])
WHERE
(
([MACAddresses].[MAC] LIKE N'0003FF%') OR
([MACAddresses].[MAC] LIKE N'000C29%') OR
([MACAddresses].[MAC] LIKE N'005056%') OR
([MACAddresses].[MAC] LIKE N'000569%') OR
([MACAddresses].[MAC] LIKE N'001C14%')
)
AND ([HostNodes2].[HostID] IS NULL)
)
)
AS Res
ON [Res].[NodeID] = [Nodes].[NodeID]
INNER JOIN dbo.Volumes AS Volumes
ON Volumes.NodeID = Nodes.NodeID
INNER JOIN
(
SELECT Nodes.NodeID, COUNT(DISTINCT CPUIndex) AS CPUCount
FROM CPUMultiLoad_Detail CPU WITH(NOLOCK)
JOIN Nodes
ON CPU.NodeID = Nodes.NodeID
GROUP BY
Nodes.NodeID
) AS CPU
ON CPU.NodeID = Nodes.NodeID
WHERE
( DateTime BETWEEN 42337 AND 42429 )
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.NodeID, Nodes.Caption, Nodes.MachineType, Nodes.Device_Type, Nodes.Company, Nodes.Owner, Nodes."System", Nodes.Server_Description, Nodes.TotalMemory,CPUCount,Nodes.Region,Nodes.Region_Country,Nodes.Office,Nodes.Production,Nodes.OS
ORDER BY 9 ASC, 10 ASC ,SummaryMonth ASC
