Hello
I am very new to writing SQL queries, I have been tasked with creating a problem report to provide, Detail of the Triggered Alert (Alert Name), When the Alert was Triggered and count of times with in that period, I also would like to include columns for the Site and Device Type to which the device belongs.
I found a report written by another community member that I have been trying to tweak to give me what I need.
select
'<img src="/NetPerfMon/images/Small-' + n.StatusLED + '"/> ' + '<ahref="thwack.solarwinds.com/.../View.aspx + CAST(N.NodeID AS varchar(256)) + '"style="font-size:100%">' + N.Caption + '</a>' AS NODE
,'<ahref="' + '/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' +CAST(ahv.AlertObjectID AS varchar(256)) + '"style="font-size:100%">' + ahv.Name + '</a>' AS 'AlertName'
,COUNT(*) AS 'Total_Alerts'
,DAY(timestamp)AS 'Day'
,MONTH(TimeStamp)AS'Month'
,YEAR(timestamp)AS 'Year'
FROM AlertHistoryView ahv with(nolock)
INNER JOIN Nodes n ON n.NodeID = ahv.RelatedNodeId
WHERE
Category =‘Default'
AND Device_Type = 'switch'
AND EventTypeWord = 'Triggered'
AND DATEDIFF(DAY,TimeStamp, GETDATE()) < 30
group by
DAY(timestamp),MONTH(TimeStamp) ,YEAR(timestamp)
,n.StatusLED,n.Caption,n.NodeID,ahv.AlertObjectID,ahv.Name
order by total_alerts desc, month(timestamp)desc, day(timestamp)desc
The above seems to work well, However I am currently unable to work out how to include columns from the Nodes (View) with in the report.
The follow columns present in the nodes View Table need to be visible with in the report.
Device_Type
DSS_Site
any help or pointers would be much appreciated.