6 Replies Latest reply on Apr 1, 2014 1:31 PM by scottfraley

    AlertStatus and Events mapping?


      We are trying to generate tickets based on alerts pulled from the AlertStatus table, but AlertStatus entries don't seem to have enough information. We then took a look at the Events table and noticed that the Message column contained the exact info we need, which is of course part of the Alert Definition.


      Is there some way to get the message that we're seeing in the Message column of the Events table into the AlertStatus.AlertMessage column ?  We can't seem to find any mapping between the two tables.. and/or don't know how to set up our alerts to write that info into the AlertMessage column.



        • Re: AlertStatus and Events mapping?
          Jeff Catlin

          This was addressed a little earlier this month which makes it seem like it isn't possible.

          Extracting alert messages


          I have had some minor success in joining the tables trying to tie in the events.message field using the following:

          events.netobjectid=alertstatus.activeobject and events.eventtime=alerts.triggertimestamp


          In some queries where I look for a specific netobjectid it works, but in most cases in my lab environment the query throws an error due to a field type mismatch in the query.  I haven't had time to isolate that problem yet.


          • Re: AlertStatus and Events mapping?

            Hey SolarWinds guys, any chance you can help me on this?  I mean, how is it even possible that we're defining Alerts, yet the alerts we get don't contain the info we put into the definition?!?!  This is making ticket creation pretty much useless, at least in some cases.


            -Scott F.

              • Re: AlertStatus and Events mapping?

                What appears in the event log is based on the trigger action.   What Orion shows you is the name of the alert definition.   You can get that by using the following SWQL query


                SELECT Def.Name, AlertStatus.ActiveObject, AlertStatus.ObjectType, ObjectName

                FROM Orion.AlertStatus

                JOIN Orion.AlertDefinitions Def ON AlertStatus.AlertDefID = Def.AlertDefId

                WHERE State = 2


                ObjectType gives you the type of object, e.g. Node, Interface, APM component, etc..

                ObjectName gives you the name of the object

                ActiveObject gives you the ID of the object

                  • Re: AlertStatus and Events mapping?

                    Right, I get that. The problem is that the name of the alert definition is useless. We need to be able to get the info that apparently shows up in the Events table, into the actual alert(s). What I don't get is why the alert doesn't contain the info but the event does, yet AlertStatus is the table we were told to use when handling alerts. AND, these two tables are apparently not linked in any reliable way so that we could at least do a join to get the needed data.


                    And then the cherry on the top is the fact that there is an AlertMessage column that comes up EMPTY for every alert we have!  VERY confused by this. Makes no sense to me at all.


                    As an example; in my Events I see a message like "Interface Ethernet1/10 for node CO1XNODENAMEXYZ is Down.", but in the AlertStatus table I MIGHT get the actual node name, but all to often I get "Ethernet1/10", which makes it just a bit difficult to figure out which node the alert has fired against.




                    Scott F.

                • Re: AlertStatus and Events mapping?

                  instead of focusing on what the tool cannot do, how about we consider ways to achieve what you do want: the ability to create a ticket from a SolarWinads Alert.


                  What mechanisms do you have to create a ticket?

                  How can you invoke those methods?

                  How can you populate them with the information needed?


                  Here, today, I use email to create tickets; I populate the email message with fields that contain the information needed in he ticket, and then use an alert action to create such an email.

                  In the future we might use a SOAP or JSON method to create a ticket.

                    • Re: Re: AlertStatus and Events mapping?

                      Fair enough. I guess I was kinda trying to hint really hard that SW should look into improving the Alerts story.


                      Anyhow, what we're doing for the moment is adding separate queries for each alert that will query the appropriate table based on the AlertStatus.ObjectType field in order to try to get the parent NodeName.


                      Here are the queries;


                      "SELECT N.NodeName FROM Orion.HardwareHealth.HardwareItem AS HHI LEFT OUTER JOIN Orion.Nodes AS N ON N.NodeID = HHI.NodeID WHERE HHI.ID = {0}"
                      "SELECT N.NodeName FROM Orion.Routing.Neighbors ORN LEFT OUTER JOIN Orion.Nodes AS N ON N.NodeID = ORN.NodeID WHERE ORN.IsDeleted = false AND NeighborID = {0}"
                      "SELECT N.NodeName FROM Orion.Nodes N WHERE N.Interfaces.InterfaceID = {0}"
                      "SELECT N.NodeName FROM Orion.NPM.MulticastRouting.GroupNodes AS MR LEFT OUTER JOIN Orion.Nodes AS N ON N.NodeID = MR.NodeID WHERE MR.MulticastGroupNodeID = {0}"


                      Where {0} is the value in the AlertStatus.ActiveObject field.


                      Hopefully this will be of some help to others.


                      Longer term, we're looking at writing an 'external program' that we can 'pipe' all the data we want into that will then parse said data and call a SPROC, which will put the data into our ticketing system's database.



                      -Scott F.