3 Replies Latest reply on Sep 15, 2010 3:12 PM by maladil

    Page customization question ( sql stuff )

    maladil

      I've done a fair bit of php / mysql scripting a few years ago and want to customize some solarwinds pages.  I'm looking through the files in the Orion directory and don't seem to see examples of queries to the database.  I'm just trying to start simple and create a simple "select nodes where status = down  from db" type thing to get rolling, but that is even a problem.    Can someone explain the basics of how to use existing solarwinds code in order to execute my own query in a customized page?

        • Re: Page customization question ( sql stuff )
          NTeam

          Hey Maladil,

          You may want to browse the thwack content exchange for NPM. There are quite a few resources on there where ppl are manipulationg the solarwinds pages.

          I haven't created one from scratch yet but i have manipulated a few of the provided resources to benefit my setup.

          Michael

          • Re: Page customization question ( sql stuff )
            darryld

            Many of the pages supplied by Solarwinds separate the front end web control from the back end code.

            When creating simple resources I prefer to use a single file which contains both elements. There are a number of examples of the this type of file elsewhere in thwack but below is the one I use

             

            ++++

            <%@ Control Language="C#" ClassName="EnhAlerts" Inherits="SolarWinds.Orion.Web.UI.BaseResourceControl" %>

            <%@ Import Namespace="System.Data.SqlClient"%>

            <%@ Import Namespace="SolarWinds.Orion.Common"%>

            <%@ Import Namespace="SolarWinds.Orion.Web.DAL"%>

            <%@ Import Namespace="System.Data"%>

            <%@ Register TagPrefix="orion" Namespace="SolarWinds.Orion.Web.Controls" %>

             

            <script runat="server">

             

                protected void Page_Load(object sender, EventArgs e)

                {

                    string filter = this.Resource.Properties["Filter"];

             

                    DataTable table;

             

                    try

                    {

                        string sql = @"

            SELECT     AlertDefinitions.AlertName, Nodes.Caption, AlertStatus.TriggerTimeStamp

            FROM         AlertDefinitions INNER JOIN

                                  AlertStatus ON AlertDefinitions.AlertDefID = AlertStatus.AlertDefID INNER JOIN

                                  Nodes ON AlertStatus.ActiveObject = Nodes.NodeID

            WHERE     AlertDefinitions.AlertName = 'NodeDown'

            ORDER BY  AlertStatus.TriggerTimeStamp DESC

            ";

                        if (!String.IsNullOrEmpty(filter))

                            sql = string.Format(sql, "AND " + filter.Replace('*', '%').Replace('?', '_'));

                        else

                            sql = string.Format(sql, "");

                            

                        using (SqlCommand cmd = SqlHelper.GetTextCommand(Limitation.LimitSQL(sql)))

                        {

                            table = SqlHelper.ExecuteDataTable(cmd);

                        }

                    }

                    catch (SqlException)

                    {

                        this.SQLErrorPanel.Visible = true;

                        return;

                    }

             

                    this.enhAlertsTable.DataSource = table;

                    this.enhAlertsTable.DataBind();

                }

             

             

             

             

             

                protected override string DefaultTitle

                {

                    get { return "Down Node (Time Ordered)"; }

                }

             

                public override string HelpLinkFragment

                {

                    get { return "OrionPHResourceDownNodes"; }

                }

             

                public override string EditURL

                {

                    get

                    {

                        string url = String.Format("/Orion/NetPerfMon/Resources/FilterEdit.aspx?ResourceID={0}&ViewID={1}&HideInterfaceFilter=True",

                            this.Resource.ID, this.Resource.View.ViewID);

                        if (!String.IsNullOrEmpty(this.Request.QueryString["NetObject"]))

                        {

                            url += "&NetObject=" + this.Request.QueryString["NetObject"];

                        }

             

                        return url;

                    }

                }

             

                public override string SubTitle

                {

                    get

                    {

                        string subTitle = base.SubTitle;

                        if (String.IsNullOrEmpty(subTitle))

                        {

                            return "The Following Nodes Are Down (Latest ones listed first)";

                        }

                        return subTitle;

                    }

                }

            </script>

             

            <orion:resourceWrapper runat="server" ID="wrapper">

                <Content>

                    <asp:Panel ID="SQLErrorPanel" runat="server" Visible="false">

                        <table cellpadding="10px">

                            <tr>

                                <td style="font-weight: bold; font-size: small; color: Red">

                                    Custom SQL filter is incorrectly formated.

                                </td>

                            </tr>

                        </table>

                    </asp:Panel>

                    <asp:Repeater runat="server" ID="enhAlertsTable">

                        <HeaderTemplate>

                            <table border="0" cellpadding="2" cellspacing="0" width="100%" >

                                <tr>

                                   <th class="ReportHeader">AlertName</th>

                                   <th class="ReportHeader">Time</th>

                                    <th class="ReportHeader">Node Name</th>

             

                                </tr  >

                        </HeaderTemplate>

                        <ItemTemplate>

                            <tr >

                         <td class="Property"><%# Eval("AlertName") %></td>              

                                  <td class="Property"><%# Eval("TriggerTimeStamp") %></td>

                                  <td class="Property"><%# Eval("Caption") %></td>

                              </tr>

                        </ItemTemplate>

                        <FooterTemplate>

                            </table>

                        </FooterTemplate>

                    </asp:Repeater>

             

             

                </Content>

            </orion:resourceWrapper>

            +++++
            The sql statement is in lines 18 to 25
            ie 

                    try

                    {

                        string sql = @"

            SELECT     AlertDefinitions.AlertName, Nodes.Caption, AlertStatus.TriggerTimeStamp

            FROM         AlertDefinitions INNER JOIN

                                  AlertStatus ON AlertDefinitions.AlertDefID = AlertStatus.AlertDefID INNER JOIN

                                  Nodes ON AlertStatus.ActiveObject = Nodes.NodeID

            WHERE     AlertDefinitions.AlertName = 'NodeDown'

            ORDER BY  AlertStatus.TriggerTimeStamp DESC

            ";

             

            My query returns 3 variables which need to be displayed.

            Lines 101 to 115 deal with this, first setting up column headers in the resource then accessing the results

             

                         <td class="Property"><%# Eval("AlertName") %></td>              

                                  <td class="Property"><%# Eval("TriggerTimeStamp") %></td>

                                  <td class="Property"><%# Eval("Caption") %></td>

                              </tr>

             

            Hope this helps

             

              • Re: Page customization question ( sql stuff )
                maladil

                Thanks everyone.  I got my page working as a stand alone.   It was a lot harder than I thought it would be.  Now I'm trying to figure out how to get it to work with a CustomHTML field.  If that is too difficult, then can someone point me to a way to add the  Solarwinds Header and Menu bar to the top of my custom page?  Here's my code if anyone is curious. 

                <%@ Import Namespace="System.Data" %>
                <%@ Import Namespace="System.Data.SqlClient" %>

                <Script Runat="Server">
                Sub Page_Load
                 dim dbconn AS SqlConnection
                 dim dbcomm AS New SqlCommand
                 dim dbread AS SqlDataReader
                 dim cmdSelectNodes AS String
                 dbconn=New SqlConnection("Server=165.X.x.x;uid=x;pwd=x;database=NetPerfMon" )
                 dbconn.Open()
                 cmdSelectNodes= "Select Nodes.NodeID, Nodes.Caption, Nodes.IP_Address, Nodes.Site_ID, Nodes.Lattitude, Nodes.Longitude, Nodes.Location_Type, Nodes.Alert_Importance, Nodes.MachineType From Nodes Where ( Nodes.Status = 2 ) AND ( Nodes.Lattitude IS NOT Null)"
                 dbcomm=New SqlCommand(cmdSelectNodes,dbconn)
                 dbread=dbcomm.ExecuteReader()
                 Nodes.DataSource=dbread
                 Nodes.DataBind()
                 dbread.Close()
                 dbconn.Close()
                End Sub
                </Script>

                <html>
                <head>
                <title>SolarWinds / GoogleMaps Node Assignment</title>
                <link href="http://code.google.com/apis/maps/documentation/javascript/examples/default.css" rel="stylesheet" type="text/css" />
                <script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"></script>
                 <form runat="server">
                   <asp:Repeater id="Nodes" runat="server">
                <HeaderTemplate>
                <script type="text/javascript">
                var map = null;

                function initialize() {
                  var myOptions = {
                    zoom: 5,
                    mapTypeControl: true,
                    mapTypeControlOptions: {style: google.maps.MapTypeControlStyle.DROPDOWN_MENU},
                    navigationControl: true,    
                    center: new google.maps.LatLng(38, -95),
                    mapTypeId: google.maps.MapTypeId.ROADMAP
                  }
                  map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
                  google.maps.event.addListener(map, 'click', function() {
                        infowindow.close();
                        });    
                 
                </HeaderTemplate>
                    <ItemTemplate>
                    var caption = "<%#Container.DataItem("Caption")%>";
                    var point = new google.maps.LatLng(<%#Container.DataItem("Lattitude")%>, <%#Container.DataItem("Longitude")%>);
                    var node_url = '<b>Node : </b> <a href="/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:'+"<%#Container.DataItem("NodeID")%>"+'">'+caption+"<a/> is down.";
                    var contentString = '<div id="content">'+node_url+"<br />IP = <%#Container.DataItem("IP_Address")%><br />Location type = <%#Container.DataItem("Location_Type")%>"+
                                        "<br />Device Type = <%#Container.DataItem("MachineType")%></div>";
                    var marker = createMarker(point, caption , contentString);
                    
                    </ItemTemplate>    

                <FooterTemplate>

                }

                var infowindow = new google.maps.InfoWindow(
                  {
                  });
                 
                function createMarker(latlng, mark_title, html) {
                    var contentString_marker = html;
                    var marker = new google.maps.Marker({
                        position: latlng,
                        map: map,
                        title: mark_title
                         });

                    google.maps.event.addListener(marker, 'click', function() {
                        infowindow.setContent(contentString_marker);
                        infowindow.open(map,marker);
                        });
                }

                </script>
                </FooterTemplate>
                  </asp:Repeater>
                </form>

                </head>
                <body onload="initialize()">
                  <div id="map_canvas" style="width: 1400px; height: 800px;"></div>
                </body>
                </html>