Weekly SAM Report

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="thwack.solarwinds.com/.../View.aspx + CAST(n.NodeID AS varchar(256)) + '" style="font-size:100%">' + N.Caption + '</a>' AS NODE,

'<a href="thwack.solarwinds.com/.../View.aspx + 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