I have the following query which collects the latest volumes that exceed a threshold. It works great but now I need to also have it join another table to show when it first alerted.
The current query looks like:
;with cte as (
select d.nodeid,n.Caption,d.volumeid, v.VolumeDescription,d.Datetime, round(AVG(D.PercentDiskUsed),0) as [AverageUtilization] from VolumeUsage_Detail D join dbo.Volumes V on D.nodeid = V.nodeid and d.volumeid = v.volumeid join dbo.nodes n on d.nodeid=n.nodeid where (n.SLA = '51' AND V.VolumeType like '%fixed%'
AND d.Datetime >= Dateadd(dd, -5, datediff(dd,0,getdate())) ) and D.PercentDiskUsed > 65 group by d.nodeid,n.caption,d.volumeid, v.VolumeDescription,d.datetime, d.disksize
select a.* from cte a
where a.datetime = (select max(datetime) from cte where nodeid=a.nodeid and volumeid = a.volumeid) order by Caption
I need it to now join the results from this query against the ALERTLOG table on the caption field from the above query on the ObjectName in the AlertLog table.
I have tried to join them but keep getting an error becuase the orginal query has a ";with cte' in it.