13 Replies Latest reply on Aug 30, 2016 8:24 AM by ed209

    Combined capacity report of all server in one page

    krishna mishra

      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

        • Re: Combined capacity report of all server in one page
          ladames

          I didnt see this. I should be able to do a little something later today or tomorrow.

          • Re: Combined capacity report of all server in one page
            ladames

            This should work for the Procs. Check it and let me know

             

            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,

            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

            • Re: Combined capacity report of all server in one page
              ladames

              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

                • Re: Combined capacity report of all server in one page
                  orioncrack

                  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?

                    • Re: Combined capacity report of all server in one page
                      ladames

                      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

                        • Re: Combined capacity report of all server in one page
                          orioncrack

                          Hi,

                           

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

                           

                          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

                            • Re: Combined capacity report of all server in one page
                              orioncrack

                              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

                                • Re: Combined capacity report of all server in one page
                                  aqeel73

                                  Hi, Use SQL instead of SWQL.

                                    • Re: Combined capacity report of all server in one page
                                      krishna mishra

                                      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

                                    • Re: Combined capacity report of all server in one page
                                      ed209

                                      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