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

Parents
  • This should be everything you need,

    select

    --VM.Name,

    --VM.PowerState as PowerState,

    node.Caption,

    node.MachineType,

    Hardware.AgentVersion,

    hardware.Manufacturer,

    hardware.Model,

    procs.NumberOfCores,

    --procs.NumberOfThreads,

    procs.Manufacturer,

    procs.Name,

    procs.Stepping,

    procs.Caption,

    procs.SpeedMHz,

    procs.NumberOfCores,

    Hdd.Model,

    HDD.SerialNumber,

    HDD2.VolumeName,

    Hdd2.FileSystem,

    hdd2.CapacityB,

    Hdd2.FreeSpaceB,

    Hdd2.VolumeName,

    Ram.Model,

    ram.CapacityMB,

    ram.SpeedNSec,

    ram.Slot,

    node.DeviceRole as devicerole

    from VIM_VirtualMachines as VM

    full outer join dbo.Nodes as node on vm.NodeID = node.NodeID

    full outer join dbo.APM_HardwareInfo as Hardware on Hardware.NodeID = node.NodeID

    full outer join dbo.AssetInventory_Processor as procs on procs.NodeID = node.NodeID

    full outer join dbo.AssetInventory_HardDrive as HDD on HDD.NodeID = node.NodeID

    full outer join dbo.AssetInventory_LogicalDrive as HDD2 on HDD2.NodeID = node.NodeID

    full outer join dbo.AssetInventory_MemoryModuleView as Ram on Ram.NodeID = node.NodeID

    full outer join dbo.AssetInventory_MemoryModule as Ram2 on Ram.NodeID = node.NodeID

  • I copy and paste the code and its invalid in the query window.

    Can someone reveal the secret to copying and pasting code from HTML posts to SW?

Reply Children
  • Opps I dont think you DeviceRole try this also are you using SQL Studio?

    select

    --VM.Name,

    --VM.PowerState as PowerState,

    node.Caption,

    node.MachineType,

    Hardware.AgentVersion,

    hardware.Manufacturer,

    hardware.Model,

    procs.NumberOfCores,

    --procs.NumberOfThreads,

    procs.Manufacturer,

    procs.Name,

    procs.Stepping,

    procs.Caption,

    procs.SpeedMHz,

    procs.NumberOfCores,

    Hdd.Model,

    HDD.SerialNumber,

    HDD2.VolumeName,

    Hdd2.FileSystem,

    hdd2.CapacityB,

    Hdd2.FreeSpaceB,

    Hdd2.VolumeName,

    Ram.Model,

    ram.CapacityMB,

    ram.SpeedNSec,

    ram.Slot

    from VIM_VirtualMachines as VM

    full outer join dbo.Nodes as node on vm.NodeID = node.NodeID

    full outer join dbo.APM_HardwareInfo as Hardware on Hardware.NodeID = node.NodeID

    full outer join dbo.AssetInventory_Processor as procs on procs.NodeID = node.NodeID

    full outer join dbo.AssetInventory_HardDrive as HDD on HDD.NodeID = node.NodeID

    full outer join dbo.AssetInventory_LogicalDrive as HDD2 on HDD2.NodeID = node.NodeID

    full outer join dbo.AssetInventory_MemoryModuleView as Ram on Ram.NodeID = node.NodeID

    full outer join dbo.AssetInventory_MemoryModule as Ram2 on Ram.NodeID = node.NodeID

  • Hi,

    I appreciate the help on a report that should have been made available 15 years ago. emoticons_happy.png

    I am copying your code into a new report window/SQL code as I have done before with other code. It breaks with either type option. I'm sure the formatting isn't right copying from here....thanks

    6-22-2016 8-45-23 AM.jpg

  • I just want to also define the problem we're trying to solve so anyone else can chime in if needed. It seems myself and the OP are the only people on the planet that need a very basic server inventory report from SolarWinds.

    Most companies have PMs/DBAs/Admins/Janitors that want server reports that target a specific group of servers. With SolarWinds it is impossible to do that out of the box.

    PMs and other interested parties simply want to see:

    1. Server name

    2. Processor count

    3. Memory count

    4. Disk volumes (logical, cluster, mount) count

    5. OS type

    Thats it.

    With SCOM I have a simple report that does this for me. Yes, they still have Microsoft misspelled in the 2008 MP, lol...:)

    This one I cooked up doesn't have disk size though. Something funky with this client's servers, so I left it out. You get the idea of what we're looking for though.

    scom-servers.jpg

  • 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