Dashboard : SQL query

This is a question probably best suited for the forum.

Could you move it into that group ?

Thx

Yeah this would need to be a custom SQL report in report writer as the friendly premade reports limit you to one SQL view at a time.

Database manager is the best place to go reading tables, right click to see available columns, then back in report writer joins will be your friends.

Hello,

I'll try that way.

Thank you

Hello,

I come back with this request.

I didn't find a correct SQL to do it.

Any one has an idea?

Regards

So I took a stab at this.  All the data you want is in the AlertStatus table, including duration which just needed to be converted to Day:Hours,Mins,Sec.  But based on your original post, you want something a little more readable.  The issue there is that the "ActiveObject" in the AlertStatus table can refer to a NodeID or an ApplicationID or a VolumeID, etc, basically any object type that you have an alert for.  In my case, I have 5 basic types of advanced alerts, Nodes, Volumes, APM Hardware, APM Applications and APM Components.  By using a series of SELECT statements bound by UNION ALL commands, I was able to do subqueries against each different table that an ActiveObject may refer to.  I even included the name of the alert definition that was triggered.  You may leverage different tables, so you would have to make your own UNION ALL for those additional tables.  I do not use dependencies so I was not able to cover that.

The end result looks like this...after some manipulation in the Custom Table portion of the Add Report Wizard (column labels/sizes/positions/etc)

Here is the SQL that I used.  I avoided JOINS since I find a series of AND statements to be easier to trace and quickly modify from subquery to subquery.

SELECT DISTINCT 

   nodes.caption

      ,[ObjectName]

   ,AlertDefinitions.AlertName

      ,[TriggerTimeStamp]

      ,[TriggerTimeOffset]

   ,convert(char(10), [TriggerTimeOffset] / (60 * 60 * 24))+':'+convert(char(10), dateadd(s, [TriggerTimeOffset], convert(datetime2, '0001-01-01')), 108)

      ,[Acknowledged]

      ,[AcknowledgedBy]

      ,[AcknowledgedTime]

  FROM

  [Orion].[dbo].[AlertStatus]

  ,[Orion].dbo.Volumes

  ,[Orion].dbo.AlertDefinitions

  ,[Orion].dbo.Nodes

  where

  (volumes.VolumeID = AlertStatus.ActiveObject

    AND AlertStatus.ObjectType = 'Volume'

  AND Nodes.NodeID = Volumes.NodeID

  AND AlertStatus.AlertDefID = AlertDefinitions.AlertDefID

  )

UNION ALL

SELECT DISTINCT 

   nodes.caption

      ,[ObjectName]

   ,AlertDefinitions.AlertName

      ,[TriggerTimeStamp]

      ,[TriggerTimeOffset]

   ,convert(char(10), [TriggerTimeOffset] / (60 * 60 * 24))+':'+convert(char(10), dateadd(s, [TriggerTimeOffset], convert(datetime2, '0001-01-01')), 108)

      ,[Acknowledged]

      ,[AcknowledgedBy]

      ,[AcknowledgedTime]

  FROM

  [Orion].[dbo].[AlertStatus]

  ,[Orion].dbo.Nodes

  ,[Orion].dbo.AlertDefinitions

  ,[Orion].dbo.APM_HardwareItem

  where

  (APM_HardwareItem.ID = AlertStatus.ActiveObject

    AND AlertStatus.ObjectType like 'Hardware Sensor'

  AND APM_HardwareItem.NodeID = Nodes.NodeID

  AND AlertStatus.AlertDefID = AlertDefinitions.AlertDefID

  )

UNION ALL

SELECT DISTINCT 

   nodes.caption

      ,[ObjectName]

   ,AlertDefinitions.AlertName

      ,[TriggerTimeStamp]

      ,[TriggerTimeOffset]

   ,convert(char(10), [TriggerTimeOffset] / (60 * 60 * 24))+':'+convert(char(10), dateadd(s, [TriggerTimeOffset], convert(datetime2, '0001-01-01')), 108)

      ,[Acknowledged]

      ,[AcknowledgedBy]

      ,[AcknowledgedTime]

  FROM

  [Orion].[dbo].[AlertStatus]

  ,[Orion].dbo.Nodes

  ,[Orion].dbo.AlertDefinitions

  ,[Orion].dbo.APM_ApplicationAlertsData

  where

  (APM_ApplicationAlertsData.ID = AlertStatus.ActiveObject

    AND AlertStatus.ObjectType = 'APM: Application'

  AND APM_ApplicationAlertsData.NodeID = Nodes.NodeID

  AND AlertStatus.AlertDefID = AlertDefinitions.AlertDefID

  )

UNION ALL

SELECT DISTINCT 

   nodes.caption

      ,[ObjectName]

   ,AlertDefinitions.AlertName

      ,[TriggerTimeStamp]

      ,[TriggerTimeOffset]

   ,convert(char(10), [TriggerTimeOffset] / (60 * 60 * 24))+':'+convert(char(10), dateadd(s, [TriggerTimeOffset], convert(datetime2, '0001-01-01')), 108)

      ,[Acknowledged]

      ,[AcknowledgedBy]

      ,[AcknowledgedTime]

  FROM

  [Orion].[dbo].[AlertStatus]

  ,[Orion].dbo.Nodes

  ,[Orion].dbo.AlertDefinitions

  where

  (Nodes.NodeID = AlertStatus.ActiveObject

    AND AlertStatus.ObjectType like '%Node%'

  AND AlertStatus.AlertDefID = AlertDefinitions.AlertDefID

  )

UNION ALL

SELECT DISTINCT 

   nodes.caption

      ,[ObjectName]

   ,AlertDefinitions.AlertName

      ,[TriggerTimeStamp]

      ,[TriggerTimeOffset]

   ,convert(char(10), [TriggerTimeOffset] / (60 * 60 * 24))+':'+convert(char(10), dateadd(s, [TriggerTimeOffset], convert(datetime2, '0001-01-01')), 108)

      ,[Acknowledged]

      ,[AcknowledgedBy]

      ,[AcknowledgedTime]

  FROM

  [Orion].[dbo].[AlertStatus]

  ,[Orion].dbo.Nodes

  ,[Orion].dbo.AlertDefinitions

  ,[Orion].dbo.APM_ApplicationAlertsData

  where

  (APM_ApplicationAlertsData.ID = AlertStatus.ActiveObject

    AND AlertStatus.ObjectType = 'APM: Component'

  AND APM_ApplicationAlertsData.NodeID = Nodes.NodeID

  AND AlertStatus.AlertDefID = AlertDefinitions.AlertDefID

  )

  order by 4