cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Google Charts | Pie Charts

Thanks to this post I was able to re-write some code for a simple Custom HTML Widget that can modified relatively easily to create some pie chart widgets.

So, thank you wluther​ for the code and allowing me to quickly learn some AJAX / Google Charts API to adapt to something less robust than a nested charts and a calendar view of alerts by day.

pastedImage_0.png

From line 5 of the attached file:

Modify this SWQL query to your liking, biggest piece is it should be 2 columns only here I have Machine Type and Qty (Count of NodeID's per Machine Type)

In this Query I have explicitly called out the Machine Types I care about

var swql="SELECT n.MachineType, count(n.NodeID) as Qty FROM Orion.Nodes as n Where n.MachineType = 'IBM PowerPC' OR n.MachineType = 'net-snmp - Linux' OR n.MachineType LIKE 'Red Hat%' OR n.MachineType LIKE 'Windows 20%' OR n.MachineType like 'Sun Microsystems%' OR n.MachineType like '%Unknown%' GROUP BY n.MachineType ORDER BY Qty DESC"

From lines 31-34 of the attached file

This will go through each row of the results from the SWQL query and add it to a row for the pie chart.

I needed to modify the "var row = " line to below

for(var i=0; i < response.d.Rows.length; i++){
var row = [response.d.Rows[i][0],response.d.Rows[i][1]];
dataTable.addRow(row);
}

From lines 38-44 Chart options were modified. You can get all the options from Googles site on this: Google Charts

var options = {
            title: 'Operating Systems',
            colorAxis: {colors:['#86ce76','#d61007']},
                            legend: {position: 'right'},
                            tooltip: {trigger: 'selection'},
                            chartArea:{left:2,top:2,width:500,height:500},
            };

From lines 52-54 some modifications to the DIV at the end to make it match a size I felt was good

<div id="container" style="width: 500px; height: 215px;">
<div id="server_os" style="width: 500px; height 215px;"></div>
</div>

With this you'll be able to; hopefully, modify the code to your liking for the SWQL Query as well as what chart you would like.

Attachments
Comments

very niceee..

how if wanna show node is down / up in PieChart?

Something like so should get what you need, I tossed in unmanaged as well. if you don't need that one then take out that line

Select COUNT(CASE WHEN n.StatusDescription like '%up%' then 1 else null end) as UpNodes, COUNT(CASE WHEN n.StatusDescription like '%down%' then 1 else null end) as DownNodes, COUNT(CASE WHEN n.StatusDescription like '%unmanaged%' then 1 else null end) as UnmanagedNodes

FROM Orion.Nodes as n

Hello! Thanks for this. Is there a way to do this with Groups and show up/down? Thanks in advance!

I believe the table you're looking for may be Orion.Groups, if so, use one of the SQL Queries below and you'll have a chart that shows the desired status.

Up/Down Only:

SELECT COUNT(CASE WHEN StatusDescription like '%up%' then 1 else null end) as Up, COUNT(CASE WHEN StatusDescription like '%down%' then 1 else null end) as Down FROM Orion.Groups

Up/Down/Warning/Unmanaged:

SELECT COUNT(CASE WHEN StatusDescription like '%up%' then 1 else null end) as Up, COUNT(CASE WHEN StatusDescription like '%down%' then 1 else null end) as Down, COUNT(CASE WHEN StatusDescription like '%warning%' then 1 else null end) as Warning, COUNT(CASE WHEN StatusDescription like '%unmanaged%' then 1 else null end) as Unmanaged FROM Orion.Groups

There is an error with this request, how will it be more correct?

Getting a blank html page. I'm trying to get a Pie Chart to display how many of my devices are currently using the different types of versions of SNMP. When I execute the query I get data back, but trying to use your code and adapt hasnt worked. Do you mind giving me a hand?

SELECT 

    CASE WHEN SNMPVersion = 1 THEN 'v1' 

         WHEN SNMPVersion = 2 THEN 'v2c' 

         WHEN SNMPVersion = 3 THEN 'v3' 

    END AS SNMPVersionUsed 

    ,COUNT(1) AS Qty 

FROM Orion.Nodes 

WHERE ObjectSubType = 'SNMP' 

GROUP BY SNMPVersion

Node Status PIE chart

pastedImage_0.png

SWQL:

SELECT s.statusname as Node_Status, COUNT (n.status) as QTY FROM Orion.Nodes n INNER JOIN Orion.StatusInfo s ON s.StatusID = n.Status GROUP BY s.StatusName ORDER BY Node_Status DESC

may share full code?

Hello Elevate - I keep getting a blank widget when I plug in the below SWQL query into the code.  Was there something else you did in the code to get it to display?

SELECT COUNT(CASE WHEN StatusDescription like '%up%' then 1 else null end) as Up, COUNT(CASE WHEN StatusDescription like '%down%' then 1 else null end) as Down, COUNT(CASE WHEN StatusDescription like '%warning%' then 1 else null end) as Warning, COUNT(CASE WHEN StatusDescription like '%unmanaged%' then 1 else null end) as Unmanaged FROM Orion.Groups

If the widget is blank then you're probably missing something small somewhere.

For starters though, i'd verify the SWQL Query returns records for your environment, plug the SWQL Query in to SWQL Studio and see if it returns anything.

Example results from my environment:pastedImage_0.png

Once that has been verified, i'd double check the syntax in the full code (apologies for any of you that may be familiar with this already), there is a chance that something could be getting crossed up with the use of " and ' quotation marks. I'd use " on the outside and ' on the inside

"this is an 'example' of how i'd use ' quotes inside double quotes"

Lastly, if you modified any of the options on lines 38-44, ensure you have a comma at the end of your lines. Use the example above or the attachment on the post to see where commas should be.

Again, if you have modified any of the options on lines 38-44 there is a chance what you put in there is causing the widget to appear blank as well, try less options to see if one is the culprit.

I hope this gets you where you need to be, i'll try to monitor this a little more closely. I haven't been getting in to THWACK as much as I used to

If anyone from the earlier posts is still having issues please send me a message or a new comment on here and i'll try and help out.

This is the full code that I use to show up/down/managed status:

Untitled picture.png

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

<script type="text/javascript">

 

var swql="SELECT s.statusname as Node_Status, COUNT(n.status) as QTY FROM Orion.Nodes n INNER JOIN Orion.StatusInfo s ON s.StatusID = n.Status WHERE s.StatusID IN (1,2,3) GROUP BY s.StatusName ORDER BY Node_Status 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:['corechart']});

google.charts.setOnLoadCallback(drawChart);

var i;

function drawChart() {

var data = new google.visualization.DataTable();

data.addColumn({ type: 'string', id: 'Event Type' });

data.addColumn({ type: 'number', id: 'Total Events' });

data.addColumn({ type: 'string', role: 'annotation'});

for(var i=0; i < response.d.Rows.length; i++){

var row = [response.d.Rows[i][0],response.d.Rows[i][1],response.d.Rows[i][2]];

data.addRow(row);

//console.log(response.d.Rows);

}

var options = {

title: 'System Status' ,

is3D: false,                                     

};

var chart = new google.visualization.PieChart(document.getElementById('piechart'));

chart.draw(data, options);

}

}

})

    </script>

