2 Replies Latest reply on Dec 16, 2013 8:33 PM by acherman

    Custom Poller Alert - SQL Error All Of A Sudden

    acherman
    acherman Dec 15, 2013 6:33 PM

      Hey All,

       

      I have a few customer poller alerts that have been running for 4+ years thanks to the help from a few people here: Re: Custom Poller Alert Short explanation - all these do is put the current status, input voltage, and battery capacity of a UPS in one message when it loses AC power.

      Recently, the messages being sent from these alerts have contained an SQL error referencing an ambiguous column name.  I am no SQL expert at all, so here I am asking for some help.  Nothing has changed in either the SQL server, the Orion server, or the alerts themselves.... other than possible software updates from SW (NPM and NTA).

       

      The original alert outgoing message is written like this:

       

      UPS has been ${SQL:Select Status From CustomPollerAssignment as Assignment, CustomPollerStatus as Status where Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${NodeID} and Assignment.AssignmentName like 'CVMupsPowerSource%'} for 60 minutes.

       

      So, to make it perform better when running the query manually, I have removed all of the NPM related formatting and used a Node ID that I know works, making the SQL query look like this:

       

      Select Status From CustomPollerAssignment as Assignment, CustomPollerStatus as Status where Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = 530 and Assignment.AssignmentName like 'CVMupsPowerSource%'

       

      When I run this query I get the following with the first "Status" underlined:

      Msg 209, Level 16, State 1, Line 2

      Ambiguous column name 'Status'.

       

      Soooo.... I read some stuff about trying to use tablename.columnname, and assuming I understand the query, I tried CustomerPollerAssignment.Status, and get an error that the multi-part identifier can't be found.  This is where I get lost.

       

      Any help is appreciated.  Thanks in advance.

       

      Aaron

        • Re: Custom Poller Alert - SQL Error All Of A Sudden
          zackm
          zackm Expert Dec 16, 2013 8:02 AM (in response to acherman)

          The issue is that there is a 'Status' column present in both tables, and you need to identify which one you want to 'SELECT'

           

          Try this:

           

          SELECT STATUS.STATUS
FROM CustomPollerAssignment AS Assignment
,CustomPollerStatus AS STATUS
WHERE Assignment.CustomPollerAssignmentID = STATUS.CustomPollerAssignmentID
AND Assignment.NodeID = 530
AND Assignment.AssignmentName LIKE'CVMupsPowerSource%'

           

           

          Or, to fix your alert:

           

          UPS has been ${SQL:Select Status.Status From CustomPollerAssignment as Assignment, CustomPollerStatus as Status 
where Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${NodeID} 
and Assignment.AssignmentName like 'CVMupsPowerSource%'} for 60 minutes.

           

           

          Please note: the only change was to your SELECT statement. Status.Status = Table.ColumnName = CustomPollerStatus.Status

           

          You were very close on your fix attempt, but I am assuming you want the status of the actual polled device and not whether or not the the poller is turned on. (CustomerPollerStatus vs CustomPollerAssignment)

           

          FYI - for future reference: "ambiguous column name" errors are *always* (to my knowledge) fixed by identifying a column in your SELECT statement.

           

          Please let me know if this works and/or you have any questions!

            • Re: Custom Poller Alert - SQL Error All Of A Sudden
              acherman
              acherman Dec 16, 2013 8:33 PM (in response to zackm)

              Zach, you are my hero!!  That worked perfectly.  Thanks so much.

               

              Now, I have to wonder, what changed?  I have 8 alerts running this same query - 4 of them run it 3 times, and 4 of then run it 4 times, all to produce the UPS values I want in my alerts.  And these have been running fine the way they were for 4+ years.  So, somewhere, something had to change.

               

              Thanks again.