Combined capacity report of all server in one page

Hi All,

I want a create a customize capacity report, where I can get report of all node in one format where I can get like - DISK (Name, Uses, free space
etc), CPU (Count, Load, etc.), MEMORY (total memory, Peak Average),

Kindly any one can help us to get the such type of report

  • i am using this, can you please verify and add same table in same query, Table details like - cpu memory, disk

    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

  • Yeah, I have a similar problem. I want to use something we already have to be the center of the reporting universe, rather than having to run different agents/applications

  • after long time again i m doing comment on original requirement-

    Any one get  this type of report with new version of NPM / SAM.

  • Funnily enough, I have just had a request to get a combined report out. I go looking for information and I am brought here. Again.

  • Yup, Did you get something relevant ?

    Thanks & Regards

    krishna Mishra

    +91- 9015630644