<div id="piechart" style="width: 700px; height: 310px;"></div>

If you want all status' to show, delete the part that says "WHERE s.StatusID IN (1,2,3)"

If you want to change color, let me know.

This is just from me asking questions and doing searches online.

This is interesting, I am able to create the chart as per my requirements. However running into below issue. My goal is to keep 2 pie charts, one to show the network device availability and other to show its type. I am unable to keep both pie chart under one view. the moment i edit the custom html resource the other one is getting replaced. 

Is there any Limitations? They work if i keep them in different views.

 

johnwilly_0-1590663914331.png

johnwilly_1-1590664204688.png

 

 

@pipperoni How did you make it so that the colors are not random?

@jellobones Oh boy, that was so long ago. Why didn't I just include it? I don't like it when other people hint like that, haha.

In the example below, I used the colors of my junior high (not on purpose). You would simply add the "slices line" under var options. I put mine under title:

Annotation01.png

 

slices: {0: {color: '#641E16'}, 1:{color: 'orange'}, 2:{color: '#27AE60'}},

 

 You can use any hex color code, or you can keep it basic and just use names like 'blue' or 'orange'. Add or remove based on the number of slices. I hope this makes sense. I am no closer to being a pro than when I last posted. I am still a pre-beginner. Let me know if you need some clrification.

Annotation02.png

Thank you so 0:, 1: and 2: are positions.  I thought you had to where UP = Green, Down = Red, Unknown = Gray.

@jellobones Annotation 4.png

Its from the swql at the top!

The colors of the slices seem to be randomly assigned to values.  I was looking to be able to assign Green to UP, Red to Down, Yellow to Warning, so on.  Is that at all possible?

@jellobones Hmmm, I am not sure. If you assign green to 0, does that not make UP green?

Version history
Revision #:
1 of 1
Last update:
‎12-17-2018 11:54 AM
Updated by: