Hello,
I always had the challenge of creating a historical alert report, none of the inbuilt report were correct and not showing the actual alerts, Unfortunately I couldnt find any information in Thwack as well.
I wanted a daily report Which will have all the alerts triggered, Acknowledged etc.. I created the below query and it is very useful for me to generate quick reports on the historical alerts.Since I also wanted the custom properties to be included in the report I have to fetch the information from 2 tables.
The information which I wanted was in [dbo].[AlertHistoryView] and [dbo].[Nodes].
Create a new web report, Select custom table and use SQL Query. Select the required columns you want to see in report.
Query
SELECT TOP 200 b.CUSTOMPROPERTY1,b.CUSTOMPROPERTY2,b.CUSTOMPROPERTY3,b.CUSTOMPROPERTY4,a.* FROM
[dbo].[AlertHistoryView] a,[dbo].[Nodes] b
Where
a.relatednodeid = b.nodeid and
a.EventTypeWord in('Triggered' ,'Acknowledged')
and a.timestamp >= getdate() - 1
order by a.timestamp asc
Note
Replace the CUSTOMPROPERTY1,2,3,4, with required custom properties name.
you can also select more eventypype if you want.
get date () -1 will give last 24 hours, change 1 to any desired days you wanted to see the alerts
Order by desc or asc.
Attached sample report output,Incase if some one has a better query please share.
SELECT TOP 200 b.customername,b.nodeclassification,b.noderole,b.region,a.* FROM
[dbo].[AlertHistoryView] a,[dbo].[Nodes] b
Where
a.relatednodeid = b.nodeid and
a.EventTypeWord in('Triggered' ,'Acknowledged')
and a.timestamp >= getdate() - 1
order by a.timestamp asc
New
SELECT TOP 10
NodesData.Caption
,NodesData.IP_Address
,NodesData.Vendor
,AlertHistory.Message
,AlertHistory.TimeStamp
,NodesCustomProperties.NodeClassification
,NodesCustomProperties.NodeRole
,NodesCustomProperties.AssetTag
FROM dbo.NodesData
,dbo.AlertHistory
,dbo.NodesCustomProperties
WHERE AlertHistory.TimeStamp >= GETDATE() -1
Please note:
Change Select top 10 to a higher value depending on your setup.
Add/Modify custom properties marked in bold, You can copy the line and add as much as custom properties in this.
Modify Get Date -1 to pull out the number of days old alerts...