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

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.{ No valid data was received. Unable to convert query result. }

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

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.

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]