10 Replies Latest reply on May 3, 2016 2:58 PM by muduck18

    Add nodename in active alerts report

    spyke 62

      Hi, I want to build a report about active alerts.

       

      I already used the default report « Active Alerts » but it is incomplete for me.

       

      The node name never appear. For exemple, I have an alert active about volume space. In this report, I see only the volume’s name. Same thing with an alert « interface down », I see only Interface name. This display is useless because I don’t know what active alert corresponds to which nodes.

       

      I opened a case and support tell me currently is not possible with report manager.

      It can be possible to create SQL report to retrieve data, but support do not support custom SQL

       

      I'm not at all familiar with SQL,

      Can someone help me to create the sql query ?

       


        • Re: Add nodename in active alerts report
          neomatrix1217

          ${N=SwisEntity;M=Caption}  =Node Name

          ${N=SwisEntity;M=DisplayName}  = Display Name

           

          On the action message if you select insert variable and change your drop box in "show variables for:" from Global  --> Group by: most commonly used  to Node and then change "the output preview shows the current values on specific test object" at the very bottom and select a specific node now you can see the values from that node it's very helpful.

            • Re: Add nodename in active alerts report
              spyke 62

              Hi,

               

              In alert definition, tab "trigger action", I already added the variable ${Nodename}.

               

               

               

              With this, I can see the node name in active alert view : (tab message)

               

               

              But in active alert report I see only caption of volume.

               

              You speak select variable in "action message", this is where I indicated the variable ${NodeName) ?

            • Re: Add nodename in active alerts report
              neomatrix1217

              OK sorry my bad you need to add node to the table layout.

               

              Capture.PNG

                • Re: Add nodename in active alerts report
                  spyke 62

                  Hi,

                   

                  Ok, I edited table layout of all active alertes report but I have not the orion object " node " available, just "group"

                   

                    • Re: Add nodename in active alerts report
                      neomatrix1217

                      So that report is Group report and you cannot add node name you would need to change the report content to volume to get node name.

                        • Re: Add nodename in active alerts report
                          spyke 62

                          Hi,

                           

                          The report content is a sql query

                           

                          I feel that it's really hard to add nodename !!

                            • Re: Add nodename in active alerts report
                              neomatrix1217

                              Can you past the full SQL statement.

                                • Re: Add nodename in active alerts report
                                  spyke 62

                                  SELECT DISTINCT

                                  AlertActive.AlertActiveID, AlertObjects.AlertObjectID, AlertConfigurations.Name, AlertConfigurations.Severity, AlertConfigurations.ObjectType,

                                  AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption,

                                  AlertActive.TriggeredDateTime, AlertObjects.LastTriggeredDateTime, AlertActive.TriggeredMessage AS Message,

                                  AlertActive.AcknowledgedDateTime, AlertActive.Acknowledged AS Acknowledged, AlertActive.AcknowledgedBy, AlertActive.AcknowledgedNote,

                                  Case

                                      When Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400)>0 Then

                                          ToString(ToString(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) +0.0)/86400))+'d '+

                                          ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+

                                          ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

                                      When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)>0 Then

                                          ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+

                                          ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

                                      When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)>0 Then

                                          ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

                                      Else ''

                                  End AS ActiveTime

                                  FROM Orion.AlertObjects (nolock=true) AlertObjects

                                  INNER JOIN Orion.AlertActive (nolock=true) AlertActive ON AlertObjects.AlertObjectID=AlertActive.AlertObjectID

                                  INNER JOIN Orion.AlertConfigurations (nolock=true) AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID

                                  Order By AlertConfigurations.Name, AlertObjects.EntityCaption

                        • Re: Add nodename in active alerts report
                          muduck18

                          I have this same question Spyke,

                          Any chance you found a work-around for it?

                           

                          I can get NODE ID which is a number, but I can't seem to get the actual node name, which is more "useful" in this scenario.