Integrate Google Charts with Solarwinds reports/view, A Pie chart example

Version 3

    When built reports with Solarwinds report builder we only have plain data tables. It would be very informative if we could add some customised charts, such pie charts in the reports. It is possible to use MSSQL report builder to create more richly reports but this involves installing additional components on MSSQL server and you need access the Solarwinds Database directly.

     

    I start to use Google Chart recently for my “Raspberry  Pi “ projects and suddenly realize I could Integrating the Google Chart into the Solarwinds reports/views. Here is an example:

     

    pie01.jpg

    Prerequisite: 
    You need an IIS web server supporting ASP and can access both your Solarwinds Website and Internet. You can use Solarwinds web server if it can access Internet.

     

    Step 1: Build a report using Solarwinds Report Builder


    Report Title:  “Solarwinds Statistic - Node Polling Method”
    In my example I am using “Advanced SQL”, the query is:

     

    Query
    SELECT
    (SELECT COUNT(Nodes.NodeID) FROM Nodes)  AS Total_Nodes,
    (SELECT COUNT(Nodes.NodeID) FROM Nodes where ObjectSubType = 'ICMP')  AS ICMP,
    (SELECT COUNT(Nodes.NodeID) FROM Nodes where ObjectSubType = 'SNMP' and SNMPVersion = 1)  AS SNMPv1,
    (SELECT COUNT(Nodes.NodeID) FROM Nodes where ObjectSubType = 'SNMP' and SNMPVersion = 2)  AS SNMPv2,
    (SELECT COUNT(Nodes.NodeID) FROM Nodes where ObjectSubType = 'SNMP' and SNMPVersion = 3)  AS SNMPv3,
    (SELECT COUNT(Nodes.NodeID) FROM Nodes where ObjectSubType = 'WMI' )  AS WMI
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    


    In the “Field Formatting” you need add “Web URL” for each data you want to send to Google chart.  This is for parsing data out from the report later in the asp page. Here I am using dummy URL, you can using real links to link your table/chart to another Solarwinds page.

     

    pie02.jpg

     

    Step 2: open your report from Solarwinds web console:


    The URL should looks like: "http://solarwinds-web-server-IP-or-FQDN/Orion/Report.aspx?Report=Solarwinds_Statistic_-_Node_Polling_Method&Printable=TRUE&accountid=userID&password=userPwd"

     

    Added the following parameter to the end of the URL link:
    &Printable=TRUE&accountid=userID&password=userPwd
    userID is Solarwinds user account ID, userPwd is the account password. The account should be read only.

     

    From your browser: open view page source, you should find the dummy links you created in the report, we will use these information to parse the data in the ASP page below.

     

    pie04.jpg

    Step 3:  Create an ASP page


    Create an asp page and save it to a web server which can assess Solarwinds web pages and Internet. The asp page has three parts: VBscript, JavaScript and HTML codes.

     

    The VBScript will get the report page and save the return to a string. I use the regular expression to  parse the data from the string and pass them to JavaScript. The JavaScript will call Google Chart API to draw the Pie chart in the HTML <div></div>

     

    The asp code is:

    ==================================================================

    <html>
    <head>
    <%
    ' URL link to the Solarwinds Report
    ' http://solarwinds-web-server-IP-or-FQDN/Orion/Report.aspx?Report=Solarwinds_Statistic_-_Node_Polling_Method
    ' add the following parameter to the end of the URL link: &Printable=TRUE&accountid=userID&password=userPwd"
    ' userID is Solarwinds user account ID, userPwd is the account password. The account should be read only. 
    strURL = "http://your-url/Orion/Report.aspx?Report=Solarwinds_Statistic_-_Node_Polling_Method&Printable=TRUE&accountid=user&password=userpwd"
    
    ' get the URL return text
    strHttpText = GetTextFromUrl(strURL)
    
    ' using vbs regular expression to parse the data
    
    ' get total number of nodes polling by Ping
    Set rexp01 = New RegExp
    With rexp01
          .Pattern    = "polling-ping" & Chr(34) & ">\d+</a"
          .IgnoreCase = False
          .Global    = False
    End With
    set matchs01 = rexp01.Execute(strHttpText)
    If matchs01.Count > 0 Then
        set match01 = matchs01(0)
        match01 = Replace(match01, "polling-ping" & Chr(34) & ">", "")
        match01 = Replace(match01, "</a", "")
        match01 = cInt(match01)
        'response.write match01 - debug
    Else
        match01 = 0
    End If
    
    ' get total number of nodes polling by SNMP1
    Set rexp02 = New RegExp
    With rexp02
          .Pattern    = "polling-snmp1" & Chr(34) & ">\d+</a"
          .IgnoreCase = False
          .Global    = False
    End With
    set matchs02 = rexp02.Execute(strHttpText)
    If matchs02.Count > 0 Then
        set match02 = matchs02(0)
        match02 = Replace(match02, "polling-snmp1" & Chr(34) & ">", "")
        match02 = Replace(match02, "</a", "")
        match02 = cInt(match02)
        'response.write match02 - debug
    Else
        match02 = 0
    End If
    
    ' get total number of nodes polling by SNMP2
    Set rexp03 = New RegExp
    With rexp03
          .Pattern    = "polling-snmp2" & Chr(34) & ">\d+</a"
          .IgnoreCase = False
          .Global    = False
    End With
    set matchs03 = rexp03.Execute(strHttpText)
    If matchs03.Count > 0 Then
        set match03 = matchs03(0)
        match03 = Replace(match03, "polling-snmp2" & Chr(34) & ">", "")
        match03 = Replace(match03, "</a", "")
        match03 = cInt(match03)
        'response.write match03 - debug
    Else
        match03 = 0
    End If
    
    ' get total number of nodes polling by SNMP3
    Set rexp04 = New RegExp
    With rexp04
          .Pattern    = "polling-snmp3" & Chr(34) & ">\d+</a"
          .IgnoreCase = False
          .Global    = False
    End With
    set matchs04 = rexp04.Execute(strHttpText)
    If matchs04.Count > 0 Then
        set match04 = matchs04(0)
        match04 = Replace(match04, "polling-snmp3" & Chr(34) & ">", "")
        match04 = Replace(match04, "</a", "")
        match04 = cInt(match04)
        'response.write match04 - debug
    Else
        match04 = 0
    End If
    
    ' get total number of nodes polling by WMI
    Set rexp05 = New RegExp
    With rexp05
          .Pattern    = "polling-wmi" & Chr(34) & ">\d+</a"
          .IgnoreCase = False
          .Global    = False
    End With
    set matchs05 = rexp05.Execute(strHttpText)
    If matchs05.Count > 0 Then
        set match05 = matchs05(0)
        match05 = Replace(match05, "polling-wmi" & Chr(34) & ">", "")
        match05 = Replace(match05, "</a", "")
        match05 = cInt(match05)
        'response.write match05 - debug
    Else
        match05 = 0
    End If
    
    iTotalNodesCount = match01 + match02 + match03 + match04 + match05
    
    ' Sub Function: get a URL return text
    Function GetTextFromUrl(url)
      Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.3.0")
      oXMLHTTP.Open "GET", url, False
      oXMLHTTP.Send
      If oXMLHTTP.Status = 200 Then
        GetTextFromUrl = oXMLHTTP.responseText
      End If
    End Function
    
    
    %>
    
    <!--Google Chart: Load the AJAX API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
    
    // Load the Visualization API and the piechart package.
    google.load('visualization', '1.0', {'packages':['corechart']});
    
    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(drawChart);
    
    // Callback that creates and populates a data table,
    // instantiates the pie chart, passes in the data and
    // draws it.
    function drawChart() {
        // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'NodeStatus');
        data.addColumn('number', 'Slices');
        data.addRows([
            ['PING <%= match01 %>', <%= match01 %>],
            ['SNMP1 <%= match02 %>', <%= match02 %>],
            ['SNMP2 <%= match03 %>', <%= match03 %>],
            ['SNMP3 <%= match04 %>', <%= match04 %>],
            ['WMI <%= match05 %>', <%= match05 %>]
        ]);
        // Set chart options
        var options = {title: 'Total Nodes: <%= iTotalNodesCount %>',
                    width: 550,
                    height: 250,
                    is3D: true,
                    pieSliceText: 'percentage',
                      sliceVisibilityThreshold: 0,
                    pieStartAngle: -100,
                    slices: {0:{offset: 0.2, color: 'Magenta'},
                            1: {offset: 0.1, color: 'orange'},
                            2: {color: '#0099C6'},
                            3: {offset: 0.1, color: 'yellow'},
                            4: {offset: 0.2, color: 'blue'},
                            },
        };
    
        // Instantiate and draw our chart, passing in some options.
        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, options);
    }
    </script>
    
    </head>
    
    <body>
      <!--Div that will hold the pie chart-->
      <div id="chart_div"></div>
    </body>
    </html>
    
    
    
    
    
    
    
    
    
    
    
    

    ==================================================================

     

    Step 4: In Solarwinds, create a view page and add two resources: “Report from Orion Report Writer” and “Custom HTML”

     

    Edit “Report from Orion Report Writer”, Link the report to the report you just created
    Edit “Custon HTML”, add the following code:  (replace with your asp page link)

     

    ====================================================

    <iframe src="http://link-to-your-asp-page/solarsta.asp" width=550 height=250 frameborder=0 scrolling="no"></iframe>
    
    
    
    
    
    
    
    
    
    
    

    ====================================================

     

    View your chart in the view page. Done!

     

    You can use the similar method to create different chats, such as  Node status pie chart. Also you can make the chart click-able to link to other pages.  Just add the following code in asp page after JavaScript function “chart.draw(data, options);”  An example for Node status pie chart:

     

    * Below is an image, not text. Cannot paste as text

    pie06.jpg

     

    pie05.jpg

    Hope this is helpful. Thanks

     

    Update: 01

    ==================

    As designerfx suggested, the code is  attached as a text file