6 Replies Latest reply on Apr 7, 2014 2:57 PM by Detroiter

    Dashboard : SQL query

    jof300


      Hello everybody,

       

      We are currently migrating nagios in solarwinds. Nagios dashboards are easy views to understand.

       

      I would like to have same views in Solarwinds. I tried to create custom reports and display them but I can't display every info I wanted.

       

      In fact, I would like (to start with) a view like this :

       

       

      HostAny alertStatusDurationDepencies impactedDepencies nodeAcknowlegde
      host1DB xxx downCRITICAL60 minintranet downhost2ack alert

       

      Then, I could add some other fields later .

      I know that kind of report / dashboard is quite heavy SQL's.

       

      Anyone has already done this ?

       

      Thanks in advance.


      Regards

        • Re: Dashboard : SQL query
          aLTeReGo

          This is a question probably best suited for the Report Lab forum.

          • Re: Dashboard : SQL query
            bluefunelemental

            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.

            • Re: Dashboard : SQL query
              jof300

              Hello,

               

              I come back with this request.

               

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

               

              Any one has an idea?

               

              Regards

                • Re: Dashboard : SQL query
                  Detroiter

                  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)

                  AlertDashboard.jpg

                   

                  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