This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Comprehensive Server Report (CPU, Memory, Disk and Availability)

Hi,

Is there a way to create a report for server utilization based on last 30 days or Last Month? The columns should be

NodeID      NodeName     CPU     Memory     Disk          Availability

1                 Server A          80%     99%          C: 70%          99%

                                                                         E: 65%

Can this be done via query?

  • Someone recently posted something very similar that I use but now I can't find it here...

    It was named Server+Report.xml I believe.

    2-17-2016 5-11-14 PM.jpg

  • Hi,

    Thanks for the input but this report does not contain the details for Fixed Disk Volumes ie Drive C: or D:  etc. The Node report can contain CPU and Memory but we're having trouble including the disk volumes

  • Yep, I feel your pain. My PMs want the same thing.

  • select

    nodez.NodeID as id,

    Nodez.Caption as Servername,

    HDD.CapacityB as HDDsize,

    hdd.Model,

    hdd2.VolumeName,

    hdd2.CapacityB,

    Nodez.DeviceRole as DeviceRole,

    IPAddr.IPAddress as 'IP Addresses'

    FROM dbo.Nodes AS Nodez

    join dbo.NodeIPAddresses as IPAddr on Nodez.NodeID = IPAddr.NodeID

    join dbo.AssetInventory_HardDrive as HDD on Nodez.NodeID = HDD.NodeID

    join dbo.AssetInventory_LogicalDrive as HDD2 on Nodez.NodeID = HDD2.NodeID

    where DeviceRole = 'server'

  • What I ended up doing was first creating the CPU / Memory portion of the report using the Gauges and Charts they provide in the custom report builder.

    Then added my own Custom Table [SQL Query] at the bottom to handle the Disk/Volume information.

    Its working nicely.

    SELECT

       N.NodeID AS 'Node ID'

      ,N.Caption AS 'Server Name'

      ,I.IPAddress as 'IP Addresses'

      ,V.Caption AS 'Volume Name'

      ,V.VolumeDescription AS 'Volume Description'

      ,V.VolumeType AS 'Volume Type'

      ,V.VolumeTypeID AS 'Volume Type ID'

      ,ROUND(V.VolumeSize/1073741824,0) AS 'SIZE /GB'

      ,ROUND(V.VolumePercentUsed,0) AS 'Percent Used'

      ,ROUND(V.VolumeSpaceUsed/1073741824,0) AS 'USED /GB' --VolumeSpaceUsed is calculated in Bytes (/ by 1073741824 to get GB)

      ,ROUND(V.VolumeSpaceAvailable/1073741824,0) AS 'FREE /GB' --VolumeSpaceAvailable is calculated in Bytes (/ by 1073741824 to get GB)

      ,CASE

      WHEN V.VolumePercentUsed >= 95 THEN 'WARNING Less than 5% Free'

      ELSE ' '

      END AS 'Warning'

    FROM dbo.Nodes AS N

      INNER JOIN dbo.NodeIPAddresses AS I

      ON N.NodeID = I.NodeID

      INNER JOIN dbo.Volumes AS V

      ON N.NodeID = V.NodeID

    WHERE

      V.VolumeTypeID = (4)

    Order BY

      N.Caption ASC

  • Can you elaborate on how you incorporated memory and CPU?

  • The report manager gives you built in charts and gauges to build a report. I just used that.

    Reports > All Reports > Report Manager > Create New Report

    Add Content > Charts > Average CPU Load

    Add Content > Gauges > Average CPU Load & Memory Utilization – Pie Gauges

    Then I added my own table content and pasted in the SQL query above.

    Add Content > Reports > Custom Table > Advanced Database and SQL Query


  • Thanks. Ok. If I have a static list of servers to create a report against how can we mortals wit multiple tenants tie all of this together for a group report for each client?

  • The report builder allows you to choose which servers you want to include. 

    As for the SQL query i couldn't tell you without seeing your setup, but it would likely just be another line to the WHERE statement.