21 Replies Latest reply on Dec 3, 2015 9:33 AM by bman6074

    Unable to add the node Hardware property to a report

    jest4kicks

      Hey all, first post!  (There will be more.  LOL!)

       

      I'm trying to create a node report showing various properties, mostly from the Asset Inventory section.  One property that I'm having trouble with is the "Hardware" property.  For physical servers, the property reads (and should display) "Physical", and for VMs, the property should display "Virtual" (along with the host info, if known).

       

      The info for these properties shows up just fine in the Node Details view, but for the life of me, I cannot figure out how to include it in a report.  I've seen a few other threads that seem to touch on the same subject, but none of them were ever resolved. (That I found.)

       

      Appreciate any help!

        • Re: Unable to add the node Hardware property to a report
          ladames

          SELECT

          ---Nodes.Site AS Site,

           

          Nodes.Caption AS ServerName,

           

          Nodes.Vendor AS Vendor,

           

          Nodes.MachineType AS OSModel,

           

          Nodes.Description AS Hardware,

           

          Nodes.IOSVersion AS OSVersion,

           

          Nodes.CPULoad AS CPUUtil,

           

          Nodes.PercentMemoryUsed AS PercentMemory,

           

          Nodes.TotalMemory AS TotalMemory,

           

          Volumes.Caption AS Volume,

           

          Volumes.VolumeSize AS VolumeSize,

           

          Volumes.VolumeSpaceUsed AS SpaceUsed,

           

          CPU.CPUCount,

           

          CASE WHEN Res.NodeType in ('H','VM') Then 'Virtual' Else 'Physical' END AS NodeType

           

          FROM dbo.Nodes AS Nodes

           

          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

          1 of 1 people found this helpful
            • Re: Unable to add the node Hardware property to a report
              ladames

              This is not mine I grabbed it along time and do not know who it came from. But this will give you the physical / virtual information you are looking for

                • Re: Unable to add the node Hardware property to a report
                  jest4kicks

                  Thanks! That seems to have an odd grouping setting, but it definitely gives me something to work with!

                    • Re: Unable to add the node Hardware property to a report
                      ladames

                      how would you like it grouped? I can help. Also if this is done you can mark it as completed.

                        • Re: Unable to add the node Hardware property to a report
                          jest4kicks

                          Oh ok!  Well there's a couple things I'd like to learn how to do differently.  Any light you can shed is greatly appreciated!

                           

                          First, the report is producing one line per physical volume, with all the other details of the server duplicated between those lines.  Is there a way to group the volumes together so that it just displays one entry per server?  (This could be a pipe dream, but it's worth asking.)

                           

                          Second, is there an easy way to convert the SpaceUsed/TotalMemory/VolumeSize entries to GB (looks like it's currently in bytes)?

                           

                          EDIT: Oh, one other important thing: I need the report to only show the nodes that are part of the users view limitation.  This was actually working properly in the report that I started with, but appears to have stopped working after I converted to the custom SQL query.

                           

                          Many thanks!

                            • Re: Unable to add the node Hardware property to a report
                              ladames

                              Typically what I do to group the volumes is to use a pivot table in Excel.

                                • Re: Unable to add the node Hardware property to a report
                                  jest4kicks

                                  Hmm, I was hoping to make it more presentable in the web view.  Sounds like it'll have to be exported to excel.  Ok, I'll keep working with it.

                                   

                                  What about the view limitation?  I searched around and saw that view limitations seem to be a problem with custom queries.  Is there any way around that?

                                    • Re: Unable to add the node Hardware property to a report
                                      ladames

                                      SELECT

                                      ---Nodes.Site AS Site,

                                       

                                      Nodes.Caption AS ServerName,

                                       

                                      nodes.DeviceRole As DeviceRole,

                                       

                                      Nodes.Vendor AS Vendor,

                                       

                                      Nodes.MachineType AS OSModel,

                                       

                                      --Nodes.Description AS Hardware,

                                       

                                      --Nodes.IOSVersion AS OSVersion,

                                       

                                      --Nodes.CPULoad AS CPUUtil,

                                       

                                      --Nodes.PercentMemoryUsed AS PercentMemory,

                                       

                                      --Nodes.TotalMemory AS TotalMemory,

                                       

                                      --Volumes.Caption AS Volume,

                                       

                                      --Volumes.VolumeSize AS VolumeSize,

                                       

                                      --Volumes.VolumeSpaceUsed AS SpaceUsed,

                                       

                                      --CPU.CPUCount,

                                       

                                      CASE WHEN Res.NodeType in ('H','VM') Then 'Virtual' Else 'Physical' END AS NodeType

                                       

                                      FROM dbo.Nodes AS Nodes

                                       

                                      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]

                                        • Re: Unable to add the node Hardware property to a report
                                          jest4kicks

                                          Hmm, that doesn't seem to work.  I'm getting the following error when I did deeper.

                                           

                                          Msg 207, Level 16, State 1, Line 6

                                          Invalid column name 'DeviceRole'.

                                          Msg 207, Level 16, State 1, Line 6

                                          Invalid column name 'DeviceRole'.

                                           

                                          Is DeviceRole a custom property in your instance?  I replaced it with a similar property that we use and the query works with that. 

                                           

                                          The only issue I'm still trying to overcome is the view limitation (restrict report content to the user's view limitation).  I've seen other threads indicating this could not be done when using custom SQL queries.  Do you know if that's still the case?

                                           

                                          Thanks again for all the help!

                                            • Re: Unable to add the node Hardware property to a report
                                              ladames

                                              SELECT

                                              ---Nodes.Site AS Site,

                                               

                                              Nodes.Caption AS ServerName,

                                               

                                              --nodes.DeviceRole As DeviceRole,

                                               

                                              Nodes.Vendor AS Vendor,

                                               

                                              Nodes.MachineType AS OSModel,

                                               

                                              --Nodes.Description AS Hardware,

                                               

                                              --Nodes.IOSVersion AS OSVersion,

                                               

                                              --Nodes.CPULoad AS CPUUtil,

                                               

                                              --Nodes.PercentMemoryUsed AS PercentMemory,

                                               

                                              --Nodes.TotalMemory AS TotalMemory,

                                               

                                              --Volumes.Caption AS Volume,

                                               

                                              --Volumes.VolumeSize AS VolumeSize,

                                               

                                              --Volumes.VolumeSpaceUsed AS SpaceUsed,

                                               

                                              --CPU.CPUCount,

                                               

                                              CASE WHEN Res.NodeType in ('H','VM') Then 'Virtual' Else 'Physical' END AS NodeType

                                               

                                              FROM dbo.Nodes AS Nodes

                                               

                                              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]

                                              • Re: Unable to add the node Hardware property to a report
                                                ladames

                                                what restricted view issue are you dealing with?

                              • Re: Unable to add the node Hardware property to a report
                                ladames

                                okay which solarwinds grouping do you use. So if I know that I have it set that a set of my users can only see devices in a site ny I would group them by the node location ny. is this similar to what you are doing?

                                • Re: Unable to add the node Hardware property to a report
                                  ladames

                                  can give me a screenshot blanking out what ever is critical to your envirnoment?