I am using Google visualization GeoChart to color code US states based on quantity network alerts by state. I get these alerts through Solarwinds Orion where i have a custom Attribute of all my nodes assigned to a State. When the node goes down, it triggers an alert, and the node's assigned state is also included. Problem i am trying to solve is that I some states that there are zero alerts for, which i can set to a default color of Green, but there are states that we do not operate in and I want those to be excluded from any color at all, and just have a deparate color of grey, null.
I added a link at the bottom to an image of my Geochart. for example. I have things in California but there are no alerts, so i want it to be green. But i do not have anything in Montana, so i want that state to show grey. I have all the code working to paint the states that do have alerts triggered and I do have a "default" background color for any state with zero alerts except i cannot figure out the code to exclude certain states that we have no presence it at all that i want to be grey or null in color.
Here's my code...
<!--Begin GeoMap code-->>
var swql="\
SELECT SUBSTRING(TOSTRING(DATETRUNC('Day',TimeStamp)),0,12) AS Day,\
COUNT(*) AS Qty,\
ISNULL(AlertHistory.AlertObjects.Node.CustomProperties.State,'Unknown') AS State\
FROM Orion.AlertHistory\
WHERE YEAR(TOLOCAL(TimeStamp)) = YEAR(GETDATE()) AND MONTH(TOLOCAL(TimeStamp)) = "+querymonth+ "\
AND\
DAY(TOLOCAL(TimeStamp)) = "+querydate+" AND EVENTTYPE='0'\
GROUP BY DATETRUNC('Day',TimeStamp),AlertHistory.AlertObjects.Node.CustomProperties.State\
ORDER BY DATETRUNC('Day',TimeStamp) DESC,QTY DESC"
var params = JSON.stringify({
query: swql,
parameters: {
}
});
$.ajax({
type: 'POST',
url: '/Orion/Services/Information.asmx/QueryWithParameters',
data: params,
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function(response) {
console.log(response.d.Rows);
google.charts.load('current', {'packages':['geochart'],'mapsApiKey': 'MyGoogleAPIKeyRemoved'});
google.charts.setOnLoadCallback(drawRegionsMap);
function drawRegionsMap() {
var dataTable = new google.visualization.DataTable();
dataTable.addColumn({ type: 'string', id: 'State' });
dataTable.addColumn({ type: 'number', id: 'Qty' });
for(var i=0; i < response.d.Rows.length; i++){
var row = [response.d.Rows[i][2],response.d.Rows[i][1]];
dataTable.addRow(row);
}
var options = {
region: 'US',
resolution: 'provinces',
colorAxis: {colors:['yellow','red']},
datalessRegionColor: '#AADAA6',
legend: 'none',
};
var chart = new google.visualization.GeoChart(document.getElementById('regions_div'));
chart.draw(dataTable, options);
}
}
})
<!--end GeoMap code-->>