Version 2

    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...