4 Replies Latest reply on Sep 3, 2014 5:01 PM by allanseyberth

    Table to show all 5 statistics being returned from Linux script

    allanseyberth

      Does anyone have a good method to show all five of the returned statistics, in column format from the 'Linux Disk Monitoring Perl' script?

      ala

      Caption       Timing Cached Reads     Timing Buffered     Blocks Recieved     Blocks Sent     Available Space

      Machine1          5                                   2                         6                              1                    28

      Machine2          4                                   1                         10                             2                    33        

      etc.

       

       

      Web reports allows me to show them singularly but I need them in a column format to allow for a per metric average

       

      I'm trying to drop to a SQL report but am getting lost in the relational linkages as well as not being able to find the table where these statistics are being stored.

       

       

      Does anyone have any suggestions, see something obvious I'm missing?

        • Re: Table to show all 5 statistics being returned from Linux script
          allanseyberth

          A little progress forward.  After hunting around SSMS and using a utility from dbschema.com to map out the database (seriously, are you guys ever going to release the database design document?) I stumbled across a view APM_MultiValueReportsData which is comprised of the following tables:

           

          SELECT

               c.ID AS ComponentID,

               ccs.ID as ComponentStatusID,

               DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()),

               ccs.[TimeStamp]) as [DateTime],

               statisticColumn.Name,

               statisticColumn.Label,

               statisticData.MinNumericData,

               statisticData.MaxNumericData,

               statisticData.AvgNumericData,

               messageData.StringData,

               ccs.PercentAvailability as ComponentAvailability,

               dbo.apm_GetDisplayStatus(ccs.Availability) AS ComponentStatus

          FROM

               APM_Component c

          JOIN

               APM_ComponentStatus ccs ON c.ID = ccs.ComponentID

          JOIN

               APM_DynamicEvidenceColumnSchema statisticColumn ON c.ID = statisticColumn.ComponentID AND statisticColumn.[Type] = 1

          LEFT JOIN

               APM_DynamicEvidence statisticData ON statisticData.ComponentStatusID = ccs.ID AND statisticData.ColumnSchemaID = statisticColumn.ID

          LEFT JOIN

               APM_DynamicEvidenceColumnSchema messageColumn ON c.ID = messageColumn.ComponentID AND messageColumn.[Type] = 0 AND messageColumn.Name = statisticColumn.Name

          LEFT JOIN

               APM_DynamicEvidence messageData ON messageData.ComponentStatusID = ccs.ID AND messageData.ColumnSchemaID = messageColumn.ID

           

          -------

          A down and dirty

          SELECT 

              *

          FROM

               APM_Component 

          JOIN

               APM_Application ON APM_Component.ApplicationID = APM_Application.ID

          JOIN

               Nodes on APM_Application.NodeID =  Nodes.NodeID

          JOIN

              APM_MultiValueReportsData ON APM_Component.ID = APM_MultiValueReportsData.ComponentID 

           

          (I'll throw the WHERE clauses and limit the fields selected later, this was just a test to see if I'm getting what I'm hoping for)

           

           

          Seems to show all the fields I'm looking for - though I wonder why the fields MinNumericData, MaxNumericData and AvgNumericData exist - as all three fields show the same value

            • Re: Table to show all 5 statistics being returned from Linux script
              HolyGuacamole

              A quick pointer for any such future efforts - use the Legacy Reporter on the SolarWinds server and add in the columns that show the data you need. Once you have done designing the report, the Edit menu in the Design view has a 'Show SQL' option. If you choose that option, the SQL query to execute the report will be displayed. So, you shouldn't have to play around with DB schema tools.

               

              Once you have the SQL that will fetch you the data, you can come up with the SQL to change the format, etc and create a new 'Advanced SQL' report.

              Below is a good resource for the kind of manipulation you are after

              SQL SERVER – PIVOT and UNPIVOT Table Examples | Journey to SQL Authority with Pinal Dave

                • Re: Table to show all 5 statistics being returned from Linux script
                  allanseyberth

                  HolyGuacamole wrote:

                   

                  A quick pointer for any such future efforts - use the Legacy Reporter on the SolarWinds server and add in the columns that show the data you need. Once you have done designing the report, the Edit menu in the Design view has a 'Show SQL' option. If you choose that option, the SQL query to execute the report will be displayed. So, you shouldn't have to play around with DB schema tools.

                   

                  Tried that early on, (and why the hell can't we get the back end SQL out of web reports?)  but the historical stats for SAM scripts is placed in an a-typical (and poorly documented) location.  At the very least I wasn't able to find what I was looking for there.

                • Re: Table to show all 5 statistics being returned from Linux script
                  allanseyberth

                  Final query - I gave up on trying to merge them into one table due to limited time.  Here is the query that targets on the 5th component of the Linux Disk Monitoring Perl SAM template

                   

                   

                   

                  SELECT 
                      Nodes.Caption,
                     AVG(APM_MultiValueReportsData.avgNumericData) AS 'Available space on / partition (MB)'

                     

                  FROM APM_Component 

                   

                      JOIN

                     APM_Application ON APM_Component.ApplicationID = APM_Application.ID

                      JOIN

                     Nodes on APM_Application.NodeID =  Nodes.NodeID

                      JOIN

                     APM_MultiValueReportsData ON APM_Component.ID = APM_MultiValueReportsData.ComponentID

                   

                      WHERE

                     APM_Component.Name = 'Available space on / partition (MB)'
                  AND
                     APM_MultiValueReportsData.DateTime  BETWEEN '2014-08-28 17:00:00.00' AND '2014-08-31 17:00:00.00' 

                   

                      GROUP BY

                     Nodes.Caption

                   

                      ORDER BY

                     Nodes.Caption