0 Replies Latest reply on Apr 5, 2018 11:24 AM by jdwinns

    Weekly SAM Report

    jdwinns

      Hey everyone,

       

      I'm attempting to create a custom SQL report but my SQL knowledge is a bit limited. I have 3 queries that I would like to tie together into one report. I tried using "UNION" and "UNION ALL" but haven't had much luck. Also, I’m having trouble with the WHERE statements. It won’t run unless I take the DateTime out, is there a better way to filter these for a Weekly 7 day pull?

       

      Any help would be awesome, thank you!

       

       

      QUERY #1 CPU/Memory Report:

       

      SELECT  TOP 10000 Nodes.NodeID AS NodeID,

      1. Nodes.Caption AS NodeName,
      2. Nodes.VendorIcon AS Vendor_Icon,

      AVG(CPULoad.AvgLoad) AS AVERAGE_of_AvgCPULoad,

      MAX(CPULoad.MaxLoad) AS MAX_of_MaxCPULoad,

      AVG(CPULoad.AvgPercentMemoryUsed) AS AVERAGE_of_AvgPercentMemoryUsed,

      MAX(CPULoad.MaxMemoryUsed) AS MAX_of_MaxMemoryUsed,

      AVG(CPULoad.TotalMemory) AS AVERAGE_of_TotalMemory

       

      FROM

      Nodes INNER JOIN CPULoad ON (Nodes.NodeID = CPULoad.NodeID)

       

       

      WHERE

      ( DateTime BETWEEN 43172 AND 43179.5833333333 )

      AND 

      (

        (Nodes.AlertGroup = 'SCMS_PROD') OR

        (Nodes.AlertGroup = 'AMS_PROD')

      )

       

       

      GROUP BY Nodes.NodeID, Nodes.Caption, Nodes.VendorIcon

       

       

      ORDER BY 2 ASC

       

       

      QUERY #2 Availability Report

      SELECT  TOP 10000

      1. Nodes.NodeID AS NodeID,
      2. Nodes.VendorIcon AS Vendor_Icon,
      3. Nodes.Caption AS NodeName,

      AVG(ResponseTime.Availability) AS AVERAGE_of_Availability

       

      FROM SolarWindsOrion.dbo.Nodes

      INNER JOIN SolarWindsOrion.dbo.ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

       

      WHERE

      ( DateTime BETWEEN 43172 AND 43179.5416666667 )

      AND 

      (

      (Nodes.AlertGroup = 'SCMS_PROD') OR

      (Nodes.AlertGroup = 'AMS_PROD')

      )

       

      GROUP BY

      1. Nodes.caption, Nodes.VendorIcon, Nodes.NodeID

       

       

      QUERY #3 Volume Usage Report

       

      SELECT

      '<img src="/NetPerfMon/images/Small-' + n.StatusLED + '"/>  ' + '<a href="/Orion/View.aspx?View=NodeDetails&NetObject=N:' + CAST(n.NodeID AS varchar(256)) + '" style="font-size:100%">' + N.Caption + '</a>' AS NODE,

      '<a href="/Orion/View.aspx?View=NodeDetails&NetObject=V:' + CAST(v.VolumeID AS varchar(256)) + '" style="font-size:100%">' + v.caption + '</a>' AS VOLOUME,

      CONVERT(NVARCHAR(50), ROUND(v.VolumeSpaceUsed/1024/1024/1024,2)) + ' GB' AS 'Used',

      CONVERT(NVARCHAR(50), ROUND(v.VolumeSpaceAvailable/1024/1024/1024,2)) + ' GB' AS 'Available',

      CONVERT(NVARCHAR(50), ROUND(v.VolumeSize/1024/1024/1024,2)) + ' GB' AS 'Size',

      CONVERT(NVARCHAR(50), cast(v.VolumePercentUsed as decimal (4,2))) + '%' as PercentUsed,

      • v.VolumeType,
      • vc.WarningThreshold,
      • vc.CriticalThreshold,
      1. n.alertgroup

       

      FROM SolarWindsOrion.dbo.Volumes v

      INNER JOIN SolarWindsOrion.dbo.nodes n on n.nodeid = v.NodeID

      INNER JOIN SolarWindsOrion.dbo.VolumesForecastCapacity VC on vc.instanceID = v.VolumeID

       

      WHERE

      VolumeType IN ('Fixed Disk', 'FixedDisk')

      AND (n.AlertGroup = 'SCMS_PROD') OR (n.AlertGroup = 'AMS_PROD')

       

      GROUP BY

      1. n.caption,
      • v.caption,
      • v.VolumeSpaceUsed,
      • v.VolumeSpaceAvailable,
      • v.VolumeSize,
      • v.VolumePercentUsed,
      • v.VolumeType,
      • vc.WarningThreshold,
      • vc.CriticalThreshold,
      1. n.StatusLED,
      2. n.NodeID,
      3. N.Caption,
      • v.VolumeID,
      1. n.alertgroup

       

      ORDER BY

      (v.VolumePercentUsed) desc