Analyse JunOS XML output using PHP

Tags

junos, PHP, XML

In JunOS you can export any output in XML. For example you can try:

show route protocol bgp | display xml

This will show you all the BGP routing table but in XML format. Which will look like:

<rpc-reply xmlns:junos="http://xml.juniper.net/junos/12.3R4/junos">
<route-information xmlns="http://xml.juniper.net/junos/12.3R4/junos-routing">
<!-- keepalive -->
<route-table>
<table-name>inet.0</table-name>
<destination-count>547112</destination-count>
<total-route-count>547137</total-route-count>
<active-route-count>547112</active-route-count>
<holddown-route-count>0</holddown-route-count>
<hidden-route-count>1</hidden-route-count>
<rt junos:style="brief">
<rt-destination>0.0.0.0/0</rt-destination>
<rt-entry>
<active-tag> </active-tag>
<protocol-name>BGP</protocol-name>
<preference>170</preference>
<age junos:seconds="106012">1d 05:26:52</age>
<local-preference>100</local-preference>
<as-path>10102 9498 I
</as-path>
<validation-state>unknown</validation-state>
<nh>
<selected-next-hop/>
<to>103.4.109.169</to>
<via>ge-1/0/0.0</via>
</nh>
</rt-entry>
</rt>

Google Chart from dynamic data

Tags

Dynamic Data, Google Chart, mysql, PHP

I have found Google Chart very interesting while creating chart from dynamically driven data. I have tried to put things together for better understanding.

 

1. Include mysql connection string:

<?php </span>

include “conn_string.php”;

?>

2. Load the AJAX API:

script type=”text/javascript” src=”jquery-1.7.1.min.js”>

// /javascript”>

3. Load the Visualization API for Column Chart

google.load(‘visualization’, ‘1’, {‘packages’:[‘corechart’]});

4. Set a callback to run when the Google Visualization API is loaded

  google.setOnLoadCallback(drawChart);

  function drawChart() {

     var data = google.visualization.arrayToDataTable([

     [‘Time’, ‘Bots’, ‘Openresolvers’, ‘Proxy’, ‘Malwareurl’, ‘Phishing’, ‘Bruteforce’, ‘Scanners’, ‘Spam’]

     <?php <o:p>

     //$sql_query = “SELECT DATE_FORMAT(time, ‘%Y-%m-%d’) AS time,report,count(ip) AS countip FROM asn_data WHERE DATE_FORMAT(time, ‘%Y-%m-%d’

) > ‘2012-10-31’ GROUP BY DATE_FORMAT(time,’%Y-%m-%d’),report”;

     $sql_query = “

          SELECT DATE_FORMAT(time, ‘%Y-%m-%d’) AS time,

          SUM(CASE WHEN report = ‘bots’ THEN ipc ELSE 0 END) AS bots,

          SUM(CASE WHEN report = ‘openresolvers’ THEN ipc ELSE 0 END) AS openresolvers,

          SUM(CASE WHEN report = ‘proxy’ THEN ipc ELSE 0 END) AS proxy,

          SUM(CASE WHEN report = ‘malwareurl’ THEN ipc ELSE 0 END) AS malwareurl,

          SUM(CASE WHEN report = ‘phishing’ THEN ipc ELSE 0 END) AS phishing,

          SUM(CASE WHEN report = ‘bruteforce’ THEN ipc ELSE 0 END) AS bruteforce,

          SUM(CASE WHEN report = ‘scanners’ THEN ipc ELSE 0 END) AS scanners,

          SUM(CASE WHEN report = ‘spam’ THEN ipc ELSE 0 END) AS spam

          FROM (

          SELECT count(ip) AS ipc, report, DATE(time) as time

          FROM  asn_data

          GROUP BY report, DATE(time)) i

          GROUP BY time;

”;

 

     $result = mysql_query($sql_query);

 

     while($row = mysql_fetch_assoc($result)){

          echo “,[‘{$row[‘time’]}’,{$row[‘bots’]},{$row[‘openresolvers’]},{$row[‘proxy’]},{$row[‘malwareurl’]},{$row[‘phishing’]},{$row[‘br

uteforce’]},{$row[‘scanners’]},{$row[‘spam’]}]rn”;

     }

     ?>

     ]);

Lets explain what I have done here. First we have create a function name drawChart() and fetch the required data from mysql. As per Google Chart documentation, bellow is the format of data source:

[ ‘Month, ‘Dhaka’, ‘Chittagong’, ‘Sylhet’, ‘Khulna’ ]

[ ‘2011/05’, 150,     200,          210,       130  ]

[ ‘2011/06’, 180,     190,          215,       210  ]

[ ‘2011/07’, 196,     176,          190,       155  ]

To match format we have writer down the mysql query in such way that it will give output in desired format. Later using while loop we echo the output.

 

5. Options for the graph:

var options = {

          title : ‘Category Counts’,

          vAxis: {title: “Counts”},

          hAxis: {title: “Date”},

          seriesType: “bars”,

          series: {5: {type: “line”}}

        };

6. Call the variable and create graph:

var chart = new google.visualization.ComboChart(document.getElementById(‘chart_div’));

chart.draw(data, options);

7. Lastly use HTML tag to print the chart_div.

You can get the complete script from http://pastebin.com/HbfAdFas

Here is few sample graphs: