3 Replies Latest reply on Jan 7, 2020 11:18 AM by wex

    SQL Component Monitor graphing return instead of showing up/down/unknown

    wex

      I have an oracle user experience monitor polling an outside database. It is correctly returning 0 for Up, 1 for Down, and 4 for unknown.

      instead of showing these states, the Component Monitor is graphing 0, 4 , and showing critical for 1. I don't see this as normal anywhere in the SAM documentation.

       

      I have tried to add "'statistic:'," in the select statement, pass the data as 'statistic: 1', 'statistic.1:1', and 'statistic.1: 1', and pas the data as 'message: 1' but all of them cause an error.{ }

        • Re: SQL Component Monitor graphing return instead of showing up/down/unknown
          serena

          wex  wrote:

           

          I have an oracle user experience monitor polling an outside database. It is correctly returning 0 for Up, 1 for Down, and 4 for unknown.

          instead of showing these states, the Component Monitor is graphing 0, 4 , and showing critical for 1. I don't see this as normal anywhere in the SAM documentation.

           

          I have tried to add "'statistic:'," in the select statement, pass the data as 'statistic: 1', 'statistic.1:1', and 'statistic.1: 1', and pas the data as 'message: 1' but all of them cause an error.{ }

          Would you like to share the SQL query that you're inputting into the SQL component monitor?

            • Re: SQL Component Monitor graphing return instead of showing up/down/unknown
              wex

              The monitor SQL is:

              {

              SELECT CASE WHEN DISPLAY_NAME = 'CDC' THEN 0

              WHEN DISPLAY_NAME = 'SCC' THEN 4

              ELSE 1

              END AS S1

              FROM RTD_ALARMS

              WHERE NAME = 'OAG'

              }

               

              It is still returning as a graphed value so I had to build a work around by making the screen as a custom HTML CSS page. I use Ajax to query the component monitor return and convert it to a color that is then assigned to an object.

               

              {

              var oag = Fetch_SQL("

                   SELECT ComponentName,

                        CASE WHEN StatisticData = 0 THEN 'limegreen'

                        WHEN StatisticData = 1 THEN 'red'

                        WHEN StatisticData = 2 THEN 'yellow'

                        ELSE 'grey' END as color

                   FROM Orion.APM.ComponentAlert

                   WHERE ComponentName like '%OAG-%'

                   ORDER BY ComponentName"); // Fetch Value from a component

               

              document.getElementById("PT_OAG").style.backgroundColor = oag[0][1] // Set color of div=id name "PT_OAG"

              } // Note: I don't ever want it to be yellow but I reuse this case string.

               

              This can't be the correct way to make an object turn grey...

               

              Thanks for taking a look.

              1 of 1 people found this helpful
                • Re: SQL Component Monitor graphing return instead of showing up/down/unknown
                  wex

                  I don't know how "cool" it is to reply to my own post, but it would seem my javascript workaround is the best solution.

                  For future code clobberers (such as myself). Here is the Fetch_SQL() function.

                   

                  [code]

                  <div id="DIV_ID" class="DIV_ID"></div> // div color to be set. size and shape should be set by CSS

                   

                  <script type="text/javascript" >

                  function Fetch_SQL(sql) { // Supply an SQL statement and an array of rows will be returned

                      var VAL = "";

                      $.ajax({

                          async: false, // If synchronous response is allowed, return will not work

                          type: "POST",

                          url: '/Orion/Services/Information.asmx/Query',

                          data: JSON.stringify({query:sql}),

                          contentType: "application/json; charset=utf-8",

                          dataType: "json",

                          success: function (response) { //acction upon success

                              VAL = response.d.Rows;

                          }

                      });

                      return VAL;

                  }

                  var VariableName = Fetch_SQL("SELECT {column name}, CASE WHEN {second column name}= 0 THEN 'limegreen' WHEN {second column name}= 1 THEN 'red' WHEN {second column name}= 2 THEN 'yellow' ELSE 'grey' END as color FROM ....");

                  document.getElementById("DIV_ID").style.backgroundColor = VariableName[0][1]

                  </script>

                   

                  [/code]

                  Generally I return two columns [identifier][color] for trouble shooting, so the next VariableName would be VariableName[1][1],

                  if you only return one column then you would want VariableName[0],VariableName[1]

                  1 of 1 people found this helpful