16 Replies Latest reply on Sep 6, 2018 11:39 PM by krishna mishra

    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
                                    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

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

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

                             

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

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

                              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.