1 Reply Latest reply on Jun 27, 2014 7:30 AM by oliver.grist

    Help - Comparing to show volumes that have increased in size over the last 24 hours (current SQL and results included)

    dkandola

      Hi,


      I am attempting to create a report to highlight fixed disk utilisation spikes over the last 24 hours.

       

      I have tried to create a report, that compares the current disk utilisation with the historical disk utilisation, specified time frame = last 24 hours.

       

      This is the SQL;

       

      SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
      Nodes.NodeID AS NodeID,
      Nodes.Caption AS NodeName,
      Nodes.Application AS Application,
      Volumes.VolumeDescription AS Volume_Description,
      AVG(VolumeUsage.AvgDiskUsed) AS AVERAGE_of_AvgDiskUsed,
      AVG(Volumes.VolumeSpaceUsed) AS AVERAGE_of_VolumeSpaceUsed

      FROM
      (Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID))  INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)


      WHERE
      ( DateTime BETWEEN 41365.5833333333 AND 41366.625 )
      AND 
      (
        (Volumes.VolumeSpaceUsed > VolumeUsage.MaxDiskUsed) AND
        (VolumeUsage.AvgDiskUsed < Volumes.VolumeSpaceUsed) AND
        (Volumes.VolumeDescription NOT LIKE  '%RAM%') AND
        (Volumes.VolumeDescription NOT LIKE  '%memory%') AND
        (Volumes.VolumeDescription NOT LIKE  '%page%') AND
        (Volumes.VolumeDescription NOT LIKE  '%pool%') AND
        (Volumes.VolumeDescription NOT LIKE  '%Call%') AND
        (Nodes.Caption NOT LIKE  '%SOL%')
      )


      GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
      Nodes.NodeID, Nodes.Caption, Nodes.Application, Volumes.VolumeDescription


      ORDER BY SummaryDate ASC

       

      Below is a sample of the results returned;

       

      Date

      NodeApplicationVolumeDisk Space UsedSpace Used
      03/04/2013Server 1Application 1System ASP571.8 GB595.2 GB
      03/04/2013Server 2Application 2System ASP264.4 GB266.9 GB
      03/04/2013Server 3Application 3System ASP214.2 GB215.7 GB
      03/04/2013Server 4Application 4System ASP297.8 GB299.4 GB
      03/04/2013Server 5Application 5System ASP8.8 TB8.8 TB
      03/04/2013Server 6Application 6System ASP626.2 GB626.4 GB
      03/04/2013Server 7Application 7System ASP629.6 GB635.9 GB
      03/04/2013Server 8Application 8C:\ Label:SYSTEM Serial Number 16.2 GB16.2 GB
      03/04/2013Server 9Application 9C:\ Label: Serial Number12.8 GB12.8 GB
      03/04/2013Server 10Application 10C:\ Label: Serial Number 20.1 GB20.1 GB
      03/04/2013Server 11Application 11D:\ Label:Apps Serial Number 14.3 GB14.3 GB
      03/04/2013Server 12Application 12C:\ Label: Serial Number 23.9 GB23.9 GB
      03/04/2013Server 13Application 13D:\ Label:Storage Serial Number 34.0 GB34.0 GB
      03/04/2013Server 14Application 14J:\ Label:Shares_New Serial Number 4.7 TB4.7 TB
      03/04/2013Server 15Application 15C:\ Label: Serial Number 17.5 GB17.5 GB
      03/04/2013Server 16Application 16D:\ Label:Respond Serial Number 770.0 MB770.6 MB
      03/04/2013Server 17Application 17G:\ Label:Paperwork Serial Number 27.8 GB27.9 GB
      03/04/2013Server 18Application 18C:\ Label: Serial Number40.7 GB40.7 GB
      03/04/2013Server 19Application 19D:\ Label:Local Disk Serial Number 3.5 GB3.5 GB
      03/04/2013Server 20Application 20C:\ Label: Serial Number14.5 GB14.5 GB
      03/04/2013Server 21Application 21D:\ Label: Serial Number16.0 GB16.0 GB
      03/04/2013Server 22Application 22D:\ Label:New Volume Serial Number20.3 GB20.3 GB
      03/04/2013Server 23Application 23E:\ Label:New Volume Serial Number581.5 GB581.5 GB
      03/04/2013Server 24Application 24C:\ Label: Serial Number51.3 GB51.4 GB
      03/04/2013Server 25Application 25E:\ Label:DATA Serial Number166.6 GB167.5 GB
      03/04/2013Server 26Application 26C:\ Label: Serial Number 31.0 GB31.1 GB
      03/04/2013Server 27Application 27C:\ Label: Serial Number 41.8 GB41.9 GB
      03/04/2013Server 28Application 28E:\ Label: Serial Number 17.6 GB17.6 GB
      03/04/2013Server 29Application 29C:\ Label:System Serial Number 26.3 GB26.4 GB
      03/04/2013Server 30Application 30C:\ Label: Serial Number 24.5 GB24.6 GB
      03/04/2013Server 31Application 31C:\ Label: Serial Number21.1 GB21.1 GB
      03/04/2013Server 32Application 32C:\ Label: Serial Number 20.8 GB20.8 GB
      03/04/2013Server 33Application 33C:\ Label:OS Serial Number19.4 GB19.4 GB
      03/04/2013Server 34Application 34D:\ Label:APPS Serial Number32.0 GB32.0 GB
      03/04/2013Server 35Application 35F:\ Label:Logs Serial Number11.0 GB11.0 GB
      03/04/2013Server 36Application 36J:\ Label:Database9 Serial Number409.4 GB410.0 GB
      03/04/2013Server 37Application 37K:\ Label:Database10 Serial Number443.6 GB443.9 GB
      03/04/2013Server 38Application 38L:\ Label:DAGLogs Serial Number 100.0 GB106.4 GB 

       

      It can be seen that whilst the query is working, retruning results as expected (in black), the query is also returning values that are are identical for disk utilisation over the past 24 hours (highlighted in red)

       

      My only reasoning for this is that SolarWinds is liekly to be computing upto the very last byte of data, whereas above, the utilisation is displayed in a rounded up manner to 0.1 GB.

       

      How does one go about eliminating the volumes, with the same utilisation, over the last 24 hours (highlighted in red) - in order to end up with a report, which ONLY highlights volumes that have increased over the last 24 hours?

        • Re: Help - Comparing to show volumes that have increased in size over the last 24 hours (current SQL and results included)
          oliver.grist

          With some small tweaks to your SQL you can find any increases in usage over any time period.

           

          I dont think there is good date support in SWQL yet, I notice you hardcoded the date (I have too).

           

          SELECT

          Nodes.NodeID,

          Nodes.Caption,

          Volumes.VolumeId,

          Volumes.VolumeDescription,

          Volumes.VolumeSpaceUsed as SpaceUsedNow,

          VolumeUsageHistory.AvgDiskUsed as SpaceUsed24hourago

          FROM Orion.Nodes

          INNER JOIN Orion.Volumes ON Nodes.NodeID = Volumes.NodeID

          INNER JOIN Orion.VolumeUsageHistory ON VolumeUsageHistory.VolumeId = Volumes.VolumeId AND DateTime > '2014-06-26 13:00' AND DateTime < '2014-06-26 13:15' AND VolumeUsageHistory.AvgDiskUsed < Volumes.VolumeSpaceUsed

          WHERE Nodes.NodeId < 20

          ORDER BY Nodes.NodeId, Volumes.VolumeId